How to return a result from a VBA function (2023)



For non-object return types, you must assign a value to your function name, like this:

Public Functiontest() As Integertest= 1End Function

Usage 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 Function

Usage 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 Function

Document: 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").

Jay Carr

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

Jay Carr


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.

Doug Jenkins


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.

Doug Jenkins


VBA functions treat the function name itself as a variable type. So instead of usingreturnstatement "", you just say:

test= 1

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



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.

John Coleman

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

John Coleman

Thanks for explaining howExit FunctionRelated toreturn

Austin D

@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!



The code below stores the return value in a variableretValAnd after thatMsgBoxcan be used to display the value:

DimretValAs IntegerretVal=test()Msgbox retVal

By using our website, you acknowledge that you have read and understoodCookie PolicyandPrivacy Policyours.

Licensed undercc by-sa 3.0with attribution required.


Top Articles
Latest Posts
Article information

Author: Domingo Moore

Last Updated: 10/26/2023

Views: 6008

Rating: 4.2 / 5 (53 voted)

Reviews: 92% of readers found this page helpful

Author information

Name: Domingo Moore

Birthday: 1997-05-20

Address: 6485 Kohler Route, Antonioton, VT 77375-0299

Phone: +3213869077934

Job: Sales Analyst

Hobby: Kayaking, Roller skating, Cabaret, Rugby, Homebrewing, Creative writing, amateur radio

Introduction: My name is Domingo Moore, I am a attractive, gorgeous, funny, jolly, spotless, nice, fantastic person who loves writing and wants to share my knowledge and understanding with you.