Excel: Visual Basic: range as function input doesn't work

Question

I'm starting with VB in Excel; so far I can't figure the following; I've got the following function:

Function testGetRange(myRange As Range)
    Dim weekStart As Integer
    Dim weekEnd As Integer

    weekStart = myRange(1).Value
    weekEnd = myRange(2).Value
End Function

If I try to execute it like that:

Sub CreationRapport()
    Dim testRange1 As Range
    Set testRange1 = Range("A5:B5")

    testGetRange (testRange1)
End Sub

I've got an error like "object needed" (sorry the error message is in French: "objet requis"), stopping right when I try to execute the function.

So: the range is created, the function takes a range as input; don't know why this doesn't work...

A function typically returns something at the the end, like: testGetRange = .... I use "..." because I can't tell what you want it to return or do. Perhaps you just need a Sub that performs an action. It would be helpful to know what you want to happen. — Doug Glancy
– Doug Glancy, Commented Sep 28, 2014 at 16:29
testGetRange(myRange As Range) As String [...] testGetRange = weekStart doesn't change a thing :/ — benichka
– benichka, Commented Sep 28, 2014 at 17:34
Please read some basic materials on VBA Functions and Subs. Also, as I suggested, telling us what you hope to have happen would really help. Otherwise we have to guess. — Doug Glancy
– Doug Glancy, Commented Sep 28, 2014 at 19:09

MNor · Accepted Answer · 2014-09-28 19:30:55Z

2

You are calling a function and the parantheses signify that you want the function to return something:

testGetRange (testRange1)

But your function doesn't return anything. You can fix this by adding this to testGetRange:

testGetRange ="My return output"

...And you don't put the output anywhere. You can fix this by changing in CreationRapport:

MyOutput = testGetRange (testRange1)
msgbox MyOutput

answered Sep 28, 2014 at 19:30

MNor

836 bronze badges

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

1 Comment

benichka Over a year ago

Your last part was the answer: MyOutput = testGetRange (testRange1), after initializing MyOutput, made it work. Plus, I got an error when setting weekStart and weekEnd. Will post my solution just below :) Thank you!

Shawn E · Accepted Answer · 2014-09-28 20:15:27Z

When you call a function but don't want a return value you need to either leave off the parenthesis

Sub CreationRapport()
    Dim testRange1 As Range
    Set testRange1 = Range("A5:B5")

    testGetRange testRange1
End Sub

Or use call

Sub CreationRapport()
    Dim testRange1 As Range
    Set testRange1 = Range("A5:B5")

    Call testGetRange (testRange1)
End Sub

For the why you can see how VBA handles transferring control to a sub or function here on MSDN

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you use either Call syntax to call any intrinsic or user-defined function, the function's return value is discarded.

benichka · Accepted Answer · 2014-09-28 20:30:11Z

0

OK, so after testing the different answers, this worked:

Function testGetRange(myRange As Range) As String
    Dim weekStart As String
    Dim weekEnd As String

    weekStart = myRange(1)
    weekEnd = myRange(2)

    testGetRange = weekStart
End Function

And in the Sub:

Sub CreationRapport()
    Dim myOutput As String
    Dim testRange1 As Range
    Set testRange1 = Range("A5:B5")

    myOutput = testGetRange(testRange1)
    MsgBox myOutput
End Sub

The MsgBox is not mandatory, but the part myOutput = testGetRange(testRange1) is! So, as advised by Doug, need to work more on the VB to see why it is. Thank you all :)

answered Sep 28, 2014 at 20:30

benichka

9252 gold badges12 silver badges34 bronze badges

Collectives™ on Stack Overflow

Excel: Visual Basic: range as function input doesn't work

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related