Excel Programmer : Change Recorded Code

10/10/2010 9:36:51 AM
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.
Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=ws.Range("A1:C15"), PlotBy:= _
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
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
' Remove the last row (Total) from the range.
Set rng = ws.Range(ws.Cells(rng.Row, rng.Column), _
rng.SpecialCells(xlCellTypeLastCell).Offset(-1, 0))
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
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.
' 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
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.

  •  Excel Programmer : Record and Read Code
  •  Configuring Server Roles in Windows 2008 : New Roles in 2008
  •  Windows Server 2003 : Creating and Configuring Application Directory Partitions
  •  Windows Server 2003 : Configuring Forest and Domain Functional Levels
  •  Windows Server 2003 : Installing and Configuring Domain Controllers
  •  Manage Server Core
  •  Configure Server Core Postinstallation
  •  Install Server Core
  •  Determine Your Need for Server Core
  •  Install Windows Server 2008
  •  Windows Server 2008 : Configure NAP
  •  Incorporate Server Core Changes in Windows Server 2008 R2
  •  Decide What Edition of Windows Server 2008 to Install
  •  Perform Other Pre-Installation Tasks
  •  Developing Windows Azure Services that Use SQL Azure
  •  Creating Windows with Mixed Content
  •  Mixing Windows and Forms
  •  Exploring an Assembly Using ildasm.exe
  •  The Assembly/Namespace/Type Distinction
  •  Communicate Between Two Machines on the Same Network (WCF)
    Top 10
    Home Theatre Pc Software And Operating Systems (Part 4) - XBMC
    Home Theatre Pc Software And Operating Systems (Part 3) - Setting Up Windows Media Center
    Home Theatre Pc Software And Operating Systems (Part 2)
    Home Theatre Pc Software And Operating Systems (Part 1) - Windows Media Center
    Nokia's Extreme Megapixel Bid
    Storage, Screens And Sounds (Part 3)
    Storage, Screens And Sounds (Part 2)
    Storage, Screens And Sounds (Part 1)
    Microsoft ASP.NET 4 : Using the SqlProfileProvider (part 4) - The Profile API, Anonymous Profiles
    Microsoft ASP.NET 4 : Using the SqlProfileProvider (part 3) - Profiles and Custom Data Types
    Most View
    Managing and Administering SharePoint 2010 Infrastructure : Using Additional Administration Tools for SharePoint
    Binding Application Data to the UI objects in Silverlight
    iPhone Application Development : Getting the User’s Attention - Generating Alerts
    Understanding and Using Windows Server 2008 R2 UNIX Integration Components (part 2)
    iPhone Application Development : Creating and Managing Image Animations and Sliders (part 3) - Finishing the Interface
    Cisco Linksys X3000 - The Link to Connectivity
    HP LaserJet Pro CM1415fnw - Print from The Clouds
    Building Your First Windows Phone 7 Application (part 2) - Using Your First Windows Phone Silverlight Controls
    Determine Your Need for Server Core
    Mobile Application Security : Bluetooth Security - Overview of the Technology
    Using System Support Tools in Vista
    Windows 7 : Using Windows Live Calendar (part 3) - Scheduling Appointments and Meetings & Viewing Agendas and Creating To-Do Lists
    Advanced ASP.NET : The Entity Framework (part 3) - Handling Errors & Navigating Relationships
    Graham Barlow: the Apple view
    Ipad : Presentations with Keynote - Adding Transitions (part 2) - Object Transitions
    Windows Server 2003 : Troubleshooting Group Policy
    Microsoft XNA Game Studio 3.0 : Controlling Color (part 2)
    Building the WinPE Image
    Programming the Mobile Web : HTML 5 (part 3) - Offline Operation
    Windows Phone 7 Development : Using Culture Settings with ToString to Display Dates, Times, and Text