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.

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 DimPrivatePublicReDim, 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:

Activating the Require Variable Declaration setting in the VBA Editor.The Require Variable Declaration setting in the VBA Editor.
  1. Select the Tools menu
  2. Select Options…
  3. Activate the Editor tab
  4. Activate the Require Variable Declaration option
  5. 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
  1. The line Option Explicit ensures that all variables are declared. It is considered good coding practice to declare your variables.
  2. The Dim statement declares the variable userResponse as a string type. [2]
  3. The value returned by the MsgBox() is then assigned to the variable userReponse using the = assignment operator.
  4. The value returned will be one of the VBA constants; in this case if the OK button is clicked the constant vbOK is returned.
  5. The logic If ... Then ... Else ... End If is a very basic logic structure that should be manageable for any user familiar with Excel’s IF() 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


References:

  1. 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).
  2. 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).
  3. 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).
  4. 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).

By MisterFoxOnline

Mister Fox AKA @MisterFoxOnline is an ICT, IT and CAT Teacher who has just finished training as a Young Engineers instructor. He has a passion for technology and loves to find solutions to problems using the skills he has learned in the course of his IT career.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.