CODEWITHSUNDEEP
excelvbarange
benichka
benichka

Reputation: 925

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

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...

Upvotes: 0

Views: 1237

Answers (3)

benichka
benichka

Reputation: 925

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 :)

Upvotes: 0

Shawn E
Shawn E

Reputation: 472

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.

Upvotes: 1

MNor
MNor

Reputation: 83

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

Upvotes: 2

Related Questions