This tutorial follows on from the “Hello World!” with Excel VBA tutorial which is an introduction to the world of VBA. I urge you to quickly read that post before continuing here. This tutorial introduces key concepts such as declaring variables.
In this tutorial:
Required knowledge:
1. Starting point
Here is the code created in the “Hello World!” with Excel VBA tutorial:
Private Sub Workbook_Open() MsgBox "Hello World!" End Sub
2. Declaring & using a variable
The code above could be written as:
Private Sub Workbook_Open() Dim customGreeting as String customGreeting = "Hello World!" MsgBox customGreeting End Sub
Line 2: Dim
is an abbreviation of Dimension which is how variables are declared in VBA. In the above code snippet we are declaring a variable named customGreeting
that will hold information of the String
type. [1]
Line 3: The string Hello World!
is assigned to the variable customGreeting
using the assignment operator, the equals sign.
Line 4: The variable customGreeting
is used as the argument to the MsgBox
function [3].
This may well appear to be totally unnecessary — and in this case, it is — but you will soon learn the power of variables — we can’t program without them!
3. Option Explicit
Because we are now using variables, it is time to introduce the Option Explicit
statement. [3] This statement is used at the beginning of a Module to ensure that all variables in that Module are explicitly declared using the Dim
, Private
, Public
, ReDim
, or Static
statements. It must appear before any Procedures in the Module.
Option Explicit Private Sub Workbook_Open() Dim customGreeting as String customGreeting = "Hello World!" MsgBox customGreeting End Sub
To avoid having to manually add Option Explicit
, we can change an Access setting to do it for us:


- Select the Tools menu
- Select Options…
- Activate the Editor tab
- Activate the Require Variable Declaration option
- Click OK
Using a variable before it has been declared will now result in an error when the code compiles.
4. Branching If… Then… Else… logic
The above code exists in ThisWorkbook
which is an object in the VBA Project
. The simple MsgBox
function employed in the previous example is now expanded upon by simply adding more code to the original code. We are going to employ branching logic, something you should be familiar with from Excels logic functions :
Option Explicit Private Sub Workbook_Open() ' declare a variable Dim userResponse As String ' assign a value to the variable userResponse = MsgBox("Hello World!", vbOKCancel) If userResponse = vbOK Then ' all is good - open the Workbook as usual Exit Sub Else ' user cancelled - close this Workbook ActiveWorkbook.Close End If End Sub
- The line
Option Explicit
ensures that all variables are declared. It is considered good coding practice to declare your variables. - The
Dim
statement declares the variable userResponse as a string type. [2] - The value returned by the
MsgBox()
is then assigned to the variable userReponse using the=
assignment operator. - The value returned will be one of the VBA constants; in this case if the OK button is clicked the constant vbOK is returned.
- The logic
If ... Then ... Else ... End If
is a very basic logic structure that should be manageable for any user familiar with Excel’sIF()
function. [4]
5. Improvement?
You could arguably improve this code as follows:
Option Explicit Private Sub Workbook_Open() Dim userResponse As String userResponse = MsgBox("Hello World!", vbOKCancel) If userResponse = vbOK Then ' user clicked OK - all is good - open the Workbook as usual MsgBox("Welcome!") Else If userResponse = vbCancel Then MsgBox("Goodbye!") ' user clicked Cancel - close the Workbook ActiveWorkbook.Close Else ' we are not sure what the user did MsgBox("Computer says no!") ActiveWorkbook.Close End If End Sub
6. Next steps
- Learn to refactor code and use constants in the Declaring and using Constants in VBA tutorial
- The Excel VBA to select a cell based on its value tutorial will introduce you to the
ActiveSheet
property of theWorkbook
object, as well as using theDate
function.
References:
- o365devx, Microsoft Learn. (no date) Declaring variables (VBA). Available at: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/declaring-variables (Accessed: 11 April 2024).
- o365devx, Microsoft Learn. (no date) MsgBox function. Available at: https://learn.microsoft.com/en-gb/office/vba/Language/Reference/User-Interface-Help/msgbox-function (Accessed: 6 October 2022).
- o365devx, Microsoft Learn. (no date) Option Explicit statement (VBA). Available at: https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/option-explicit-statement (Accessed: 6 July 2024).
- o365devx, Microsoft Learn. (no date) If…Then…Else statement (VBA). Available at: https://learn.microsoft.com/en-gb/office/vba/language/reference/user-interface-help/ifthenelse-statement (Accessed: 6 July 2024).