You can easily test your return value using by using the Debug.Print function. The following example shows this: ' Sub WriteValues()ĭim Amount As Long ' Get value from GetAmount function You do this by assigning the function call to a variable. When you return a value from a function you will obviously need to receive it in the function/sub that called it. The following example demonstrates this: ' Function GetAmount() As Long ' Returns 55Įnd Function Function GetName() As String ' Returns John To return a value from a function you assign the value to the name of the Function. ' call requires parentheses for arguments Call AddValues(2, 4)Įnd Sub Sub AddValues(x As Long, y As Long) ' If call is not used then no parentheses If you are passing arguments using Call then you must use parentheses around them.
There is no real need to use it unless you are new to VBA and you feel it makes your code more readable. ' call each sub to perform a task Call CopyData We are calling the CopyData sub from the Main sub. We refer to this as calling a procedure e.g. When VBA reaches a line containing a procedure name, it will run the code in this procedure. You can see that in the Main sub, we have added the name of three subs. ' Add code here End Sub Sub AddFormulas()
It is better to break your code into multiple procedures. This is not a good way to write your code. When people are new to VBA they tend to put all the code in one sub. Note 2:We can use the word procedure to refer to a function or sub They also won’t appear in a list of subs when you bring up the Macro window on the developer tab. This means that subs won’t be visible to other projects and applications. Note 1: You can use “Option Private Module” to hide subs in the current module. If the function is public, will appear in the worksheet function list for the current workbook.but it won’t appear in the list of Macros. Can be called it from VBA\Button\Event etc.Can return a value but doesn’t have to.Can be called from VBA\Button\Event etc.You can return any type you can declare as a variable including objects and collections. You can see it is simple how you declare a variable.
The next example shows you how to specify the return type Function PerformCalc() As Long End Function This means that VBA will decide the type at runtime. If you don’t add the type then it is a Variant type by default. It is optional to add a return type to a function. You normally create a function when you want to return a value.Ĭreating a function is similar to creating a sub Function PerformCalc() There are other differences which we will look at but this is the main one. The major difference is that a function can return a value – a sub cannot. When you create your Sub you can add some code like the following example shows: Sub WriteValues()Ī Function is very similar to a sub.
The end of the Sub is marked by the line End Sub.