Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. 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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Filling Source Data Array with Decimal Values
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" 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. 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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Filling Source Data Array with Decimal Values
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 "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 |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Filling Source Data Array with Decimal Values
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Array for Data Source | Charts and Charting in Excel | |||
Updating charts when data source is in a different file | Charts and Charting in Excel | |||
ranking query | Excel Discussion (Misc queries) | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel |