Excel VBA

Introduction To VBA: Macros And Automation Part 1

Our First Macros

Hello World!

A tradition dating back to the first programming courses ever delivered, the first macro we will create is going to be simple, and display the basic notation of the VBA language. We will look at the most basic way to display the text ‘Hello World!’ within Excel.

Begin by saving the workbook you are using to a convenient location, and naming the file ‘Excellency_VBA’, and you will want to choose the ‘.xlsm’ file type, as it is a macro-enabled workbook format which we need in order to save our work properly.

Once this has been done, in order to begin our first macro we need to create a module to work in. In the Project window, select the section with the title ‘VBAProject (Excellency_VBA.xlsm)’, and use the Insert menu to add a Module.

A new section of the file should have appeared called ‘Modules’ and within this folder structure a module named ‘Module1’ has also appeared. There should also now be a blank window in the Activity Window with the workbook name and module name in the title bar. It is in this window that we will write our code.

This is where the first of our essential keywords comes into play: Sub. The first line of our code will be the Sub keyword, followed by the name we want to give our subroutine, and a pair of parentheses. We will name this subroutine ‘ExHello’.

Type the following into the first line of the module:

Sub ExHello()

And hit ENTER. The editor will automatically create the End Sub closing argument for you.

Anything we now type between the Sub and End Sub lines, will run whenever we call for ExHello.

The next step is to tell the computer that we would like to display a specific text whenever the ExHello subroutine is called. We will do this using a message box, also known as a pop-up window.

The syntax used to call a message box is an abbreviated form of the term itself: MsgBox.

All we are required to do is put the text we want to display in between quotation marks next to the MsgBox function, and it will display correctly.

Type the following into the line between the Sub and End Sub lines of the module:

MsgBox “Hello World!”

Now we have all that we need in terms of code. But we will go one step farther by adding a button to call this subroutine. Navigating back to the workbook, under the Developer tab, there is the insert control. Select the button tool and draw a button to the size of your choosing.

When releasing the mouse button from the creation of the button, a menu will open asking if you would like to assign a macro to the newly created button. Select the ‘ExHello’ macro from the list, and press the OK button.

We can edit the button text by using our secondary click on it, and selecting the ‘Edit Text’ option. Let’s name it the ‘Welcome!’ button.

With all that set up, we can press the button to display our message box.

And there it is! We have successfully created our first macro.

The next step in this process is to learn about defining variables.