# 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

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


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


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


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:

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.

### 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


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)

• Development platform: Excel 2016 (64 bit) Office 365 ProPlus
• Revised: Thursday 5th of July 2018 - 08:41 AM, [Australian Eastern Time (AET)]