Drawing a Horizontal Line on a Chart 2

[Stretchwickster]

I need to draw a horizontal line on a chart to show an upper limit. I know how to add a new series but is there a way to set the all the values in the series to one value with just one command rather than using a loop. Clive

 

[RobBroekhuis]

What type of chart do you have? How many data points in a series? You can assign an array of values to a series, rather than a range. The array is easy to construct programmatically.
Rob

 

[Stretchwickster]

I have an xlColumnClustered chart with a constant variable representing the number of data points in the series. Clive

 

[Stretchwickster]

Ok, I've created and populated (using a for loop to set each value to 1.1) an array of the right number of variables. The problem I get is assigning the array to the Values property. I get an error message saying "Unable to set the Values property of the Series class" referring to the line below:

ActiveChart.SeriesCollection(3).Values = vaUpperLimitDataArray

Clive

 

[RobBroekhuis]

I don't think it works with standard VBA arrays (sorry if I confused you). What DOES work is assigning a constant string to the series.formula property, e.g.

activechart.SeriesCollection(3).formula="=series(,,{14,14,14,14,14,14},3)"

to get a series of all 14's. If you know how many points are in the series, it's easy to generate the string value programmatically.
I hope that helps.
Rob

 

[Stretchwickster]

Sorry Rob - I'm getting a bit confused and I don't really understand what your last post was getting at! Can you give me an example of how to add 20 data items with a value of 1 to a series. At the moment I'm doing it like this and it works fine but ideally I would like to set a certain number of values (this certain number will be based on a constant variable called INT_NUMBER_OF_ITEMS) equal to 1:

.SeriesCollection(3).Values = "={1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}"

Clive

 

[RobBroekhuis]

In that case, you'd do

s="={1"
for i=1 to int_number_of_items
s=s+",1"
next i
.seriescollection(3).values=s + "}"

or am I thinking too simple?
Rob

 

[Stretchwickster]

Perfect...thanks Rob...much appreciated...that's exactly what I needed...have a good weekend...I need sleep....aaaaggghhh!!!! ;-) Clive

 

[RobBroekhuis]

A star even though I forgot to add a "-1" to the for loop count. You're too generous
Rob

 

[Stretchwickster]

It worked fine...so I didn't notice until you told me - I'll have to take back my star - hehehe!!! Clive

 

[lazycreeks]

Don't know if this code-free method will skin the cat even easier, but I have one highly automated Excel SS with 3 charts whose plot values are + & - values (X axis crosses at 0) When the current plot values on the chart are all negative, or all positive, the zero line is off the chart. To quickly understand about how far away from zero the plots are ranging, I stuck in an additional series w/the legend title "Reference Line". In a free col., let's say Col. Z, I entered the value 0. In cells Z2:Z21, enter $Z$1. The 1st time the chart plots, right click anywhere on the plotted 0 line, Format Data Series, Weight, then choose a thicker line and/or color to make it visibly stand out. This forces the chart to display the zero line, but it could be any one, or more, visible lines. To visually define any limits, the line is easily moved by changing the value in Z1. Then with Z1 value as a starting point, you could define any norm, say a sine wave, in cells Z2:Z21 referencing each successive point back to Z1 e.g. Z2 might be $Z$1+1, Z3... $Z$1+2, etc. Then the plotted pattern is easily moved into your data range by changing the value in Z1 manually or in your code.
Lazycreeks