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

Question

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!

I suggest first researching the msdn documentation on NamedRanges and Functions. NamedRanges require a Name and an address for the RefersToRange. In VBA we do not use the return keyword for returning something from a function. Also, the $ I think is meaningless here. — QHarr, Jun 14 '18 at 16:25
And yes to return an object from a function then Public Function myFunc(args....) As Object — QHarr, Jun 14 '18 at 16:26
Names is a collection at workbook level. You iterate those to get the properties information associated with a given name. What are you actually trying to achieve? — QHarr, Jun 14 '18 at 16:27
This was a brain fart, I've been working a LOT in C lately. I'll edit my question to include the working code. — Skoddie, Jun 14 '18 at 16:28

score 3 · Answer 1 · answered Jun 14 '18 at 16:23

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.

Ack, I realized this a split second before you posted haha. Too much time in C lately. Thank you! — Skoddie, Jun 14 '18 at 16:25

ashleedawg · Accepted Answer · 2018-06-14T16:26:55.027

2

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

edited Jun 14 '18 at 16:26

answered Jun 14 '18 at 16:23

ashleedawg

20,365
9
72
105

Too much time in C lately, haha. This really helped, thank you! – Skoddie Jun 14 '18 at 16:26
2

@Skoddie - Ah - that i understand - I often hesitate when switching between languages. The `;` in js are my nemesis when switching to another language. – ashleedawg Jun 14 '18 at 16:27

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

2 Answers2

Linked

Related