GETTING STARTED with EXCEL VBA

Create a Macro-enabled Excel Workbook

VBA code should always be stored in a Macro-enabled workbook. Otherwise, you lose your work when you close the workbook. Open a new Excel workbook and select the Save As option:

Click on Browse. Navigate to the directory where you want to save this workbook. Enter the name for the workbook in the window next to File name. You may change the workbook name to whatever you wish and store it in any directory you wish.

Next, select Excel Macro-enabled Workbook from the Save as type menu and click Save. Note that the file extension changes to .xlsm.

Bring up the VBA Environment

The VBA environment can be accessed two ways. First, directly enter VBA and create a module with code. The second method records your keystrokes when inserting Excel macros into worksheets (saves code behind the scenes). The latter method often requires that you then tweak the code so generated.

We will first focus on directly creating modules with code before delving into recording keystrokes and changing code.

Select the Developer tab. Click the Visual Basic icon to the far left in the Ribbon.

This brings up the VBA environment.

VBA Properties and Project Windows

The Project Explorer window shows a directory of worksheets and modules. Here, no modules are showing because none have been added. (More on this later.)

The Properties window is where characteristics of the modules can be customized.

Insert a Module

To add code, we will need a module. To create one, click on the Insert drop-down menu and select Module.

A module window is added to the environment (in the workbook we named Examples.xlsm). This is a blank canvas where VBA code can be created and stored in either subroutines or functions. See Project Building Blocks: Subroutines and Functions for an introduction.

Create a HELLO WORLD Subroutine

Let's create a Hello World subroutine. Type Sub NameofSubroutine in a module and hit carriage return. VBA inserts open-and-closed parentheses at the end of this line and an End Sub statement below it.

In this example, we give our subroutine the name HelloWorld, but you can use any name you wish as long as it adheres to the VBA Naming Conventions. It's best to stick to alpha-numeric characters. Illegal characters in a subroutine or function name usually generates an error message. When this happens, VBA will not allow the routine to be fully created until the error(s) have been corrected or it just won't run.

Between the Sub and End Sub statements, ask VBA to generate a Hello World message by using the MsgBox command. Notice we have to enclose our character message in double quotes. Without quotes, VBA thinks that Hello and World are two variables. Since we have not set them to any value, the message will pop-up with a blank message. Single quotes are not allowed, as they are reserved for inserting comments in code.

Store Excel VBA Code

Although you can run code without storing it, it's best to save your work before running the code if you plan to use it again. To do so, click on the drop-down File menu and select Save Examples.xlsm.

Execute the Code

There are three ways to execute (run) VBA code. You can click on Run or the right-pointing arrow in the VBA environment.

The third method for running a VBA macro (subroutine) is from the Excel workbook (outside of VBA) using the Macros icon. Return now to the Excel workbook by clicking on the File drop-down menu and selecting Close and Return to Microsoft Excel.

Next to the Visual Basic icon in the Ribbon is the Macros icon. Click on it.

A Macro Selection dialog box appears and provides a list of all macros created in this Excel workbook. The Macro Name Bar shows the routine that is selected. This routine will be also be highlighted in the Macro List Window.

Select the HelloWorld subroutine that was just created.

Next, click the Run button to the right of the Macro Name Bar.

VBA generates a pop-up message box in which is written Hello World (with no quotes).

Notice that the pop-up has a button labeled "OK." This message box will persist until that box is clicked. To dispense with it, click that button.

Now that you know how to invoke VBA, you are ready to dive into the wonderful world of creating VBA routines to help streamline your Excel functionality.