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

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 three ways to get there:

• Tools menu > Macro > Visual Basic Editor
• Visual Basic Editor button on the Visual Basic toolbar
• Shortcut key combo Alt+F11

Find your workbook in the Project Explorer window. 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.

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.

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:

• Tools menu > Macro > Macros, select the Macro, and click Run
• Shortcut Key combo Alt+F8, select the Macro, and click Run
• Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
• Click in the macro with the mouse and press F5

This is the first exposure most people have to VBA and the VB Editor. In a series of upcoming posts, I’ll cover the following VB topics:

• 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

Possibly Related Posts:

• How To: Record Your Own Macro
• How To: Assign a Macro to a Button or Shape
• How To: Assign a Macro to a Toolbar or Menu
• How To: Fix a Recorded Macro
• Improved Macro Security Warning in Excel 2010
• How To: Assign a Macro to an ActiveX Control
• Highlight a Series with a Click or a Mouse Over
• Chart Event Class Module to Highlight a Series
• VB Editor Settings
• Indispensable Excel Utilities