How to Get Started With Excel VBA

Microsoft Excel is a powerful application with features that go far beyond simple spreadsheet design. Perhaps you've enjoyed mastering complex formulas and data analysis, and you're ready for a new challenge. With Visual Basic for Applications (VBA), you can supercharge Excel. You can start using VBA immediately, with no programming experience necessary.

Instructions

Record a Macro

1. If you have ever recorded a macro, you are already using VBA. The Visual Basic editor translates your keystrokes into VBA commands. The resulting code is inefficient, but it can help you get familiar with VBA syntax and commands.

2. In Excel, record a simple macro. In Office XP, select Macro, Record New Macro from the Tools menu. (In Excel 2007, commands are on the Developer tab.) Change the macro name or leave the default, and press OK.

3. With the macro recorder running, type "Hello World" in cell A1. Apply Bold, Italic, and Underline, and change the font color to red. Double-click on the column separator in the header row, between columns A and B, to resize the cell's width to its contents.

4. Turn off the macro recorder by selecting Tools, Macro, Stop Recording.

5. Test the macro to make sure the message appears.

Examine Recorded Code

1. Right-click on the Sheet1 tab and select View Code, or press Alt-F11, to open the VB editor, and double-click on Module 1. Your macro code will look something like this:

   Sub Macro1()

   ActiveCell.FormulaR1C1 = "Hello World"
   Range("A1").Select
   Selection.Font.Bold = True
   Selection.Font.Italic = True
   Selection.Font.Underline = xlUnderlineStyleSingle
   Columns("A:A").EntireColumn.AutoFit
   Selection.Font.ColorIndex = 3

   End Sub

2. Note that the macro begins with "Sub" and ends with "End Sub." Every VBA subroutine begins and ends this way.

3. Find familiar keywords. Since you know what this macro does, you can figure out that "Selection.Font.Bold=True" changes the selection to bold font. You can also recognize the commands to resize the column and change the color.

4. Go back to Excel and try recording a few more simple macros, each time examining the code in the VB editor.

Set Up the VB Editor

1. Launch the VB editor by pressing Alt-F11.

2. Click on each menu item across the top to familiarize yourself with available options.

3. Set up your environment with options from the View menu. At a minimum, add the Properties Window and the Project Explorer. You can dock them to the left side of the screen by right-clicking and selecting "Dockable."

4. Try the context-sensitive Help feature at any time by pressing F1.

Design a Form

1. From the Insert menu, select Insert UserForm. The UserForm is your design canvas.

2. If the UserForm doesn't pop up, go to the Project Editor and double-click "Forms," then double-click UserForm1.

3. When the UserForm appears, you will also see the Toolbox, which contains controls you will use in creating your forms. For instance, you can include buttons, text boxes, drop-downs and labels. Hover your cursor over each to see the names.

4. Add some controls to your form. To add a text box, find it in the Tool Box, click on it, then move your cursor to the User Form and draw a rectangle on the form. You will see a shape that looks something like a window. This is where the user will enter his input.

5. Place more controls on the form. Experiment with resizing and rearranging. For some controls to work, they need to be programmed. For instance, an "OK" button needs an associated sequence of actions. Other controls, like labels, usually need little, if any, programming.

Program a Message Box

1. The "MsgBox" is preset; you will not need to design a form. But you will need some code. You can program "Yes," "No," "OK" and "Cancel" buttons, configure the prompt, and more.

2. This message box will warn the user that the file will close without saving when she clicks "Yes." If she clicks "No," the file will save before closing. If she selects "Cancel," the file will not close.

3. Launch the VB editor. From the Insert menu, insert a module; then, in the Project Explorer, double-click the new module. A code window will open up.

4. Type the following routine. (Do not type the text in the brackets that follow.)
   Sub MsgBoxTest() [Subroutines always begin with Sub and end with End Sub]
   myTitle = "Warning" [Assigns a value to "myTitle." When you use "myTitle" later, it's the same as typing "Warning."]
   myMsg = "Close without saving? All changes will be lost." [Assigns a value to "MyMsg."]
   Response = MsgBox(myMsg, vbExclamation + vbYesNoCancel, myTitle) [Defines the message box. It will contain "myMsg," a warning exclamation point, and Yes, No and Cancel buttons, and the title bar will read "Warning," because that's the value of "myTitle."]
   Select Case Response [Select Case assigns actions to various options.]
   Case Is = vbYes [If the user clicks Yes]
   ActiveWorkbook.Close SaveChanges:=False [Close without saving.]
   Case Is = vbNo [If the user clicks No]
   ActiveWorkbook.Close SaveChanges:=True [Save and close.]
   Case Is = vbCancel [If the user clicks Cancel]
   Exit Sub [Exit the subroutine. Don't do anything.]
   End Select [This ends the Select Case statement.]
   End Sub [Ends the subroutine]

5. Select Run, Run Sub/User Form from the menu. Your message box will be displayed; you have now successfully written a functioning VBA routine.

Tips & Warnings

• If your program doesn't run, you may need to adjust your security settings. In Excel 2007, on the Developer tab, select Macro Security, then choose Macro Settings. Check the box next to "Trust access to the VBA project object model," and click OK. If you're using Office XP, select Tools, Macro, Security; then, in the popup box, click the Trusted Sources tab. Put a check mark next to "Trust access to Visual Basic Project," and click OK.

References

• "VBA and Macros for Microsoft Excel;" Bill Jelen, Tracy Syrstad; 2004
• "Pro Excel 2007 VBA;" Jim DeMarco; 2008

Resources

• Microsoft Interactive Tutorial