PTS Blog

Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS Blog

 

Main menu:

 
Peltier Tech
Chart Utilities

PTS Waterfall Chart Utility
PTS Cluster-Stack Column Chart Utility
PTS Box and Whisker Chart Utility
PTS Marimekko Chart Utility
PTS Dot Plot Utility
PTS Cascade Chart Utility

Subscribe

Site search

Subscribe

Site search


Recent Posts

Recently Commented

Popular Posts

Archive


 

Categories


 

Buy me a coffee

If this topic or the whole blog has been helpful, support further development by treating me to a cup of coffee.


Privacy Policy

Creative Commons License
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

Make Your Recorded Macro Independent of Which Sheet is Active

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

When you run a recorded macro, it may work great, except it keeps referring back to the original worksheet. It also refers back to the original data range. Both of these situations can be remedied by editing of the recorded macro.

In How To: Fix a Recorded Macro I showed how to make a recorded macro more efficient by omitting and combining statements and eliminating the selection of objects during macro execution. This is a more specific example that makes the macro independent of the conditions (active sheet and selection) when it was recorded.

In this example, a macro is recorded after data is selected to capture the steps required to insert a chart object in the active worksheet. This recording was made in Excel 2003; the steps in 2007 are similar, and I’ll cover them in another post.

Here is our original data in C7:F13 of Sheet1:

Data for recorded macro /></p> <p>I recorded this macro while I created a chart, without changing any other formatting.</p> <pre>Sub Macro1() ' ' Macro4 Macro ' Macro recorded 3/16/2009 by Jon Peltier '  '     Charts.Add     ActiveChart.ChartType = xlXYScatterLinesNoMarkers     ActiveChart.SetSourceData Source:=Sheets(

The Recorded Macro

I recorded this simple macro while creating the chart, without changing any formatting:

Sub Macro1()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
'

'
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C7:F13")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
 

The worksheet name and selected range appear hard-coded in this macro. No matter what sheet is active or what range is selected, the chart will always appear on Sheet1 using the data in C7:F13 of Sheet1.

I will describe a couple ways to change this, both involving variables which are declared and assigned before the chart is created.

Fixing the Macro with String Variables

The sheet name and range address are present in the recorded macros as simple text. We can define string variables to contain the sheet name and the range address, as shown in this fixed macro.

Sub Macro1a()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
' Fixed to use text variables for sheet and range

'
    Dim sSheetName As String
    Dim sDataRange As String

    sSheetName = ActiveSheet.Name
    sDataRange = Selection.Address

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets(sSheetName).Range(sDataRange)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheetName
End Sub
 

Fixing the Macro with Object Variables

We can also use object variables, that is, a worksheet object for the active sheet and a range object for the data, as in the following fixed up macro.

Sub Macro1b()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
' Fixed to use object variables for sheet and range

'
    Dim wksData As Worksheet
    Dim rngData As Range

    Set wksData = ActiveSheet
    Set rngData = Selection

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=rngData
    ActiveChart.Location Where:=xlLocationAsObject, Name:=wksData.Name
End Sub
 

By adding only a few lines of code, the recorded macro has been made much more flexible. The choice whether to use string or object variables is not important in this case. In more complex procedures, one or the other approach may be advantageous, if a particular variable type is more useful later in the procedure.

Possibly Related Posts:

Bookmark and share this entry:
  • Digg
  • del.icio.us
  • Facebook
  • Technorati
  • Twitter
  • StumbleUpon
  • Google Bookmarks
  • Reddit
  • MySpace
  • Slashdot
  • LinkedIn
  • Yahoo! Buzz

Learn how to create Excel dashboards.

Comments


Comment from LEM
Time: Friday, October 16, 2009, 4:28 pm

Jon,

I have come to you before when I have hit dead ends, and I am hoping you can help me out with something I am running into with 2007. I wasn’t sure where to post this, but am in need of help.

In PowerPoint 2007 I have the option to paste my chart in, but with the entire workbook behind it (so that the user can actually go in and update everything themselves). My problem is, I get a PERSONAL.XLSB locked for editing error (when it is a chart). I have no idea how to get around this, and have been looking for answers all over… I saw mention of potentially creating my own Add-In?

Also, when I copy and paste my chart into PowerPoint, I will lose any additional text boxes containing formulas that I may have added. Any ideas (oh, and I don’t want it to be a picture…)?

I am missing 2003 about now, but hope you will be able to help ease the transition with tips for these things!

Thank you!!


Comment from Jon Peltier
Time: Friday, October 16, 2009, 6:01 pm

LEM -

I haven’t used Excel and PowerPoint 2007 together much, so I only know of the problems, not the workarounds. The deal with the personal.xlsb is that, if you change it to an add-in, it can be open multiple times without conflict. Which means it’s open in the instance in PowerPoint. That might be good or bad. So might having the workbook travel with the chart. Good to allow adjustments, bad to keep proprietary information secret.

Write a comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Read the PTS Blog Comment Policy.





Subscribe without commenting

PTS Waterfall Chart Utility PTS Cluster-Stack Column Chart Utility PTS Box and Whisker Chart Utility PTS Marimekko Chart Utility PTS Dot Plot Utility PTS Cascade Chart Utility

Create Excel dashboards quickly with Plug-N-Play reports.