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