macro to create charts in worksheets with arbitrary names

  • Thread starter Thread starter z.entropic
  • Start date Start date
Z

z.entropic

I'm trying to write a macro to create and modify charts in
spreadsheets with many worksheets. The problem I cannot
solve is that the macro uses the current worksheet name
(here Channel_1) in the code, as in the following short
example:

' Keyboard Shortcut: Ctrl+a
'
Range("G2:H11").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets
("Channel_1").Range("G2:H11")
ActiveChart.Location Where:=xlLocationAsObject,
Name:="Channel_1"
End Sub

which prevents it from running in a different worksheet. I
tried invented statements like .ActiveWorksheet. etc, but
it didn't work...

How to make the worksheet name generic to the macro
without having to rename the worksheet before the macro is
run?

z.entropic
 
Excel doesn't have an ActiveWorksheet, but it does have an ActiveSheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Tried that, too--to no avail!

z.entropic
-----Original Message-----
Excel doesn't have an ActiveWorksheet, but it does have an ActiveSheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______



.
Click to expand...
 
The Charts.Add creates a new chart in its own sheet. ActiveSheet at
that point refers to the newly minted chartsheet!

Use something like the untested:

dim SrcSheet as worksheet, aChart as chart
set srcsheet=activesheet
set achart=charts.add
achart.SetSourceData Source:=srcsheet.Range("G2:H11")
....

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Missed that. Of course, the other solution is to use

ActiveSheet.ChartObjects.Add(L, T, W, H).Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("G2:H11")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Now you both lost me--how do I use your code if my recorded macro is full of references to the specific name of the renamed active worksheet:

' Keyboard Shortcut: Ctrl+a
'
Range("H2:H10").Select
Charts.Add
ActiveChart.ChartType = xlXYScatterLinesNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Data").Range("H2:H10"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Values = "=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Data!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=Data!R1C9"
ActiveChart.SeriesCollection(2).XValues = "=Data!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Values = "=Data!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).Name = "=Data!R1C10"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
With ActiveChart

z.entropic
 
I gave you a specific example of how to work around the hardcoded
names. Where are you lost?

Three additional points.

First, while the XL macro recorder sets the charttype first and the
sourcedata second, you need to reverse those steps in your macro.

Second, to get strings for the Values and XValues properties, use
something like

....Values= "='" & SrcSheet.name & "'!R2C8:R1000C8"

Third, to create a chart that refers to only those cells that contain
data, use something like:

with SrcSheet
....Values= "='" & .name & "'!" _
& .range(.cells(2,8),.cells(2,8).end(xldown))) _
.address(false, false, xlr1c1)

Alternatively, use named formulas. See the Dynamic Charts page of my
web site for more on this methodology.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
OK, let me explain mine and others' problem: your answer is it's like getting a book in a foreign language with one sentence translated... For you guys fluent in VB, mine and others questions may seem ridiculous and we may seem dumb, but since I see a macro code once in a quarter and don't know any of these German-looking VB statements with 5 terms joined together, I get stumped by incomplete answers. You yourself quickly added three additional points to your original reply...

I and, I trust, many other readers/posters do appreciate your time and effort in helping us guide through the VBA jungle, but many of your and others' off-side comments convince me it would be a waste of my time to try to learn VBA for the few things I need it. In the end, I have to rely on your and others' goodwill, time and effort which, hopefully, is a free ad for your business when more serious problems must be solved.

Finally, both Jon's and your proposed solutions to my original problem might seem trivial to you, pros, but after trying many versions of the modified macro, I still don't know how to write/create a simple chart-creation macro working in a worksheet with an arbitrary name. So, here is a simples chart-making macro--how should it be modified using your or Jon's ideas to make it independent of the "Sheet1" name?

Regards and thanks,

z.entropic
 
OK, let me explain mine and others' problem: your answer is it's like getting a book in a foreign language with one sentence translated... For you guys fluent in VB, mine and others questions may seem ridiculous and we may seem dumb, but since I see a macro code once in a quarter and don't know any of these German-looking VB statements with 5 terms joined together, I get stumped by incomplete answers. You yourself quickly added three additional points to your original reply...

I and, I trust, many other readers/posters do appreciate your time and effort in helping us guide through the VBA jungle, but many of your and others' off-side comments convince me it would be a waste of my time to try to learn VBA for the few things I need it. In the end, I have to rely on your and others' goodwill, time and effort which, hopefully, is a free ad for your business when more serious problems must be solved.

Finally, both Jon's and your proposed solutions to my original problem might seem trivial to you, pros, but after trying many versions of the modified macro, I still don't know how to write/create a simple chart-creation macro working in a worksheet with an arbitrary name. So, here is a simples chart-making macro--how should it be modified using your or Jon's ideas to make it independent of the "Sheet1" name?

Regards and thanks,

z.entropic
 
Sorry, Z, it wasn't so long ago I struggled with this stuff, I should
try to remember what it was like.

I'll start with the code you posted halfway up this thread. We'll define
variables for the sheet name and the range. The range you selected
before running the wizard in the macro recorder has no relation to the
ranges used later, so we can leave it out.

Your Y values for both series are in column H, and the X values are in
columns I and J. Series names are in row 1, data in rows 2 through 1000.

Sub MakeAChart()
'' declare variables for active sheet name and selected range
Dim sSheet As String
Dim rRange As Range
sSheet = ActiveSheet.Name

'' add chart and put it where you want it
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet

'' Add first series (sometimes charts.add does, sometimes not)
If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries
End If

'' Define the data and type
ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!R2C8:R1000C8"
ActiveChart.SeriesCollection(1).XValues = _
"=" & sSheet & "!R2C9:R1000C9"
ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

'' Add second series and define data
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Values = _
"=" & sSheet & "!R2C8:R1000C8"
ActiveChart.SeriesCollection(2).XValues = _
"=" & sSheet & "!R2C10:R1000C10"
ActiveChart.SeriesCollection(2).Name = "=" & sSheet & "!R1C10"
End Sub

This procedure was tested and works just fine. I might make additional
adjustments, but I follow the engineer's creed: If it works, you haven't
messed with it enough.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon,

I truly appreciate your reply to my cry of despair ;-).

I've tried your code and see a chart appear with a single bar at X=1 and Y=10, but subsequently the macro chokes on the 'Define the data block'

ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8"

with a run-time error message 1004
'Unable to set the Values property of the Series class'

What went wrong? I use Excel 2002 10.2614.2625 under Win2KPro with the latest updates.

z.entropic
 
Sorry for talking to myself, but my guess was wrong. Changing the range to filled cells only didn't fix the problem.

z.entropic
 
And you would only need one valid data cell in the range for it not to
give that particular error.

I ran the code myself to make sure the chart was created and populated
as expected. What's in your range?

One more point, I had selected a blank cell prior to running the macro.
What's in the active cell when you start it?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon, again, thanks for your time. I tried an empty and populated cell with the same result. I tried a range with two columns, including and not, the title. I tried three and more. All the same...

When I moved the ActiveChart.ChartType = xlXYScatterLinesNoMarkers line on top of the block, I obtained TWO time series lines, not an XY scatter chart, instead of the bar graph chart which I was getting before the move. The points plotted are those highlighted before the macro is run, not those given in the RC segment.

z.entropic
 
This line is giving the error:

ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!R2C8:R1000C8"

What's in H2:H1000?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
I wonder if this will work (I don't know why the other doesn't work) in
place of the line I cited below.

Dim sAddr As String
sAddr = ActiveSheet.Range(ActiveSheet.Range("H2"), _
ActiveSheet.Range("H2").End(xlDown)).Address _
(ReferenceStyle:=xlR1C1)
ActiveChart.SeriesCollection(1).Values = _
"=" & sSheet & "!" & sAddr

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Thanks for your effort, Jon. I tried your suggestion in the form:

'' declare variables for active sheet name and selected range
Dim sSheet As String
Dim rRange As Range
Dim sAddr As String
sSheet = ActiveSheet.Name

'' add chart and put it where you want it
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet

'' Add first series (sometimes charts.add does, sometimes not)
If ActiveChart.SeriesCollection.Count = 0 Then
ActiveChart.SeriesCollection.NewSeries
End If

'' Define the data and type
sAddr = ActiveSheet.Range(ActiveSheet.Range("H2"), _
ActiveSheet.Range("H2").End(xlDown)).Address _
(ReferenceStyle:=xlR1C1)"=" & sSheet & "!" & sAddr
ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R288C8"
ActiveChart.SeriesCollection(1).XValues = "=" & sSheet & "!R2C9:R288C9"
ActiveChart.SeriesCollection(1).Name = "=" & sSheet & "!R1C9"
ActiveChart.ChartType = xlXYScatterLinesNoMarkers

but the macro bombed out at the >> line with the error 1004 message again... I don't know what else I can ask for; perhaps that you e-mail your first working worksheet with the example given to (e-mail address removed) so I could try on my system? I don't want to exceed my welcome here, especially that I can't contribute much in terms of VBA expertise...

z.entropic
 
Just an idea. Before this line:

'' Add first series (sometimes charts.add does, sometimes not)

insert this:

'' Delete first series
Do While ActiveChart.SeriesCollection.Count > 0
ActiveChart.SeriesCollection(1).Delete
Loop

This will delete all series before you start adding new ones. There
might be something about the first series (if it's present when the
chart is created) that prevents VBA from accessing the series formula.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon, I found the source of my problem. It had nothing to do with your or Tushar's programing.

The spreadsheets in which I tried to use the macro are generated by an XLA program written by an outside firm. They chose the names of the worksheets in the form of 'Channel_I-013', and the 'minus' sign has been the source of all this trouble. Even though the worksheet name is not specifically used by the macro discussed here, somehow some piece of VBA code looks at it, 'thinks' it's a subtraction sign and screws everything up at
'' Define the data and type
ActiveChart.SeriesCollection(1).Values = "=" & sSheet & "!R2C8:R1000C8"
If I remove the minus sign or replace it with an underscore sign, everythng runs smoothly.

So, the lesson is, don't ever use hyphens in worksheet names even though the sign is not listed on the list of proscribed charatcerrs in worksheet names: \ / * ? [ ]! Maybe now somebody will suggest how to incorporate the worksheet name change into the macro to get rid of this offensive minus/hyphen sign! ;-).

Great thanks again for your untiring efforts to help me; this was quite an education!
z.entropic
==================================
 
Back
Top