macro to create charts in worksheets with arbitrary names

  • Thread starter Thread starter z.entropic
  • Start date Start date
It's always something stupid, isn't it. But there's a fix for this.
Whenever a sheet name has a special character (i.e., one that can cause
trouble), you can surround the sheet name with single quotes:

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

See if this helps. In fact, it never hurts to use the single quotes,
because Excel ignores them if they are not needed.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Ummm...if you go back to my June 10 post, you'll find that the proposed
code includes single quotes around the worksheet name. As you noted in
a subsequent post, the tips might have been too cryptic to be useful at
that point. Now, given the (painful? <g>) experience of the past week,
it might be worth your while to revisit those earlier posts ;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Yes!

z.entropic

Jon Peltier said:
It's always something stupid, isn't it. But there's a fix for this.
Whenever a sheet name has a special character (i.e., one that can cause
trouble), you can surround the sheet name with single quotes:

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

See if this helps. In fact, it never hurts to use the single quotes,
because Excel ignores them if they are not needed.

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

z.entropic said:
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 ==================================
Click to expand...
Click to expand...
 
I cannot deny that you were absolutely right on three counts: the quotes (whose import I completely missed at the time), my inability then to grasp your solution, and the need to re-read the thread knowing what I know now. Actually, this is how I found the hyphen-minus problem.

Here's a bonus question ;-): by default, the created chart is small. I tried to incorporate a resizing statement by recording a little macro separately and incorporating it into the main one, but my trick didn't work because of, again, the specific and variable chart naming problem--"Chart 3" in

Sub ResizeChart()
ActiveSheet.ChartObjects("Chart 3").Activate
ActiveChart.ChartArea.Select
ActiveSheet.Shapes("Chart 3").IncrementLeft -169.5
ActiveSheet.Shapes("Chart 3").IncrementTop -108.75
ActiveSheet.Shapes("Chart 3").ScaleWidth 1.92, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 3").ScaleHeight 1.91, msoFalse, msoScaleFromTopLeft
End Sub

I've tried all kinds of substitutions using sSheet for "Chart 3", but no cigar... the entire macro is listed below.

Thanks for your suggestions and help.

z.entropic

Sub VQChart()
'' 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

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

'' 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"

ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "capacity, Ah"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "voltage, V"
End With
ActiveChart.Legend.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
Selection.Width = 135
Selection.Height = 36
Selection.Left = 186
Selection.Top = 128
ActiveChart.SeriesCollection(1).Select
With Selection.Border
.ColorIndex = 1
.Weight = xlHairline
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = 1
.MarkerForegroundColorIndex = 1
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 5
.Shadow = False
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Border
.ColorIndex = 3
.Weight = xlThin
.LineStyle = xlContinuous
End With
With Selection
.MarkerBackgroundColorIndex = xlNone
.MarkerForegroundColorIndex = xlNone
.MarkerStyle = xlNone
.Smooth = False
.MarkerSize = 3
.Shadow = False
End With
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = 2
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
End Sub
 
At some point it's your active chart, so just use ActiveChart. Actually
Activehart.Parent to adjust the ChartObject. I hate using the Shape
object when editing a chart, so I use the Chart Object.

With ActiveChart.Parent
.Left = <left coordinate in points>
.Top = <top coordinate in points>
.Width = <width in points>
.Height = <height in points>
End With

If you want to line the chart up with a range of cells, that's easy too:

Set rngChart = ActiveSheet.Range("D4:K20")
With ActiveChart.Parent
.Left = rngChart.Left
.Top = rngChart.Top
.Width = rngChart.Width
.Height = rngChart.Height
End With

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