I find Visual Basic for Applications (“VBA”) most useful in Excel. I have created many small enhancements to make my larger spreadsheets easier to use. Here follows a simple “Hello World!” introduction to VBA for Excel. In this exercise, we are simply going to have a pop-up message appear when we first open an Excel Workbook. This simple task will allow me to point out one or two important basics.

1. Activate the Developer toolbar

To access the VBA Editor you need to enable the Developer toolbar:

  1. Click on the File menu and select the Options option
  2. Select the Customize Ribbon option
  3. Activate the Developer tab
  4. Click on OK

2. Create a Macro-enabled workbook

  1. Save the current workbook, or create a new workbook, as a macro-enabled workbook: File » Save As » Excel Macro-Enabled Workbook (*.xlsm)
  2. Name the Workbook Hello World and save it.
  3. Navigate to the Developer menu and click on the Visual Basic button

3. Code

Time to write some code:

  1. The Visual Basic Editor will open
  2. In the Project window on the left, double-left-click on the This Workbook object
  3. At the top of the code window, select the Workbook option from the drop-down list (which displays (General) by default)
  4. The default Procedure is Open() so it need not be changed
  5. Type Option Explicit at the top
  6. Type: MsgBox "Hello World!" between the lines Private Sub Workbook_Open() and End Sub
Option Explicit

Private Sub Workbook_Open()
    MsgBox "Hello World!"
End Sub

Line 1: The Option Explicit code forces you to declare variables. It is not necessary at this stage (we are not using any variables), but it is good practice.

Line 3,5: “A Sub procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements that perform actions but don’t return a value. A Sub procedure can take arguments, such as constants, variables, or expressions that are passed by a calling procedure. If a Sub procedure has no arguments, the Sub statement must include an empty set of parentheses.” [1]

Line 4: MsgBox "Hello World!" results in a pop-up with the text specified and an OK button. [2]

Save and close the Visual Basic Editor & the workbook.

4. Test

When you re-open the Workbook, there will most likely be a security warning:

The Security warning dispalyed when Macros are disabled in an Excel workbook.
Security Warning when Macros are disabled
  1. Warning
  2. Enable Content button

The message should appear in a popup once the Enable Content button has been clicked:

5. Private

What does it mean the sub-routine is Private?

Once you have dismissed the greeting by clicking on the OK button, navigate to the Developer tab and click on the Macros button. Do you see that your sub-routine, Workbook_Open, does not appear?

Open the sub-routine in the VBA Editor and delete the word Private.

Now the name of your sub-routine appears in the list of Macros.

A Private sub procedure can only be called from the module in which it exists, and it is not visible or accessible in the Excel interface.

6. Another way

Follow the steps in the tutorial Record a Macro in Excel to see how recording a Macro and inspecting the VBA generated is a great way to inspect and learn VBA.

7. Next steps

You are now ready for the next step: VBA for Excel: add a little logic.


References:

  1. o365devx (no date) Writing a Sub procedure (VBA)(VBA) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/writing-a-sub-procedure (Accessed: 10 March 2024).
  2. o365devx (no date) MsgBox constantsMicrosoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/msgbox-constants (Accessed: 12 April 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

Your email address will not be published. Required fields are marked *