Excel VBA - Subs and Functions

Kacper Dziembek — Mon, 17 Feb 2025 14:46:58 +0000

Sub

A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that performs actions but doesn't return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are returned by some function. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.

Pseudo code example:

Sub  <name>( <argument1>, <argument2> , …) 
   <instructions>
End Sub

Example of real one

Sub GreetUser(name As String, age As Integer) 
   MsgBox("Your answer is correct!", 0, "Answer Box") 
End Sub

To call a Sub procedure from another procedure, write the name of the procedure and include values for any required arguments. The Call statement is not required, but if you use it, you must enclose any arguments in parentheses. If you do use Call on a function/Sub, do not include parentheses if the function/Sub does not take any arguments. The following example shows two ways to call a Sub procedure with more than one argument. The second time it is called, parentheses are required around the arguments because the Call statement is used. The following code shows two approaches.

Sub Main()
    HouseCalc 99800, 43100 
    Call HouseCalc(380950, 49500)
 End Sub

To use the return value of a function, assign the function to a variable and enclose the arguments in parentheses, as shown in the following example. A statement in a Sub or Function procedure can pass values to called procedures by using named arguments. You can list named arguments in any order. A named argument consists of the name of the argument followed by a colon and an equal sign (:=), and the value assigned to the argument:

answer3 = MsgBox( Title:="Question 3",
Prompt:="Are you happy with your salary?",
Buttons:=4 )

Function

A Function procedure is similar to a Sub procedure, but a function can also return a value and can be used in expressions. A Function procedure can take arguments, such as constants, variables, or expressions that are passed to it by a calling procedure. If a Function procedure has no arguments, its Function statement must include an empty set of parentheses. A function returns a value by assigning a value to its name in one or more statements of the procedure.

Pseudo Code

Function <name>(<argument1>, <argument2> , …)
   <instructions>
End Function

Example

Function Celsius(fDegrees) 
   Celsius = (fDegrees - 32) * 5 / 9 
End Function

Some functions have two versions:

Variant version: Handles automatic data type conversions and allows Null values but uses more memory.
String version: More efficient with memory usage but doesn't handle automatic type conversions or Null values.

DEV Community: Kacper Dziembek

Excel VBA - Subs and Functions

Sub

Function