Procedures

VBA code is often written (stored) as a procedure in an ordinary code module.

An ordinary code module can accommodate three different types of procedures

1. Sub procedures (also called Macros) - covered in this module
2. Function procedures
3. Property procedures

The procedures in this module are written in an ordinary code module. Ordinary modules are listed in the Modules component of the Project Explorer

Sub procedures

1. Writing and executing sub procedure code [Intermediate level]

1.1 Writing a sub procedure

To add a sub procedure to a Module, use ONE of these methods:

• Select or add a code module, then type Sub MacroName and press enter. The VBE automatically adds the paired brackets () and the closing End Sub statement
• Insert > Procedure to display the Add Procedure dialog box. A code module must be active in the VBE
• Use the Macro Recorder from the Developer tab on the Excel ribbon

SYNTAX: Sub procedure

[Public | Private] [Static] Sub procedure_name([argument_list])
    [statements]
[Exit Sub]
    [statements]
End Sub

Components of the Syntax for the Sub...End Sub procedure

1.1.1 Compulsory syntax

A the minimum, the procedure must have a declaration statement: Sub and name, and an ending statement: End Sub

Sub procedure_name()
   ...
End Sub

WelcomeMsg1 (code 1) is a procedure with one code statement.

Sub WelcomeMsg1()
    MsgBox "Welcome to excelatfinance.com"
End Sub

About Code 1

1. Lines 1 and 3: A sub procedure in Block format. Implicit Public scope, name WelcomeMsg1, with no arguments.
   The empty brackets () must be included.
   The code 1 procedure starts on line 1 with the Sub key word, and ends on line 3 with the End Sub statement.
   Each of the code statements is terminated with the Enter key
2. Line 2: A one line executable statement. The VBA MsgBox function displays a text string in the Excel window
3. Statement The entire procedure, or parts of it, can also be written in one line format using the colon character ":" to terminate each of the code statements.
   The technique is sometimes called statement concatenation.
   It is useful when the statements are trivial, or to combine declaration and assignment statements
4. Thus, an equivalent code statement is:

               Sub WelcomeMsg1(): MsgBox "Welcome to excelatfinance.com": End Sub
       

1.1.2 Explicit Public declaration

The result of code2, with an explicit Public scope statement ( line 5 ) is identical to that of code 1.

Public Sub WelcomeMsg2()
    MsgBox "Welcome to excelatfinance.com (2)"
End Sub

About Code 2

1. Lines 5: Public scope. This is the default value and is usually omitted
2. Public scope means that:
   • the procedure will be listed on the Excel Macro list (except when arguments are included)
   • the procedure is available (visible) to all modules in the project. Conditional of the containing module being Public (the default setting)
3. For an explanation and example of Private scope, see code 6 in section 2.2 below

1.1.3 A Sub Procedure with Arguments

Sub WelcomeMsg3(Msg As String)
    MsgBox Msg
End Sub

Sub WelcomeMsg31()
    ' Requires WelcomeMsg3
    WelcomeMsg3 "Welcome to excelatfinance.com (31 & 3)"
End Sub

About Code 3a

1. Line 10: declares a (public) sub procedure named WelcomeMsg3. It has one compulsory argument named Msg of type String. A procedure with arguments is sometimes referred to as the Called, or the Callee procedure
2. Line 11: the MsgBox function displays the string passed to the Msg argument. See figure 1 for an graphical depiction of the data flow.
   The syntax [WelcomeMsg3(Msg As String)] can be displayed by: Right clicking the statement and selecting 1. Quick info, or 2. Parameter info; or writing the Callee procedure first, then using VBA to display the procedure arguments
3. Line 16: is the main statement of the caller procedure. It includes the name of the called procedure WelcomeMsg3 and also passes the text argument
4. Line 16 can also be written with an explicit Call statement:

               Call WelcomeMsg3 "Welcome to excelatfinance.com (31 & 3)"
       

   Call transfers control to a Sub or Function procedure

1.2 Running a sub procedure

To run a sub procedure, do one of the following:

• From the VBE
  With the blinking insertion point in the procedure
  • Select Run > Run Sub/UserForm on the VBE menu
  • Click the Run > Run Sub/UserForm button the VBE Standard toolbar
  • Press the F5 short cut key
  • Tools > Macros, select the macro from the list, then Click Run
• From Excel
  • View > Macros, select the macro from the list, then Click Run
  • Developer > Macros, select the macro from the list, then Click Run
  • Type the macro name in the Name Box; press Enter, select the macro from the list, then Click Run
  • Assign the macro to an Object or Event. Select the Object, then right click and select Assign Macro

2. Writing and executing sub procedure code [Advanced level]

2.1 Passing arguments

Arguments are passed to the called procedure by use of the keyword ByRef (by reference) or ByVal (by value)

• ByRef passes the address of the variable to the called procedure. The called procedure is able to change the value stored in the address
• ByVal passes a copy of the variable to the called procedure. The called procedure cannot change the value of the variable

2.1.1 By reference: ByRef

By default, arguments are passed by reference (ByRef). This can be made explicit by rewriting code 3a with ByRef Msg in the procedure declaration line:

Sub WelcomeMsg3(ByRef Msg As String)
    MsgBox Msg
End Sub

The name of the argument in the called procedure need not be the same as the name of the caller variable.

Sub WelcomeMsg32()
Dim Msg As String
    Msg = "Welcome to excelatfinance.com (32 & 3)"
    WelcomeMsg3 Msg
End Sub

Sub WelcomeMsg33()
Dim Msg33 As String
    Msg33 = "Welcome to excelatfinance.com (33 & 3)"
    WelcomeMsg3 Msg33
End Sub

For example, Msg33 in code 4b (line 54) is passed ByRef to the Msg parameter in code 3; both the a and b versions.

2.1.2 By value: ByVal

Following the rules of ByRef and ByVal listed in section 2.1, the difference is demonstrated in code 5.

Sub WelcomeMsg34(ByRef Msg11 As String, ByVal Msg12 As String)
    Msg11 = Msg11 & "atfinance.com"
    Msg12 = Msg12 & "atfinance.com"
End Sub

Sub WelcomeMsg36()
Dim Msg1 As String
Dim Msg2 As String
    Msg1 = "excel"
    Msg2 = "excel"
    Debug.Print "Assigned values: " & Msg1, Msg2
    WelcomeMsg34 Msg1, Msg2
    Debug.Print "Concatenated values: " & "ByRef:= " & Msg1 & "; cf ByVal:= " & Msg2
End Sub

The return values in code 5 are printed to the Immediate Window with output shown in figure 2.

2.2 Private scope, Exit Sub statement and Static declaration

Private Static Sub DemoExitSub()
Dim Price, Quantity
Dim Sales As Double
Dim App As Application

    Set App = Application

    If Not TypeName(Price) = "Double" Then Price = 0#
    If Not TypeName(Quantity) = "Double" Then Quantity = 0#

    Price = App.InputBox(Prompt:="Enter the Price of One Unit", _
                         Title:="Price", _
                         Default:=Format(Price, "#0.00"), _
                         Type:= 1)
        If Price = False And TypeName(Price) = "Boolean" Then Exit Sub
        Price = CDbl(Price)

    Quantity = App.InputBox(Prompt:="Enter the Number of Units", _
                            Title:="Units", _
                            Default:= Quantity, _
                            Type:= 1)
        If Quantity = False And TypeName(Quantity) = "Boolean" Then Exit Sub
        Quantity = CDbl(Quantity)

    Sales = Price * Quantity

    ' more code

End Sub

About Code 6

1. Line 80: Private scope. This must be set explicitly
2. Private scope means that:
   • the procedure will not be listed on the Excel Macro list, but can still be called by typing the name in the Macro > Macro name: box and clicking Run. Private procedures are often run with calls from other procedures, or events from the Excel objects, such as a control Click event
   • the procedure is only available (visible) to other procedures in the same module. See calling private procedures for further discussion
3. Lines 80: Static - when included in the procedure declaration statement means that all variables and arrays declared in the procedure (local scope) retain there values during the current session.
4. Line 81: Dim - the variables Price, and Quantity are declared with type Variant, default value Empty. Although Variant is a type, it cannot be explicitly identified with the TypeName function. Once assigned a value, the TypeName function will return the Variant sub-type (lines 87 and 88)
5. Lines 87 and 88: can also be written as:

       If IsEmpty(Price) Then Price = 0#
       If IsEmpty(Quantity) Then Quantity = 0#
       

6. Lines 94 and 101: Exit Sub - included as part of an If...Then construct. When the user presses the Cancel button on the InputBox dialog box, the logical test Price = False And TypeName(Price) = "Boolean" will then return TRUE, and the procedure will terminate at the line 94 code point

• Development platform: Excel 2016 (64 bit) Office 365 ProPlus
• Revised: Saturday 25th of February 2023 - 10:12 AM, [Australian Eastern Time (AET)]

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy