Calling excel VBA subroutine with arguments

Question

I want to create a subroutine that accepts a range as an argument. Something like this:

Sub Test(dataRange As Range)

    Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)

End Sub

However when I try to select the range on Excel and run it, I get a "Reference is not valid" error.

Note: I am trying to run this by going into the Developer tab and clicking on the "Macros" button. In there I type in Test($A$2:$B$4), and then I get the error.

Is there a way to call a custom VBA subroutine from an Excel spreadsheet and pass arguments to it?

Thanks

It's really surprising that you can see Subs with argument list in your macro list... — user2271770
– user2271770, Commented Jun 3, 2014 at 15:51
@CST-Link you can't. I think OP is trying to simply manually type in the sub name & arguments. — David Zemens
– David Zemens, Commented Jun 3, 2014 at 15:54

David Zemens · Accepted Answer · 2014-06-03 15:56:47Z

2

Although this is not a generalized answer, it should work for your purposes:

Sub Test(Optional dataRange As Range = Nothing)
    If dataRange is Nothing then Set dataRange = Range(Selection.Address)
    Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)

End Sub

From the macro menu, simply type in Test and hit the "Run" button.

This will use the current Selection as the data range.

Alternatively, use an InputBox to capture the range argument:

Sub Test2()
    Dim dataRange as Range
    Set dataRange = Application.InputBox("Select range", Type:=8)
    If dataRange is Nothing then Exit Sub
    Sheet2.Range("A1").Cells.value = dataRange.Cells(1, 1)

End Sub

edited Jun 3, 2014 at 15:56

answered Jun 3, 2014 at 15:50

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Felipe Over a year ago

Hi, I went with the second option. It was just what I was looking for. Thanks for your answer!

MatthewHagemann · Accepted Answer · 2014-06-03 21:34:33Z

0

You would need quotes around the range:

Test(Range("$A$2:$B$4"))

answered Jun 3, 2014 at 21:34

MatthewHagemann

1,1952 gold badges11 silver badges22 bronze badges

Collectives™ on Stack Overflow

Calling excel VBA subroutine with arguments

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related