Pin Me

Excel VBA Examples

written by: Darrin Koltow•edited by: Michele McDonough•updated: 12/5/2009

This article trains readers in the fundamentals of automating Excel with Visual Basic for Applications.

  • slide 1 of 8

    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.

  • slide 2 of 8

    Data Entry

    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.

    1 

    • 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. 2 

    Learn more about recording Excel macros here.

  • slide 3 of 8

    Data Formatting

    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.

    3 

    Learn more about Excel's formatting tools here.

  • slide 4 of 8

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

  • slide 5 of 8
    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.
  • slide 6 of 8

    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.

    Sub mysort()

    ActiveSheet.Sort.SortFields.Clear

    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

    End With

    End Sub

    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.

  • slide 7 of 8

    Document Navigation

    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:

    Sub mkbmark()

    Dim strRef

    strRef = "=" & ActiveSheet.Name & "!R" & Selection.Row & "C" & Selection.Column

    ActiveWorkbook.Names.Add Name:="_mybmark",

    RefersToR1C1:=strRef

    End Sub

    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:

    Sub gotobmark()

    Application.Goto Reference:="_mybmark"

    End Sub

    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.

  • slide 8 of 8

    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:

    Sub actoggle()

    Dim str As String

    Application.EnableAutoComplete = Not Application.EnableAutoComplete

    If (Application.EnableAutoComplete) Then str = "ON" Else str = "OFF"

    MsgBox "AutoComplete is now " & str

    End Sub

    4 

    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"

    End Sub

    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.



Popular Pages



More Info