# Best Way to Return a Value from a Function [closed]

In my example, Method1 uses a variable to store the return values and then assigns the variable to the function. Method2 adds the return values directly to the function.

Questions:

1. What is the pattern of Method2 called?
2. Is one method prefered over the other and why?

Function Method1(FirstNumber As Long, LastNumber As Long) As Collection
Dim Map As New Collection
Dim n As Long

For n = FirstNumber To LastNumber
Next

Set Method1 = Map
End Function

Function Method2(FirstNumber As Long, LastNumber As Long) As Collection
Set Method2 = Collection
Dim n As Long

For n = FirstNumber To LastNumber
Next
End Function


Could someone explain the votes to close the question? I have written well over 50 of these types of functions this year alone. Not to mention that as a contributor to SO, I feel like hearing from other developers opinions on the subject will help me improve my answers.

Feel free to down vote and vote to close the question again for me commenting in my post.

## Edit 1

There was a typo in my original code. This is how Method1 should have been written.

Function Method1(FirstNumber As Long, LastNumber As Long) As Collection
Dim Map As New Collection
Dim n As Long

For n = FirstNumber To LastNumber
Next

Set Method1 = Map
End Function


I restored my original post and added this edit for clarification.

• This is a confusing aspect of VBA. When you look in the object browser you can clearly see that there is an independent variable with the same name as the Method. Previous objections I've encountered are that as VBA doesn't normally allow overloading thus the Method2 usage is atypical and therefore should be avoided. Mar 9, 2022 at 17:12
• If it helps I raised this issue for twinbasic suggesting that the 'free' variable be disambiguated as a variable called Result. The suggestion has not received much discussion and the only responder to my suggestion said that they would use Method 1. Mar 9, 2022 at 17:18
• Please edit your question so that the title describes the purpose of the code, rather than its mechanism. We really need to understand the motivational context to give good reviews. Thanks! Mar 9, 2022 at 19:36
• I didn't down vote or vote to close, because there was a reasonably good answer by the time I saw the question. The question borders on being too theoretical, and the code we review is supposed to come from working projects. Mar 9, 2022 at 21:17
• @pacmaninbw Yes to be honest I probably shouldn't have answered, this is off topic really as it is theoretical code (as I mention in my answer there are times when I would do method 2, but for the specific code in this post - making a collection I would avoid it). However I don't think the intent of this post is to discuss that specific use case, but rather a more general set of rules (based on the title) and the general case is off topic. Probably better suited to software engineering stack exchange (obviously nobody would see it there though...) Mar 10, 2022 at 10:06

Method 1 is marginally (really barely) less efficient because of the additional variable. However Method 2 sometimes doesn't do what you might want (/expect):

Function Method2(LastNumber As Long) As Collection
Set Method2 = New Collection
Dim n As Long

For n = 1 To LastNumber
Next

'print the first item from the collection for logging
Debug.Print Method2(1) 'Stack Overflow?!

End Function


Or

Function Method2(Optional ByVal LastNumber As Long = 5) As Collection
Set Method2 = New Collection
Dim n As Long

For n = 1 To LastNumber
add_n Method2(), n 'blink and you miss it, not the most obvious error
Next
End Function

Sub add_n(ByVal coll As Collection, ByVal n As Long)
End Sub


It's just a bit higher risk; confusing a variable with a function call. That makes Method 1 better in my book unless you are doing something performance critical or a one-liner (e.g. I would assign directly to the function name if I'm doing low level stuff with pointers and API calls)

I call the return variable result which encourages me not to forget to assign it to the function name at the end

Even weirder error:

Function Method2(LastNumber As Long) As Scripting.Dictionary
Set Method2 = New Dictionary
Dim n As Long

Method2(1) = "First value" 'Argument not optional error, try work that one out
For n = 2 To LastNumber
Next
End Function

• Agreed, I have always used Method1 out of instinct. Great Example! It is interesting how the compiler determines that Method2 is a local variable and Method2() is a function call. Function Method2(Optional ByVal IndexAs Long = 5) As Variant() Eek!! Mar 9, 2022 at 17:58
• Do you have actual evidence that Method 1 is marginally less efficient because of the additional variable? Or are you just assuming that the interpreter is not smart enough to elide the temporary variable? Have you looked at the p-code or execode disassembly? Mar 10, 2022 at 8:15
• @CodyGray I haven't looked no. One interesting feature of VBA is that what you see in the editor is actually a round trip translation of what you type -> p-code -> VBA. This is the mechanism that capitalises variables and converts &H001 -> &H1 as you type. Given the intermediate variable survives the round trip to and from p-code I think the interpreter/pre-compiler is not smart enough to optimise it out. Now I don't know the details of how p-code is converted to actual executable code, but it would certainly be possible to profile the extreme case (a huge UDT for example) Mar 10, 2022 at 8:33
• Like @tinman said, when the parser sees Method2(1) it generates a new call to Method2. That explains the Stack Overflow. The "argument not optional" is because Method2(1) returns a Dictionary object, and you're trying to assign a string to the Dictionary object. Mar 10, 2022 at 18:17

The usage of the temporary variable instead of the implicit function named variable is to follow Return patterns seen in other languages. This is a case of just because VBA lets you do it, doesn't mean you should.

This argument says that you should only set the return once, at the end of the function. Coding this way makes your code easier to read if the reader expects a return at the bottom of the function.

Let's compare VB.NET to VBA here:

VB.NET:

Function Method1(FirstNumber As Long, LastNumber As Long) As List(Of Long)
Dim map As New List(Of Integer)

Dim n As Long
For n = FirstNumber To LastNumber
Next

Return map
End Function


The VBA code is similar except we swap the Return for a Set

Function Method1(FirstNumber As Long, LastNumber As Long) As Collection
Dim map As Collection
Set map = New Collection

Dim n As Long
For n = FirstNumber To LastNumber

• @Freeflow, except that in VBA if you truely want multiple returns you would need to Set and Exit Function for each Return you want. I generally avoid having multiple Exits from a function if I can help it, in VBA. So while I use multiple Return statements in VB.NET or C# I would never Set...Exit in VBA. Ultimately, this is a code style question. In VBA, we also put error handlers and log handlers at the end of our functions. There are benefits to following the SESE code pattern in VBA that aren't present in the other "modern" languages discussed in that article. Mar 10, 2022 at 16:58