Filling Source Data Array with Decimal Values

[John Michl]

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.


Sub AddAverageLine()

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

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

(e-mail address removed)


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

 

[Kelly O'Day]

John:

I made up a smalls data set of A1:B22 and modified your code to add a
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.


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

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]

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]

John:

I like your idea about adding data array for the average line. You ran into
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 & "}"
' Add new series
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

(e-mail address removed)

 

[John Michl]

Thanks, Kelly.

Before I saw your reply, I took a different approach. I added a new
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