Programming Excel with VBA and .NET

Change Recorded Code

Recorded code is a great way to learn, but it’s not really a program. Real programs are much more flexible, and recorded code always has the following limitations:

Follows a linear path

Excel can’t infer logic from the user’s actions—even if the user repeats an action, Excel only records the action twice. It doesn’t know how to record “repeat until end” or “do this task if...” Excel can “replace all” and perform other global actions, but that’s still linear logic.

Actions apply to the active item

Excel bases recorded actions on whatever worksheet, range, or chart is currently selected by the user. If you want your code to work with other sheets or ranges, you need to either change the active selection or add object references that tell the code which items to work with.

Uses cell addresses

For example, Range("A1:C16"). Although Excel keeps references on worksheets up-to-date, Excel can’t update addresses in code. That means if your data is moved on the worksheet, the code won’t work correctly. To fix this, use Excel range properties or named ranges instead of addresses in code.

Methods include all the default arguments

That means lines of code are sometimes longer and more complicated than they really need to be. You can often simplify recording by removing unneeded default arguments.

Doesn’t use variables

Most programs create names to identify things that can change as the code executes. These names are called variables. Recorded code doesn’t use variables because the logic is always linear—variables are required only if the code repeats or makes decisions.

So if you want the code you just recorded to repeat the formatting and charting tasks for all worksheets in your workbook, you’ll need to make a few changes. I’ll do that in a number of steps so it’s clearer. First, add the logic to repeat the formatting for each worksheet:

    Sub Macro1( )
    '
    ' Macro1 Macro
    ' Macro recorded 5/26/2004 by Jeff
    '
     
    '
        For Each ws In Worksheets '<--- Added to repeat actions for each worksheet.
            Range("A1:C16").Select
            Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
                :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
            Range("A1:C15").Select
            Charts.Add
            ActiveChart.ChartType = xlColumnClustered
            ActiveChart.SetSourceData Source:=ws.Range("A1:C15"), PlotBy:= _
                xlColumns
            ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name '"2002"
            ' Change Name to match the worksheet's name ----------^
        Next    '<--- End of actions to repeat.
    End Sub

The preceding For Each statement tells Excel to repeat the following task for every worksheet in the workbook. The Next statement ends the set of tasks to repeat. In programming, this kind of logic is called a loop because the flow of execution runs around and around in a circle until told to stop. In this case, the loop stops after it reaches the last worksheet in the workbook.

There’s something subtle about the previous code: the For Each statement gets a reference for each worksheet as it loops and stores it using the name ws. We need to use that name (called a variable) to get the location where Excel should insert the chart. Thus, ws replaces Sheets("2002"), so each time Excel creates a chart, it inserts it on the right worksheet. Remember to search for literal references like this and replace them with variables any time you are adding logic to recorded code.

That was step 1, adding logic. If you run the code now, Excel will repeat the task for each worksheet in your workbook and it will work correctly as long as each worksheet has its sales figures in the range A1:C16. If that’s not the case, the code won’t format or chart the right range. To handle data in other locations, change Range("A1:C16") to use Excel’s UsedRange property as shown here:

    Sub Macro1b( )
    '
    ' Macro1b Macro
    ' Change absolute ranges to relative ones.
    '

    '
        For Each ws In Worksheets
            'Range("A1:C16").Select
            Set rng = ws.UsedRange '<-- Get all the cells with data.
            'Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
            '    :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
            ' Use reference (below) rather than Selection (above).
            rng.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
                :=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
            'Range("A1:C15").Select
            ' Remove the last row (Total) from the range.
            Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
              rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))
            Charts.Add
            ActiveChart.ChartType = xlColumnClustered
            'ActiveChart.SetSourceData Source:=Sheets("2002").Range("A1:C15"), PlotBy:= _
            '  xlColumns
            ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
            ' Use the range reference here ----^
            ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name
        Next
    End Sub

Tip

UsedRange was introduced in Excel 97, and it is one of those incredibly useful properties that you’ll be seeing over and over again.

The preceding changes use the UsedRange property to get all the cells on the worksheet that contain data. The hard part comes with the second change that removes the Total row from the range to chart:

    Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
      rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))

Wow, that’s complicated! To break it down a bit, ws.Cells (rng.Row, rng.Column) gets the first cell in the range, and rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0) gets the last cell minus one row (omitting the Total row). The enclosing ws.Range(...) method combines those start and end points into a rectangular block of cells. Don’t worry if you don’t completely understand at this point; you’ll find much more material on working with ranges of cells in later chapters.

Finally, I changed the chart’s Source argument to use this new range. Now if you run the code, Excel will format and chart sales data on each of the worksheets regardless of where the data is on each worksheet. The code is still a bit rough, though, because it doesn’t declare the variables it uses, it includes some arguments that aren’t really needed, and it is still named Macro1, which isn’t descriptive at all. Here’s a cleaned-up version with all the fixes:

    Sub FormatAndChart( )
    ' AutoFormats and Charts all of the worksheets in a workbook.
    ' Designed to work with Sales Data tables.
    ' 5/28/04 by Jeff Webb
    '
        Dim rng As Range, ws As Worksheet
        ' Repeats actions for all Worksheets in the workbook.
        For Each ws In Worksheets
            ' Get the cells with data in them.
            Set rng = ws.UsedRange
            ' Apply AutoFormat
            rng.AutoFormat Format:=xlRangeAutoFormatSimple
            ' Omit the Total row from the range.
            Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
              rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))
            ' Create a chart.
            Charts.Add
            ' Set chart properties.
            ActiveChart.ChartType = xlColumnClustered
            ActiveChart.SetSourceData Source:=rng, PlotBy:=xlColumns
' Insert the chart on the worksheet.
            ActiveChart.Location Where:=xlLocationAsObject, Name:=ws.Name
        Next
    End Sub

Tip

Declaring the variables enables handy Visual Basic features like Auto Complete (I discuss that later).

You might notice that I also rewrote the comments in this final version. It’s always a good idea to write out in words what your code is doing. Even if the code is only for your personal use, it’s surprising how easy it is to forget what you did.