CODEWITHSUNDEEP
vbaexcel
Sophie Altair
Sophie Altair

Reputation: 263

Is it possible to create a function that returns an object?

I am working on a macro which will require referencing a large number of named ranges using:

ThisWorkbook.Names("$NAMEDRANGE$").RefersToRange.$METHOD$

I thought it might make the code easier to read/work with if I could create a function which I could use as follows:

NamedRange("$NAMEDRANGE$").$METHOD$

Without really knowing if it would work, I tried the following:

Function NamedRange(ByVal sName As String) As Object
    return ThisWorkbook.Names(sName).RefersToRange

End Function

This won't even compile, producing an

Expected: end of statement

I think I may need to use a Class Module, but I'm not very familiar with them. Is this possible, or should I just spell out the entire reference on each line?

I have been spending too much time in C. This code works perfectly:

Function NamedRange(ByVal sName As String) As Object
   Set NamedRange = ThisWorkbook.Names(sName).RefersToRange

End Function

Thanks for the help!

Upvotes: 2

Views: 9291

Answers (2)

ashleedawg
ashleedawg

Reputation: 21639

I'm not sure about some of your syntax (are you combining languages?), but to answer your question...

Yes, a VBA function can return an object.

You just have to make sure it's properly declared and that the return value is Set, like any time an object is assigned to a variable.

Function myFunction() As Worksheet
    Set myFunction = Worksheets("Sheet1")
End Function

Sub myTest()
    Debug.Print myFunction.Name
End Sub

I'm not aware of return being used in this sense in VBA, nor the $ dollar signs. (The $ can be used as a shortcut to specify a String data type.)

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

The Return statement does exist in VBA, but it's used in conjunction with the antiquated GoSub statement, from a time before Sub and Function scopes were a thing - cue QBasic memories...

    foo = 42
    GoSub Increment
    Debug.Print foo ' prints 43
    Exit Sub
Increment:
    foo = foo + 1
    Return

So VBA is really expecting the statement to end immediately follownig the Return token, hence the "Expected: end of statement" error.

That's not what you want here. In VBA, the return value of Function (and Property Get) members is assigned by literally assigning to the member identifier:

Function GetFoo() As Long
    GetFoo = 42
End Function

And since you're returning an object reference, the Set keyword is required:

Function GetBar() As Something
    Set GetBar = New Something
End Function

Note that doing that does not immediately return to the caller - if you need to exit the function scope immediately, you need to Exit Function explcitly.

Upvotes: 3

Related Questions