277
How do I return a result from a function?
For example:
Public Functiontest() As Integer return 1End FunctionThis gives a compile error.
How do I make this function return an integer?
Answer:
429
For non-object return types, you must assign a value to your function name, like this:
Public Functiontest() As Integertest= 1End FunctionUsage example:
DimiAs Integeri=test()If the function returns an Object type, then you must useSetkeywords like this:
Public Functiontest range() AsRangeSettest range=Range("A1")End FunctionUsage example:
DimrAsRangeSetr=test range()Note that assigning a return value to a function name does not terminate the execution of your function. If you want to get rid of the function, then you need to explicitly sayExit Function. For example:
Functiontest(ByValjustReturnOneAs Boolean) As Integer IfjustReturnOneThentest= 1 Exit Function End If 'more code...test= 2End FunctionDocument:http://msdn.microsoft.com/en-us/l Library / office / gg264233% 28v = office.14% 29.aspx
For completeness, it should be noted that when you return an object (Rangee.g. example), you need to useSetlike you would if you put an instance variable in a regular method. So if, for example, "test" was a function that returned a Range, the return statement would look like thisset test = Range("A1").
Why is it @JayCarr?
@PreferencesoData - Simply because that's how you set an instance variable in general and do it withoutsetcan lead to problems. I had problems doing it without, but if I use itsetI'm not :).
I think it's worth mentioning that the function's behavior will be different when you call it from the spreadsheet, compared to calling it from another VBA or Sub function.
When called in VBA, the function will return a range object, but when called from a worksheet it will just return the value, so,set test = Range("A1")exactly equivalent totest = Range("A1").Value, where "test" is defined as a Variable, rather than a Range.
86
VBA functions treat the function name itself as a variable type. So instead of usingreturnstatement "", you just say:
test= 1Note, though, that this doesn't break out of the function. Any code after this statement will also be executed. Therefore, you can have multiple assignment statements that assign different valuestestand whatever the value is when you reach the end of the function will be the returned value.
In fact, you answered the question more clearly with additional information (which could potentially lead to another question from a newbie to VBA). Keep up the good work
Sorry, looks like you just answered the same as mine, which I had before, but just to add the fact that it doesn't break out of the function. It's a nice addition, I just thought it would be more appropriate as a comment. I'm not sure what the proper etiquette is, I guess it's a bit rude to downvote just because it's a good answer, but it won't let me undo it.
41
Just setting the return value to the function name still doesn't workcompleteexactly the same as the Java statement (or other)return, because in java,returnexit the function, like this:
public inttest(intx) { if (x== 1) { return 1; // exits immediately } // still here? return 0 as default. return 0;}In VB, the equivalentExactlylost two linesif you don't put the return value at the end of the function. So in VB, the exact corollary would look like this:
Public Functiontest(ByValxAs Integer) As Integer Ifx= 1 Thentest= 1 ' does not exit immediately. You must manually terminate... Exit Function ' to exit End If ' Still here? return 0 as default.test= 0 ' no need for an Exit Function because we're about to exit anyway.End Function Since this is the case, it's nice to know that you can use the return variable like any other variable in the method. Like this:
Public Functiontest(ByValxAs Integer) As Integertest=x' <-- set the return value Iftest<> 1 Then ' Test the currently set return valuetest= 0 ' Reset the return value to a *new* value End IfEnd Function Or,For exampleextreme on how variables returnwork(but not necessarily a good example of how you should actually code). Here's an example that will help you wake up at night:
Public Functiontest(ByValxAs Integer) As Integertest=x' <-- set the return value Iftest> 0 Then ' RECURSIVE CALL...WITH THE RETURN VALUE AS AN ARGUMENT, ' AND THE RESULT RESETTING THE RETURN VALUE.test=test(test- 1) End IfEnd Function"It's nice to know that you can use the return variable like any other variable in the method"mostlytrue - but e.g. if return type isVariantand your goal is to return an array then something likeReDim test(1 to 100)will cause an error. Also, even though itTo beGood for treating basic type likeIntegersas such it is considered somewhat unidiomatic. It makes the code harder to read. VBA programmers scan the lines assigned to the function name to understand what the function does. Using the function name as a regular variable unnecessarily obscures this.
@JohnColeman, totally agree on both points. By no means the last example is one of the recommended methods. But, the topic question is regarding How to return a variable and so this is just an attempt to fully explain what VB returns and by expanding how they work. Certainly the last case is not a recommendation. (I certainly wouldn't code more than one example.) So your point is well made and well added. Thank you.
ItTo beUseful for smallish functions, and is something that any VBA programmer should know about, so I had no problem with you mentioning it. I just thought it would be a good idea to include a warning.
Thanks for explaining howExit FunctionRelated toreturn
@JohnColeman, Obviously, you can'tReDim test(1 to 100)doesn't raise an error simply because 'test' is not declared as an array! and for no other reason! You cannot declare a function as an array. Declare it as aVariant, then just build your output array (it can be Dynamic or Static) inside this functiontestand then assign ("=") this arraytestas return value. To continue the operation, likeReDiming it, you need to assign the returned value to a variable, for exampleDim x as Variantand callx = test, Laterxis what you didtest!
—Gene
-6
By using our website, you acknowledge that you have read and understoodCookie PolicyandPrivacy Policyours.
Licensed undercc by-sa 3.0with attribution required.