VBA — Functions vs. Subs

There are two methods in VBA — functions and subs. A sub does not return any value while a function must return a value. Functions and subs can be either Private or Public. When either is private they can only be called within the current module. If it is in a class then only functions and subs within the class can call it. The function or sub would not appear in the Macros list available to the end user. When it is public it can be called from outside the module, called on the class object, and can be seen in the Macros list by the end user.

Function Example:

Public Function doubleValue(changeValue as Integer) as Integer

   doubleValue = changeValue * 2

End Function

Here is a public function which doubles the value of the parameter. The parameter is of type int and the function return value is of type int. We return the value by setting the function equal to the int we want to return.

Sub Example:

Private Sub doubleListValues(itemList() as Integer)

   For i = 0 to Ubound(itemList)

      Debug.Print doubleValue(itemList(i))

   Next i

End Sub

Here is a private sub which prints out a list of items at double the input value. The parameter is an array of ints. We loop through each item in the array and call the doubleValue function on the item. This doubles the value and the Debug.Print method will output the value to the Debug console. Note that because the doubleValue function is Public it can be called from another module. However, if the function were Private it would need to be in the same module as the doubleListValues sub or it would fail to run.