Excel VBA Examples
This article trains readers in the fundamentals of automating Excel with Visual Basic for Applications.
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.
- 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:
ActiveCell = "first name"
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.
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:
Selection.Style = "mystyle"
- 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.
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.
Knowing how to do basic operations like sorting and moving around a workbook's worksheets is essential if you want to be productive with Excel. Once you have those skills, you're poised to excel at Excel--by automating it with Visual Basic for Applications. Get started here.
Data Manipulation: Sorting
Sorting data is another essential Excel task that VBA is well equipped to handle. The following script will sort the current selection. To use it, press alt-F8 to open the Visual Basic IDE. Double-click on the ThisDocument icon in the Project window to bring up a fresh code window. In the code window, paste the following subroutine.
ActiveSheet.Sort.SortFields.Add Key:=Selection.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort .SetRange Selection: .Header = xlGuess: .MatchCase = False
.Orientation = xlTopToBottom: .Apply
Test the sorting macro
In an Excel worksheet, create a columns of numbers and select it. Run the mysort macro by pressing alt-F8, then double-clicking the mysort macro in the macro list. Excel will sort your column of numbers.
Tip: You can change the sort order from ascending to descending by changing Order:=xlAscending to Order:=xlDescending.
Get details on sorting with Excel here.
Here's an example of using VBA to navigate quickly through your worksheets.
Let's say you're working hurriedly with a table on one worksheet, and need to do a task on another sheet. You need to remember where you were working in the first worksheet, but it's a hassle to type out a name in the Name Box and then sift through the names later to find that particular one. Instead, write one macro to make a pre-defined bookmark and another to return you to that bookmark. You'll assign a shortcut key to both.
Enter the VBA code to make a bookmark
Enter the following subroutine in the VB IDE code window:
strRef = "=" & ActiveSheet.Name & "!R" & Selection.Row & "C" & Selection.Column
Return to Excel and assign a shortcut key to the mkbmark macro, using the procedure from the Data Entry example in this article. You can now bookmark your current location in the worksheet by pressing the shortcut key, but you still need a macro and associated shortcut key to return to that bookmark. Here's the macro, which you'll enter in the IDE as you did the macro to make the bookmark:
Return to Excel to assign a new shortcut key to gotobmark. Now you can quickly make, and later return to, a placeholder.
Read more about using named references here.
Toggle Excel Options with VBA
Use VBA to quickly toggle application options. For example, sometimes you'll want the AutoComplete feature turned on to enter text quickly, but other times, AutoComplete can get in the way of your entering data. Turn AutoComplete on or off with this VBA code:
Dim str As String
Application.EnableAutoComplete = Not Application.EnableAutoComplete
If (Application.EnableAutoComplete) Then str = "ON" Else str = "OFF"
MsgBox "AutoComplete is now " & str
Learn more about Excel's options here.
Special Macros for workbook open and close
You can have VBA execute when you first open and close a workbook. Enter the following macro in the VB IDE to see a useful application of this feature.
Private Sub Workbook_Open()
If (Weekday(Now) = vbWednesday) Then MsgBox "REMEMBER TO DO PAYROLL TODAY"
Going further with VBA
VBA can do much, much more than the examples in this article, and much more than you can do without VBA. The time you invest in learning this language will pay off in droves when project deadlines approach.
More To Explore