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:


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

Component Description
Sub procedure_name()
.
.
End Sub
Compulsory: Sub procedure-name is the declaration statement and the first line of the procedure. This is paired with the End Sub statement. All code statements must be written between these two statements
argument_listOptional:
frequently omitted. If arguments or parameters are included they operate in the same way as function arguments
Public | PrivateOptional:
Public or omitted:
the procedure will be listed on the Excel Macro list
the procedure is available to all modules in the project

Private:
the procedure is not listed in the Excel Macro dialog box
the procedure is only available to other procedures in the same module.
Exit SubOptional:
Normally associated with logical statements or error checking. Allows code execution to terminate, and the remaining code statements are not run

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.



Code 1: Sub procedure WelcomeMsg1 contains a one line executable 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.



Code 2: Sub procedure WelcomeMsg2 with explicit Public scope declaration

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



Code 3a: Sub procedure WelcomeMsg3 with one argument of type string

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
sub-with arguments
Fig 1: the Call from WelcomeMsg31 passes the Msg argument (a text string), by reference, to the called procedure, WelcomeMsg3.

1.2 Running a sub procedure


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


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)


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:


Code 3b: Sub procedure WelcomeMsg3 with one argument of type string passed by reference (ByRef) - the default setting
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.


Code 4a: Sub procedure WelcomeMsg32 with procedure scope variable Msg, having the same name as the procedure scope variable in WelcomeMsg3

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

Code 4b: Sub procedure WelcomeMsg33 with procedure scope variable Msg, having a different name to the procedure scope variable in WelcomeMsg3
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.


Code 5: Sub procedure WelcomeMsg34

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.


ByVal ByRef
Fig 2: Passing arguments ByRef and ByVal - ByRef can change the caller variable as seen by the concatenated "finance.com". The ByVal caller variable is unchanged

2.2 Private scope, Exit Sub statement and Static declaration



Code 6: Sub procedure DemoExitSub with Private and Static declaration, and examples of Exit Sub
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

VBA functionDescription
Empty Used as a logical expression such as Var = Empty; returns a Boolean TRUE when variant type is Empty or 0, or numeric type is 0, otherwise it returns FALSE
IsEmpty(value) Returns a Boolean value TRUE if cell is empty or variant variable has not been initialized, otherwise it returns FALSE
TypeName(VarName) Returns a String with information about a Variant containing a variable.
The return string can be one of: objecttype, Byte, Integer, Long, Single, Double, Currency, Decimal, Date, Boolean, Error, Empty, Null, Object, Unknown, or Nothing
 VarType(VarName) Returns an Integer representing the subtype of a variable.
Return values: vbEmpty (0); vbNull (1); vbbInteger (2); vbLong (3); vbSingle (4); vbDouble (5); vbCurrency (6); vbDate (7); vbSDtring (8); vbObject (9); vbError (10); vbBoolean (11); vbVariant (12) [only with arrays of variants]; vbDataObject (13); vbDecimal (14); vbByte (17); vb LongLong (20); vbUserDefinedType (36); or vbArray (8192)