dynamic chart range with VBA

[Jacob]

I have been spending a lot of time trying to figure out dynamic chart
ranges and just can't figure it out. I have a chart (scatter plot)
that needs to have the X and Y values of a series linked to the number
of values present, which will be constantly changing. It would also be
nice to have the maximum/minimum scale adjustable as well, for both
axes. the code I am using (below) is causing errors.

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

'ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & 2
+_
(span / stepsize) & ""
'ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & 2 +
_
(span / stepsize) & ""

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

 

[semiopen]

I have been spending a lot of time trying to figure out dynamic chart
ranges and just can't figure it out. I have a chart (scatter plot)
that needs to have the X and Y values of a series linked to the number
of values present, which will be constantly changing. It would also be
nice to have the maximum/minimum scale adjustable as well, for both
axes. the code I am using (below) is causing errors.

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

'ActiveChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & 2
+_
(span / stepsize) & ""
'ActiveChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & 2 +
_
(span / stepsize) & ""

ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

1) Why did you set CurrentChart then switch to ActiveChart? Using
CurrentChart would be more consistent.

2) Replace "=Sheet2!R2C2:R2C" & 2 + (span / stepsize) & ""
by "=Sheet2!R2C2:R2C" & (2 + (span / stepsize)) & """

Note the extra double quote - "" is the empty string

3) A more radical suggestion: when you create the chart, let the X and
Y values be *names*. For example, I have a chart (involving dice
probabilities) where I created a chart and typed this in the source
data under series/X Values:

='Dice Sums.xls'!xvals

And the same thing for y (but with "y" of course). "xvals" is the name
of the range which is to contain the xvals.
Then - in the code I create a Range variable xRange and, after loading
it with what I want by a combination of the Range function and Offset
something like:

Set xRange = Range(Range("A1"),Range("A1").Offset(k))

I simply use the statement

xRange.Name = "xvals"

and now the Chart will have the right x-vals

Hope that helps

-semiopen

 

[Jacob]

Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"

CurrentChart.Axes(xlCategory).Selec
With ActiveChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span / stepsize + 1
.MinorUnit = 12
.MajorUnit = 24
.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub


I get the following error:

Unable to set XValues property of the series class

 

[semiopen]

Thanks for the suggestions. I am better off avoiding the radical
solution for now. plus I want to learn more about manipulating charts
from VBA.

I am not sure what I doing. that's why I switched from Current to
Active. I changed that and tried to add the quote, but it would
automatically add one on top of that. so I get this:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value * 12
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

CurrentChart.SeriesCollection(1).XValues = "=Sheet2!R2C2:R2C" & (2
+ (span /_ stepsize)) & """"
CurrentChart.SeriesCollection(1).Values = "=Sheet2!R1C2:R1C" & (2 +
(span /_ stepsize)) & """"

Try CurrentChart.SeriesCollection(1).XValues = _
Sheets("sheet2").Range(Range(R2C2),Range(R2C2).Offset(0,span/stepsize))

XValues is expecting a range - not a string naming a range. Also - be
careful with stray line continuation characters that might arise in
editing.

You are right about needing an extra quote - I'd forgotten how to
escape a quote mark and wrote a simple test routine before I posted,
but didn't notice that the VBE had supplied the missing "

-semiopen

 

[semiopen]

Try CurrentChart.SeriesCollection(1).XValues = _
Sheets("sheet2").Range(Range(R2C2),Range(R2C2).Offset(0,span/stepsize))

XValues is expecting a range - not a string naming a range. Also - be
careful with stray line continuation characters that might arise in
editing.

You are right about needing an extra quote - I'd forgotten how to
escape a quote mark and wrote a simple test routine before I posted,
but didn't notice that the VBE had supplied the missing "

I realized that my last code snippet won't work. The Range function is
expecting strings naming cells in the A1 style notation (which is the
only style I use). So - in the above code just replace each R2C2 by
"B2" (with the quote marks)

Sorry for any confusion

-semiopen

 

[Jacob]

I still can't get it to work. I was able to set the maxvalue and
minvalue and that might suffice for now. here is my code:

Private Sub UpdateChart()
span = Sheets("sheet1").Range("C4").Value * 12
stepsize = Sheets("sheet1").Range("C6").Value
Set CurrentChart = Sheets("sheet2").ChartObjects(1).Chart
CurrentChart.Parent.Width = 450
CurrentChart.Parent.Height = 150

With CurrentChart.Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = span
.MinorUnit = 12
.MajorUnit = 24
'.Crosses = 0
End With

' Save chart as GIF
Fname = ThisWorkbook.Path & Application.PathSeparator & "temp.gif"
CurrentChart.Export Filename:=Fname, FilterName:="GIF"

' Show the chart
Image3.Picture = LoadPicture(Fname)
End Sub

 

[semiopen]

Persevere - you should be able to get the range to update. The chart
object is notoriously tricky to get right.

Just to make sure I was getting the objects correct, I created a chart
on sheet1 with xvalues A1:A3 and y values B1:B3. I then ran the
following code:

Sub test()
Dim cht As Chart
Dim sh As Worksheet
Dim xRange As Range
Dim yRange As Range
Set sh = Sheets(1)
Set cht = sh.ChartObjects(1).Chart
Set xRange = Range("A1", "A5")
Set yRange = Range("B1", "B5")
cht.SeriesCollection(1).XValues = xRange
cht.SeriesCollection(1).Values = yRange
End Sub

After which the data series of the chart were successfully extended
down to the 5th row

Maybe you could declare range variables like I did - set them then test
them by inserting a

msgbox xRange.Address
msgbox yRange.Address

to see if they are including the cells that you want them to. Then
running

CurrentChart.SeriesCollection(1).XValues = xRange
CurrentChart.SeriesCollection(1).Values = yRange

really should work.

The way you get the range is using Range with an Offset (there might be
other ways - but this always works for me)

Set xRange = Range(Cell1,Cell2) where Cell1 is the first cell in the
range and Cell2 is the last cell in the range should work. Cell1 in
your code would probably be a base cell - the first cell in the range
and cell2 can be caclulated as an offset from the base. Say you want a
column range with n cells (where n in your case is a function of span
and step size - whatever those are) then cell2 = cell1.Offset(n-1,0)
works and if you want a row range (which I thought you wanted) then
cell2 = cell1.Offset(0,n-1) works. This is what the expression
Sheets("sheet2").Range(Range("B2"),Range("B2").Offset(0,span/stepsize))
was trying to do. What *might* have happened was that you were running
the code with sheet 1 as the active sheet - in which case the "B2"
would refer to "B2" on sheet 1. so you could fully qualify the Ranges:

Sheets("sheet2").Range(Sheets("sheet2").Range("B2"),Sheets("sheet2").Range("B2").Offset(0,span/stepsize))


with maybe a worksheet variable and/or a range variable previously set
to make it more readable. (eg. Set BaseCell =
Sheets("sheet2").Range("B2")

By the way - was there any reason to use Sheets("sheet2") rather than
Sheets(2)? I just followed your code there thinking that there may
have been a history of sheet renaming in your workbook.

Hope this finally helps

-semiopen