Recorded Macro Error with Bubble and Stock Charts

You have a chart that you make every week or every month, and you’re tired of making it from scratch every time. So you record a macro to automate the process.

When you run the macro to make your chart the next time, you get no chart. Instead you get an ugly error message.

That’s frustrating. All you did was record and rerun a macro. You didn’t even change the code, so at least this once, you couldn’t have messed it up. And still it crashed.

Let’s examine the macro recorder code.

Sub RecordedMacro_MakeBubbleChart()
'
' RecordedMacro_MakeBubbleChart Macro
' Macro recorded 1/26/2010 by Jon Peltier
'

'
    Charts.Add
    ActiveChart.ChartType = xlBubble
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C7"), _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

The macro recorder follow the order of the Excel 2003 chart wizard, putting the chart type before the chart source data. The steps given by the recorder work fine for most chart types, but a few chart types cannot be correctly assigned until the chart has been populated with sufficient data. In particular, code which creates stock charts and bubble charts will fail if you set the chart type before the chart is populated with data.

To make the code run properly without crashing, all you need to do is switch the SetSourceData and ChartType commands. The following code works fine.

Sub RecordedMacro_MakeBubbleChart()
'
' RecordedMacro_MakeBubbleChart Macro
' Macro recorded 1/26/2010 by Jon Peltier
'

'
    Charts.Add
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:C7"), _
        PlotBy:=xlColumns
    ActiveChart.ChartType = xlBubble
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

This code isn’t particularly efficient, but at least it works. You can improve it following the suggestions in How To Fix a Recorded Macro.

This error has existed in Excel from the first modern version (Excel 97) through Excel 2003. Microsoft must have received inquiries about the problem, and they addressed it with a KB article, How to Use a Visual Basic Macro to Create a Bubble Chart., which propagated through versions 5/95, 97, 2000, 2002, and 2003. The author of the article didn’t think to rearrange the two VBA commands, so he wrote a long procedure that made an XY chart, and for markers, the chart used custom AutoShape circles sized according to the bubble values. It’s a nice procedure and a nice technique to know, but also a long voyage to go such a short distance.

When Microsoft rebuilt the charting infrastructure for Excel 2007, they changed some of the code produced by the macro recorder. Here is the Excel 2007 bubble chart macro:

Sub RecordedMacro_MakeBubbleChart()
'
' RecordedMacro_MakeBubbleChart Macro
' Macro recorded 1/26/2010 by Jon Peltier
'

'
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$C$5")
  ActiveChart.ChartType = xlBubble
End Sub

Excel 2007′s macro recorder has been criticized because it didn’t capture any actions that affected the new shapes which comprise the chart. But in the simple macro above, we see that some improvements were made to Excel 2007′s macro recorder:

• In earlier versions of Excel, a separate chart sheet is made, then later inserted in the worksheet. In 2007, a chart is directly added to the worksheet’s Shapes collection. That’s more efficient.
• More important, the chart type is not set until after the chart is populated with data. The error in recorded macros no longer occurs.

Related Posts:

• Make Your Recorded Macro Independent of Which Sheet is Active
• How To: Fix a Recorded Macro
• How To: Record Your Own Macro
• How To: Use Someone Else’s Macro
• Label Each Series in a Chart
• How To: Assign a Macro to a Button or Shape