VBA Tutorials

Visual Basic for Applications (VBA) is a programming language you can use to extend Microsoft Office. Working through a few sample VBA programs will spark your own ideas for maxing out Office.

This article's samples will help you automate these common Office tasks:

• Filtering data
• Navigating documents quickly
• Finding and replacing text
• Setting document options
• Creating custom formulas

Here's a quick VBA snippet that applies a custom filter to a selected column of data. To install and run this macro, do the following:

• Create a new Excel workbook, then enter the Visual Basic IDE (integrated development environment) by pressing alt-F11.
• Double-click the ThisWorkbook icon in the Project window.
• Paste the following program code into the code window (the blank area to the right of the Project window).

Sub myFilter()

Selection.AutoFilter Field:=1, Criteria1:="<=2", Operator:=xlAnd

End Sub

• Return to Excel (alt-F11), then create a column of numbers extending from 1 to 10 or so.
• Select the column, then press alt-F8 to bring up the list of macros..
• Double click the myFilter macro to run it. The macro will filter the numbers in the column..

Tip: Notice how easy it is to spot the macro's filtering criteria (i.e. "...Criteria1=..."). Try redefining the filter to work with different criteria.

Read more about Excel's filtering tool here.

Another practical use of VBA is to create placeholders you can quickly set and then return to later. This is especially useful for those big labyrinthine files you tend to get lost in.

Try this example for setting placeholders (AKA bookmarks) with VBA:

• Inside Word, press alt-F11 to enter the Visual Basic IDE and navigate to the current document's code window, as you did with the previous example.
• Paste the following code into the code window:

Public Sub setBmark()

ActiveDocument.Bookmarks.Add Name:="mybmark", Range:=Selection.Range

End Sub

Sub gotoBmark()

Selection.GoTo What:=wdGoToBookmark, Name:="mybmark"

End Sub

Return to Word and assign shut keys to both macros: press Office Button>Word options>Customize, then click the Customize button near the window's bottom. Enter an unused keyboard combination in the dialog box and press OK.

Try out your bookmark macros: open a document, press the shortcut key for the setBMark macro, navigate to another part of the document, then press the shortcut key for the gotoBmark macro. Word will return you to the set bookmark.

Bookmarks for Excel

Excel doesn't have Word's bookmarks per se, but something similar you can adapt the navigation macros to: names. Here's how to use VBA to create and return to placeholders using Excel's Names:

Open an Excel workbook and enter the VB IDE's code window using the instructions from the previous examples. Paste the following code into the code window:

Sub setName()

ActiveWorkbook.Names.Add Name:="_bmark", RefersTo:=Selection

End Sub

Sub gotoName()

Application.Goto Reference:="_bmark"

End Sub

Read more about assigning names to Excel's ranges here.

Set Excel's shortcut keys

Return to Excel and assign shortcut keys to both macros: press alt-F8, select the setName macro from the list, then press Options. Enter a memorable shortcut key (which must be used together with the control key). Press OK, then repeat this step to assign a new shortcut key to the gotoName macro.

Try out your macros by pressing the shortcut key for setName, navigating to another cell, then pressing the shortcut key for gotoName. Excel will return you to the placeholder you set with setName.