VBA Procedures

Subs

• Do not return a value
• Can be called from the Macro Dialog
• Can be assigned to shapes and controls

Functions

• Always return a value
• Can be used as spreadsheet functions

Property Procedures

• Used to assign or retrieve the values of properties
• Are usually used in class modules to control public access to private members

Event Procedures

• Are associated with objects
• Are only run when an event is triggered
• Need to be declared in very specific ways unique to each type of event

DLL Procedures

• Have their definition in an external DLL file
• Allow interoperability with other programming languages

Signature

A procedure's signature declares it's scope, name, parameters, and for functions, return type.

Body

The body of a procedure contains code statements. The body can contain up to 64 kilobytes of code when compiled. If the procedure size exceeds the 64K limit a Procedure Too Large Error will occur. Under most reasonable circumstances a procedure should not get this large so the size limit should not become an issue.

End

The definition of a procedure always ends with an End statement. To exit a procedure early, an Exit statement can be used.

Declaring Subs

To declare a Sub, provide a Modifier, the Sub keyword, a name for the Sub, any Parameters inside parentheses, and End Sub.

Sub Statement Syntax:

[Public|Private|Friend] [Static] Sub SubName ([Parameter, ...])
    
    [Statements ...]

    [Exit Sub]

    [Statements ...]
    
End Sub

Parameter Syntax:

[Optional] [ByRef|ByVal] [ParamArray] ParameterName [()] [As Type] [ = DefaultValue]

Macro Dialog

The Macro Dialog can be used to run public subroutines that do not have any parameters. The Macro Dialog can be accessed by pressing Alt + F8, through the Developer tab by clicking the Macros icon, and through the View tab by selecting View Macros.

Shapes and Controls

To assign a sub to a shape or control insert the shape or control and then right click the shape or control and select assign macro.

Declaring Functions

To declare a Function, provide a Modifier, the Function keyword, a name for the Function, any Parameters inside parentheses, and End Function.

Function Statement Syntax:

[Public|Private|Friend] [Static] Function FunctionName ([Parameter, ...])
    
    [Statements ...]

    [Exit Function]

    [Statements ...]
    
End Function

Parameter Syntax:

[Optional] [ByRef|ByVal] [ParamArray] ParameterName [()] [As Type] [ = DefaultValue]

Return Values

A Function will always return a value. To return a specific value from a Function, assign the Function a value before exiting the Function as if the name of the Function were a variable. Functions will return the default value for their data type if no return value is explicitly given. There are some differences in how to return value types, object, and arrays from a function.

Public Function Example() As Boolean

    Example = True

End Function

Public Sub Example()

    Dim Coll As Collection
    Set Coll = GetCollection()

End Sub

Public Function GetCollection() As Collection

    Set GetCollection = New Collection

End Function

Public Sub Example()

    Dim Arr1() As Long
    Arr1 = GetArray1

    Dim Arr2() As Long
    Arr2 = GetArray2

End Sub

Public Function GetArray1() As Long()

    Dim Arr(0 To 2) As Long
    Arr(0) = 1
    Arr(1) = 2
    Arr(2) = 3

    GetArray1 = Arr

End Function

Public Function GetArray2() As Variant

    Dim Arr(0 To 2) As Long
    Arr(0) = 1
    Arr(1) = 2
    Arr(2) = 3

    GetArray2 = Arr

End Function

Spreadsheet Functions (User-Defined Functions - UDFs)

Public Functions can be used in spreadsheets like built-in Excel functions. UDF arguments will not automatically appear as a tooltip. To show the arguments in the function press Ctrl + Alt + a.

Public Function AddTwoNumbers(Num1 As Double, Num2 As Double) As Double

    AddTwoNumbers = Num1 + Num2

End Function

Declaring Property Procedures

To declare a Property Procedure use the Property keyword with Get, Let, or Set inside a Class Module or a Standard Module and either assign or return a value.

'Class Module: Class1

Option Explicit

Private pMessage As String
Private pColl    As Collection

Public Property Get Message() As String
    Message = pMessage
End Property

Public Property Let Message (RHS As String)
    pMessage = RHS
End Property

Public Property Get Coll() As Collection
    Set Coll = pColl
End Property

Public Property Set Coll(RHS As Collection)
    Set pColl = RHS
End Property

Using Property Procedures

To call a Property Procedure assign or retrieve the value of the property of a class instance or module.

'Standard Module: Module1

Option Explicit

Public Sub Example()

    Dim C1 As Class1
    Set C1 = New Class1
    
    'Assign properties
    C1.Message = "Hello, World!" 'Let procedure is called
    Set C1.Coll = New Collection 'Set procedure is called
    
    'Retrieve properties
    Debug.Print C1.Message     'Get procedure is called
    Debug.Print C1.Coll.Count  'Get procedure is called

End Sub

Position Vs Keyword

Parameters can be passed to a procedure according to the order they are declared or they can be passed according to the name of the parameter. If an optional parameter is skipped then any subsequent argument will need to be passed by name. Arguments are passed by name using the "walrus" operator (":=").

Public Sub TestExample()

    'Arguments are passed according to the position
    Example 1, 2, 3 

    'Arguments are passed according to name
    Example Parameter1:=1, Parameter2:=2, Parameter3:=3 

    'Parameter1 is skipped. Next arguments must be passed by name
    Example Parameter2:="Hello", Parameter3:="World!"

End Sub


Public Sub Example(Optional Parameter1, Optional Parameter2, Optional Parameter3)

    Debug.Print Parameter1, Parameter2, Parameter3

End Sub

ByRef vs ByVal

The ByRef or ByVal keywords can be used in the declaration of a procedure's parameters to specify if the argument is passed by reference or by value. When an argument is passed by reference, the variable itself is passed to the procedure and its value can be changed by the procedure. When an argument is passed by value, a copy of the variable is created locally and passed to the procedure so the original variable cannot be affected. If ByRef or ByVal is not specified, arguments are passed by reference. The behavior of passing arguments ByRef or ByVal differs depending on if the argument is a value type variable, a reference type variable, or an array.

Public Sub TestExample()

    Dim x As Long
    x = 1

    ByValExample x

    Debug.Print x 'Prints: 1

    ByRefExample x

    Debug.Print x 'Prints: 2

    ByRefExample (x)

    Debug.Print x 'Prints: 2

End Sub

Public Sub ByValExample(ByVal y As Long)

    'y is a copy of x
    y = y + 1

End Sub

Public Sub ByRefExample(ByRef y As Long)

    'y is x
    y = y + 1

End Sub

Private Sub TestExample()

    Dim Coll As Collection
    Set Coll = New Collection

    Coll.Add 1

    Debug.Print Coll.Count

    ByValExample Coll

    Debug.Print Coll.Count

    ByRefExample Coll

    Debug.Print Coll.Count

End Sub

Private Sub ByValExample(ByVal Coll As Collection)

    'Coll is a copy of the object variable
    'Coll is pointing to the original object

    'Original object is affected
    Coll.Add 2

    'Reference is reassigned to new object
    'Original object is NOT terminated b/c original reference variable still points to it
    Set Coll = New Collection

End Sub

Private Sub ByRefExample(ByRef Coll As Collection)

    'Coll is the original object variable

    'Original object is affected
    Coll.Add 3

    'Reference is reassigned to new object
    'Original object is terminated b/c no more references to it
    Set Coll = New Collection

End Sub

Public Sub Example()

    Dim Arr(0 To 2) As Variant
    Arr(0) = 1
    Arr(1) = 2
    Arr(2) = 3

    ByRefExample Arr

    Debug.Print Arr(0), Arr(1), Arr(2)

    ByValExample Arr

    Debug.Print Arr(0), Arr(1), Arr(2)

End Sub

Public Sub ByRefExample(ByRef Arr() As Variant)

    'Array parameters must be declared ByRef.

    'Original array values are updated
    Arr(0) = 4
    Arr(1) = 5
    Arr(2) = 6

End Sub

Public Sub ByValExample(ByVal Arr As Variant)

    'Copy is made of array

    'Original array values not affected
    Arr(0) = 7
    Arr(1) = 8
    Arr(2) = 9

End Sub

Optional Parameters and Default Values

Optional parameters can be declared using the "Optional" keyword. A default value can be assigned for optional parameters. Optional parameters must be declared after required arguments. Optional parameters cannot be declared if there is a ParamArray parameter.

Public Sub Example(Message As String, Optional Flag As Boolean = True)

    Debug.Print Message, Flag

End Sub

The IsMissing function can be used to determine if an argument was passed to an optional parameter of type Variant. To check if an optional argument of another type has been passed, use a default value which specifies that no valid argument was passed.

Public Sub Example(Optional Arg1 As Variant, Optional Arg2 As String = "NoArgProvided")
    
    If IsMissing(Arg1) Then
        Debug.Print "Arg1 not passed"
    Else
        Debug.Print "Arg1 passed"
    End If

    If Arg2 = "NoArgProvided" Then
        Debug.Print "Arg2 not passed"
    Else
        Debug.Print "Arg2 passed"
    End If                    

End Sub

ParamArray

ParamArray is used to declare a parameter as an array of indeterminate size so any number of arguments can be passed to a procedure. There can only be one ParamArray argument in a procedure declaration and it must be the last parameter. ParamArray cannot be used if the procedure has any Optional arguments. Arguments are passed to a ParamArray parameter as a comma-delimited list. A ParamArray must be declared as an array of type Variant.

Public Sub Example()

    Dim TheSum As Double

    TheSum = SumValues(1, 2, 3, 4, 5)

    Debug.Print TheSum

End Sub

Public Function SumValues(ParamArray Values() As Variant) As Double

    Dim i As Long

    For i = LBound(Values) To UBound(Values)
        SumValues = SumValues + Values(i)
    Next i

End Function