Excel VBA Examples

Microsoft Excel is a powerful tool all by itself, but you can make it more powerful by learning how to automate it with Visual Basic for Applications (VBA).

When starting to learn VBA, consider the fundamental Excel tasks that you likely have to do regardless of your project type:

• Data entry
• Data formatting
• Data manipulation: sorting and filtering
• Document navigation
• Setting workbook or application options

Explore how VBA can speed up each of these tasks by trying out the following examples.

VBA can speed worksheet data entry in several ways. Try this example to learn one basic way. We'll record a macro that you can use to rapidly enter a typical column name like "first name."

• Create a new workbook in Excel (press control-N).
• Begin macro recording: press the small, rectangular icon tucked in the southwest corner of the Excel workspace.
• Enter "myMac" for the name of the macro and click OK to begin recording.

click to enlarge

• Select any clear worksheet cell and type "first name," followed by the Enter key.
• Stop recording by clicking the squarish icon that's replaced the record-macro icon.
• Clear the worksheet (control-A, delete), then replay the macro: press alt-F8, followed by double-clicking the "myMac" entry in the macro list.
• Notice the result: Excel re-inserts the "first name" text you typed.

Improving the macro

The problem with this macro is that it will insert the same text in only one specific place every time you run it. If you need to enter "first name" into several different worksheet cells, this macro, as it stands, is no good.

Revise the macro by first entering its habitat, the Visual Basic IDE (integrated development environment): press alt-f8 to call up the macro list containing your myMac macro. Select that macro and press the Edit button. You'll enter the IDE at the definition of myMac.

Change myMac to read as follows:

Sub myMac()

ActiveCell = "first name"

End Sub

Return to the Excel worksheet and replay myMac using the instructions given earlier. Click several different worksheet cells, running myMac for each new selection. Notice that the macro now enters "first name" in the currently selected cell instead of the same cell each time.

Tip: Use keyboard shortcuts

Replaying macros by using alt-F8 all the time is not nearly as speedy as a keyboard shortcut. Make a shortcut key for myMac by going to alt-F8 (one last time!), selecting myMac, and clicking the Options button. Enter a key which, when pressed with Control, will fire your macro.

click to enlarge

Learn more about recording Excel macros here.

VBA is equally adept at formatting data. Here's a scenario to illustrate that: Let's say you've created a specific style with lots of specialized formatting, including boldface, italics, highlights and borders. You've wisely saved the style using Home>Cell Styles. But, now you want to apply that style to many other cells, and quickly. Without a macro, you have to select each range of cells you want formatted and choose Home>Cell Styles>(your custom style).

Excel doesn't have keyboard shortcuts for quickly applying custom styles, as Word does. But you can install this feature with macros. Here's how:

Use VBA to apply custom styles quickly

• Let's first define a sample custom style: enter some text in any cell, then format it any way you want: highlight it, make it bold, italicize it.
• Select Style>Cell Styles>New Cell Style, and name the new style "mystyle."
• Enter the Visual Basic IDE by pressing alt-f11. Double-click the ThisWorkbook icon in the Project window.
• In the code window, enter this subroutine:

Sub applysty()

Selection.Style = "mystyle"

End Sub

• Return to the workbook and press alt-f8 to bring up the macro list. Select "applysty" and press Options. Enter a shortcut key, then close all dialog boxes to return to the worksheet.
• Enter any text in a new, blank cell.
• Press the shortcut key for the applysty macro. The text will be formatted with the "mystyle" style.

click to enlarge

Learn more about Excel's formatting tools here.

Read on to the next page to find more VBA tips that can be used to improve Excel workflow.