VB Editor Archives - Page 2 of 2

This is the first in a series of How To posts that are targeted at Excel users who are unfamiliar with VBA programming. Topics will include

Using someone else’s macro
Recording your own macro
Fixing a recorded macro
Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
VBE settings
VBE features
Debugging and error proofing

Suppose you need Excel to do something beyond its normal capabilities, or just do something automatically to save you from hours of tedium. The answer is often a VBA procedure, or “macro”. You’re not a programmer, you don’t know a macro from a mackerel, so what do you do? You could ask a colleague, you can Google for help, you can ask on a forum or newsgroup. And the answer is, “Use this macro.” Oh boy. Now what do you do?

Let’s put the macro into your workbook and try it out. We’ll use the following simple macro for this exercise.

Sub TestMacro()
  ' center alignment
  Selection.HorizontalAlignment = xlCenter

  ' font: arial, 10 point, bold, blue color
  With Selection.Font 
    .Name = "Arial"
    .Size = 12
    .Bold = True
    .ColorIndex = 5
  End With
End Sub

The first step to using a VBA procedure is to open the VB Editor (VBE). There are two ways to get there:

Developer* tab > Visual Basic
Shortcut key combo Alt+F11

*If you don’t have a Developer tab on your ribbon, right-click between buttons in the ribbon and choose Customize Ribbon. In the list of ribbon tabs on the right of the dialog, check the box in front of Developer.

This opens the Visual Basic Editor window. Find your workbook in the Project Explorer pane. By default, the Project Explorer is docked along the left edge of the VBE window. If you don’t see it, activate it from View menu > Project Explorer.

Note the treeview of the workbook. The top level is the VBAProject, with the workbook name in parentheses. Under that is a folder entitled Microsoft Excel Objects. Within this is an element for the workbook (ThisWorkbook) and for the worksheet, with the sheet name in parentheses. There would be an element in this list for every worksheet and chart sheet in the workbook.

We need to insert a code module. This is easy: Insert menu > Module. Or right-click on any of the elements in the project’s treeview, choose Insert > Module.

A new folder, Modules, has opened under the VBAProject entry in the treeview, and an item called Module1 appears in the folder. A blank code module window has opened in the VB Editor.

Copy the macro code from wherever you found it, and paste it into this window. Red text indicates a syntax error, so you will need to figure out what’s wrong. Often it’s just a problem with line wrapping in email or on a web page, or maybe you need to change curly quotes to straight quotes.

This macro requires a range to be selected, so go back to Excel and select a range, then run the macro. This can be done in a number of ways:

In Excel
- Developer tab > Macros, select the Macro, and click Run
- View tab > Macros, select the Macro, and click Run
- Shortcut Key combo Alt+F8, select the Macro, and click Run
In the Visual Basic Editor
- Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
- Click in the macro with the mouse and press F5