A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

macro to create charts in worksheets with arbitrary names



 
 
Thread Tools Display Modes
  #21  
Old June 17th, 2004, 10:43 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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
==================================

  #22  
Old June 18th, 2004, 12:19 AM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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 wrote:

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 ==================================


  #23  
Old June 18th, 2004, 12:09 PM
Tushar Mehta
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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

In article ,
says...
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
==================================


  #24  
Old June 18th, 2004, 02:56 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

Yes!

z.entropic

"Jon Peltier" wrote:

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 wrote:

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 ==================================



  #25  
Old June 18th, 2004, 03:21 PM
z.entropic
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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 Whe=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 Whe=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



"Tushar Mehta" wrote:

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


  #26  
Old June 19th, 2004, 02:48 PM
Jon Peltier
external usenet poster
 
Posts: n/a
Default macro to create charts in worksheets with arbitrary names

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/
_______


z.entropic wrote:

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 Whe=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 Whe=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



"Tushar Mehta" wrote:


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




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.