Remember Me?

 John Michl Posts: n/a Filling Source Data Array with Decimal Values

I'm trying to write a macro that will fill a data series based on a
single value in a spreadsheet. In this example, Series 1 is the actual
observation data. Series 2 would be the average for all points in
Series 1. My code works fine as long as the series average is rounded
to zero decimal places. If not, I receive the error "Subscript out
of Range". I've tried declaring "i" as various data types to no avail.
Help would be appreciated.

'Populate GrandMean values
Dim ar As Variant
ReDim ar(1 To p)

p = ActiveChart.SeriesCollection(1).Points.Count
i = Round(Range("GrandMean"), 0) 'WANT THIS TO 2 DECIMALS NOTE 0

For x = 1 To UBound(ar)
ar(x) = i
Next x

ActiveChart.SeriesCollection(2).Values = ar

End Sub

- John

 Kelly O'Day Posts: n/a Filling Source Data Array with Decimal Values

John -

I slightly modified your code and got it to work with a simple data set in
A1:B22.

For the horizontal line I took advantage of the fact that you can define a
straight line by two points, begin and end. Since you need X and Y for each
point, a straingt line can be defined by 2 X's and 2 Ys. I wrote these
values to a hor line data table in range F2:G3. I then added the avg line
series with the data in F2:G3.

I used min and max of A column data to set hor line X values.

I used the B column avg to set the Y value for the hor line.

You should be able to edit this to meet your needs.

...Kelly

'Populate GrandMean values
Dim ar() As Variant
Dim avg As Double
p = ActiveChart.SeriesCollection(1).Points.Count
ReDim ar(1 To p)
' Calc avg value
avg = Application.Average(Range("b1:b22"))
avg = Application.Round(avg, 2) 'WANT THIS TO 2 DECIMALS NOTE 0

' Create Avg Line Data Table - Need Start & End X and Y values
Range("f2") = Application.WorksheetFunction.Min(Range("a1:a22"))
Range("f3") = Application.WorksheetFunction.Max(Range("a1:a22"))
Range("G2") = avg
Range("G3") = avg

' Add Avg Line to Chart
Range("F2:G3").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
Application.CutCopyMode = False

End Sub
"John Michl" wrote in message
oups.com...
I'm trying to write a macro that will fill a data series based on a
single value in a spreadsheet. In this example, Series 1 is the actual
observation data. Series 2 would be the average for all points in
Series 1. My code works fine as long as the series average is rounded
to zero decimal places. If not, I receive the error "Subscript out
of Range". I've tried declaring "i" as various data types to no avail.
Help would be appreciated.

'Populate GrandMean values
Dim ar As Variant
ReDim ar(1 To p)

p = ActiveChart.SeriesCollection(1).Points.Count
i = Round(Range("GrandMean"), 0) 'WANT THIS TO 2 DECIMALS NOTE 0

For x = 1 To UBound(ar)
ar(x) = i
Next x

ActiveChart.SeriesCollection(2).Values = ar

End Sub

- John

 Kelly O'Day Posts: n/a Filling Source Data Array with Decimal Values

John:

horizontal line.

I computed the avg, and then developed a 4 cell horizontal line data table
(F2:G3) to store the start and End X and Y's for horizontal line. You only
need 2 points to establish a straight line, so why add avg to every row of
data?

Here's my code. Let me know if it solves your problem.

'Populate GrandMean values
Dim ar() As Variant
Dim avg As Double
p = ActiveChart.SeriesCollection(1).Points.Count
ReDim ar(1 To p)
' Calc avg value
avg = Application.Average(Range("b1:b22"))
avg = Application.Round(avg, 2) 'WANT THIS TO 2 DECIMALS NOTE 0

' Create Avg Line Data Table - Need Start & End X and Y values
Range("f2") = Application.WorksheetFunction.Min(Range("a1:a22"))
Range("f3") = Application.WorksheetFunction.Max(Range("a1:a22"))
Range("G2") = avg
Range("G3") = avg

' Add Avg Line to Chart
Range("F2:G3").Select
Selection.Copy
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.Paste Rowcol:=xlColumns,
SeriesLabels:=False, _
CategoryLabels:=True, Replace:=False, NewSeries:=True
Application.CutCopyMode = False
End Sub
************************************************** **************************************************

"John Michl" wrote in message
oups.com...
I'm trying to write a macro that will fill a data series based on a
single value in a spreadsheet. In this example, Series 1 is the actual
observation data. Series 2 would be the average for all points in
Series 1. My code works fine as long as the series average is rounded
to zero decimal places. If not, I receive the error "Subscript out
of Range". I've tried declaring "i" as various data types to no avail.
Help would be appreciated.

'Populate GrandMean values
Dim ar As Variant
ReDim ar(1 To p)

p = ActiveChart.SeriesCollection(1).Points.Count
i = Round(Range("GrandMean"), 0) 'WANT THIS TO 2 DECIMALS NOTE 0

For x = 1 To UBound(ar)
ar(x) = i
Next x

ActiveChart.SeriesCollection(2).Values = ar

End Sub

- John

 John Michl Posts: n/a Filling Source Data Array with Decimal Values

Thanks, Kelly. This will be helpful.

In my case, I already had the chart set up but just needed to change
some of the values. I did not want to have extra data ranges in the
worksheet to create the horizontal lines. After pulling my hair out, I
decided to write a little code that would create string which would
look like an array that could be put into the X Values area of the
chart. I attached this code to the Chart Activate event so that every
time I click on the chart, the lines will redraw based on current
information.

Private Sub Chart_Activate()
Dim strValues 'string that represents array of values in format "={x,
x, x, x}
p = ActiveChart.SeriesCollection(1).Points.Count

'Populate GrandMean values
strValues = "={" & Round(Range("GrandMean"), 2)
For x = 1 To p - 1
strValues = strValues & ", " & Round(Range("GrandMean"), 2)
Next x
strValues = strValues & "}" 'add closing }

ActiveChart.SeriesCollection(2).Values = strValues

End Sub

- John

 John Michl Posts: n/a Filling Source Data Array with Decimal Values

Turns out the problems I was having had nothing to do with decimals but
rather the size of the array I was creating. It appears that I can't
enter a string into the ActiveChart.SeriesCollection(2).Values when the
length of that string is greater than 255 characters. I had no problem
when I had a dozen or so data points but when I had 52 (one for each
week in a year) I started to bump into problems. Through trial and
error I determined it was the length of the string being created in my
code that was causing the problem.

I really wanted to avoid using a dummy column of data for this but it
looks like that's what I'll need to do.

- John

 Kelly O'Day Posts: n/a Filling Source Data Array with Decimal Values

John:

a problem because you were adding values for every point in original data
series.

To plot average line, we only need 2 points (begin and end).

I modified your code to add an average horizontal line without any new data
added to sheets. Basically, it just adds the X & Y min and X and Y max
values, not all the points along original data series.

The code includes a data label for avg line for editing purposes.

Public Sub Plot_avg()
' Procedure to plot avg line in activechart
Dim x_1 As Double ' Min x value
Dim x_2 As Double ' Max x value
' Remove previous Series(2) avg line
On Error Resume Next ' needed in
case no previous series(2) Avg Line
ActiveChart.SeriesCollection(2).Select
Selection.Delete
' Determine num data points
p = ActiveChart.SeriesCollection(1).Points.Count
' Calc Averge
calc_mean = Application.Average(Range("B:B"))
' Determine start and end X values
x_1 = Application.WorksheetFunction.Min(Range("A2:A1000" ))
x_2 = Application.WorksheetFunction.Max(Range("A2:A1000" ))
' SetSeriescollection Data array {X_1,X_2} & {calc_avg,calc_avg} - Notice
{}'s for array
x_values = "{" & x_1 & "," & x_2 & "}"
y_values = "{" & calc_mean & "," & calc_mean & "}"
With ActiveChart.SeriesCollection.NewSeries
.XValues = x_values
.Values = y_values
' Add data label to last pt on avg line
.Points(2).ApplyDataLabels
End With
End Sub

...Kelly

"John Michl" wrote in message
oups.com...
Turns out the problems I was having had nothing to do with decimals but
rather the size of the array I was creating. It appears that I can't
enter a string into the ActiveChart.SeriesCollection(2).Values when the
length of that string is greater than 255 characters. I had no problem
when I had a dozen or so data points but when I had 52 (one for each
week in a year) I started to bump into problems. Through trial and
error I determined it was the length of the string being created in my
code that was causing the problem.

I really wanted to avoid using a dummy column of data for this but it
looks like that's what I'll need to do.

- John

 John Michl Posts: n/a Filling Source Data Array with Decimal Values

Thanks, Kelly.

series to the chart, formatted it as an XY chart on a secondary axis.
This series only had one Y value (the mean) and one X value (1). I set
the secondary Y to the same min and max as the primary secondary axis
and the min and max of the secondary X to 0 and 1. Then I added an X
Error Bar to the new data point set to a Minus Error with a fixed value
of 1. This drew the line across the chart. Now that it is set up, it
works pretty slick since it requires no VBA except to keep the Y axis
scales in synch and add the data label to the last point. Thanks for
your help. I tries several different paths to the final destination,
and as always, learned a great deal in the journey.

Here's the code I used for the scales.

Sub SetScales

' Set the min and max ranges of the Secondary Y axis to equal the X
axis

iMin = ActiveChart.Axes(xlValue, xlPrimary).MinimumScale
iMax = ActiveChart.Axes(xlValue, xlPrimary).MaximumScale

With ActiveChart.Axes(xlValue, xlSecondary)
.MinimumScale = iMin
.MaximumScale = iMax
End With

With ActiveChart.Axes(xlCategory, xlSecondary)
.MinimumScale = 0
.MaximumScale = 1
End With

End Sub

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post John Michl Charts and Charting in Excel 1 March 17th 06 07:31 PM Sarah Charts and Charting in Excel 1 February 19th 06 11:46 AM JaimeTimbrell Excel Discussion (Misc queries) 2 February 16th 06 08:09 AM JLC Charts and Charting in Excel 3 October 14th 05 01:29 AM Jon Peltier Charts and Charting in Excel 4 November 30th 04 03:30 AM

All times are GMT +1. The time now is 08:46 AM. Copyright ©2004-2022 ExcelBanter.