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
- Sub procedures (also called Macros) - covered in this module
- Function procedures
- 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
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
|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|
frequently omitted. If arguments or parameters are included they operate in the same way as function arguments
|Public | Private||Optional:|
Public or omitted:
the procedure will be listed on the Excel Macro list
the procedure is available to all modules in the project
the procedure is not listed in the Excel Macro dialog box
the procedure is only available to other procedures in the same module.
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
WelcomeMsg1contains a one line executable statement
Sub WelcomeMsg1() MsgBox "Welcome to excelatfinance.com" End Sub
About Code 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
- Line 2: A one line executable statement. The VBA MsgBox function displays a text string in the Excel window
- 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
- 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
WelcomeMsg2with explicit Public scope declaration
Public Sub WelcomeMsg2() MsgBox "Welcome to excelatfinance.com (2)" End Sub
About Code 2
- Lines 5: Public scope. This is the default value and is usually omitted
- 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)
- 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
WelcomeMsg3with 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
- 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
- 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
- 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
- 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
WelcomeMsg3with 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
WelcomeMsg32with procedure scope variable Msg, having the same name as the procedure scope variable in
Sub WelcomeMsg32() Dim Msg As String Msg = "Welcome to excelatfinance.com (32 & 3)" WelcomeMsg3 Msg End Sub
Code 4b: Sub procedure
WelcomeMsg33with procedure scope variable Msg, having a different name to the procedure scope variable in
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
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
DemoExitSubwith 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
- Line 80: Private scope. This must be set explicitly
- 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
- 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.
- 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)
- Lines 87 and 88:
can also be written as:
If IsEmpty(Price) Then Price = 0# If IsEmpty(Quantity) Then Quantity = 0#
- 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
|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: Saturday 14th of October 2017 - 07:52 AM, [Australian Eastern Time (AET)]