If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Unable to set the Formula property of the Series class" with a tw
I have built large workbook of software test metrics for dozen of products
(by rows) with results organized by week over the columns. I have created dynamic ranges that select a moving window of dates (for the x-axis) and a corresponding moving window of metrics for each metric and product. I have created a row of reference charts for the first product. My VBA code copies the reference charts for each prduct under test and pastes them in a grid, then rewrites the series formulas of the charts to reference the correct products. The formula re-writing code is failing with the dreaded "Unable to set the Formula property of the Series class" error. The original formula and the replacement formula are each 144 characters in length, though several of the terms in the formula are dynamic ranges. I haven't seen a definitive statement on whether dynamic ranges are expanded before the 255 char VBA formula limit is applied. Here's the original formula for one of the series in a collection of 3 for one chart: =SERIES(Weekly_Release_Data!$E$27,'Sample-Defects-Test-Data.xls'!DateTextSpan,Weekly_Release_Data!'RelW00 1_Total_Test_Pass_Rate_Span',1) (The quotes around "RelW002_Total_Test_Pass_Rate_Span" were returned by the ..formula property - they do not show in the formula bar.) I want to change the "001" to another value, and have used various approaches, each of which results in the error. RelW001_Total_Test_Pass_Rate_Span is defined as: =OFFSET(INDEX(RelW001_Total_Test_Exposure_Rate,1,C urCol-3),0,-MIN(PeriodsPast,CurCol-5)-1,1,MIN(PeriodsPast,CurCol-5)+PeriodsForward) RelW001_Total_Test_Exposure_Rate is defined as: =Weekly_Release_Data!$F$25:$BK$25 PeriodsPast and PeriodsForward are defined as small constants like 26 and 13. CurCol is defined as Control!$C$3. All the named ranges are generated by VBA code from row titles, so it is possible to shorten them, but I would need a lookup table to convert from the "plain english" titles to condensed names if that is required to get under some limit. Thanks in advance.. -- Peter |
#2
|
|||
|
|||
"Unable to set the Formula property of the Series class" with a tw
Many hours later I have found a solution to this problem. Excel Developers,
please note the following: 1. If you record a macro of the steps involved in editing a SERIES formula in the formula bar, the macro recorder generates code which accesses the ..formula property of the SeriesCollection. This is wrong. If you originally assigned a cell to the series name, the .formula property will return an absolute reference for the first argument of the SERIES formula. You must use the .formulaR1C1 property to retrieve the formula. 2. The macro recorder is incorrectly placing single quotes around a range name used for the Y values. You must remove the quotes from the formula before assigning it to the .formula property. (Note that I did not assign it to the .formulaR1C1 property, but it does work.) My new code looks like this: For i = 1 To cht.SeriesCollection.Count formula = ActiveChart.SeriesCollection(i).FormulaR1C1 Debug.Print "Original formula" Debug.Print formula formula2 = WorksheetFunction.Substitute(formula, "RelW001", "RelW" & Format(chartproduct, "000")) ' The formula returned by Excel has a bug in it - there are extra single quotes around the range formula2 = WorksheetFunction.Substitute(formula2, "!'", "!") formula2 = WorksheetFunction.Substitute(formula2, "',", ",") Debug.Print "New formula" Debug.Print formula2 ActiveChart.SeriesCollection(i).formula = formula2 Next i "PeterQ" wrote: I have built large workbook of software test metrics for dozen of products (by rows) with results organized by week over the columns. I have created dynamic ranges that select a moving window of dates (for the x-axis) and a corresponding moving window of metrics for each metric and product. I have created a row of reference charts for the first product. My VBA code copies the reference charts for each prduct under test and pastes them in a grid, then rewrites the series formulas of the charts to reference the correct products. The formula re-writing code is failing with the dreaded "Unable to set the Formula property of the Series class" error. The original formula and the replacement formula are each 144 characters in length, though several of the terms in the formula are dynamic ranges. I haven't seen a definitive statement on whether dynamic ranges are expanded before the 255 char VBA formula limit is applied. Here's the original formula for one of the series in a collection of 3 for one chart: =SERIES(Weekly_Release_Data!$E$27,'Sample-Defects-Test-Data.xls'!DateTextSpan,Weekly_Release_Data!'RelW00 1_Total_Test_Pass_Rate_Span',1) (The quotes around "RelW002_Total_Test_Pass_Rate_Span" were returned by the .formula property - they do not show in the formula bar.) I want to change the "001" to another value, and have used various approaches, each of which results in the error. RelW001_Total_Test_Pass_Rate_Span is defined as: =OFFSET(INDEX(RelW001_Total_Test_Exposure_Rate,1,C urCol-3),0,-MIN(PeriodsPast,CurCol-5)-1,1,MIN(PeriodsPast,CurCol-5)+PeriodsForward) RelW001_Total_Test_Exposure_Rate is defined as: =Weekly_Release_Data!$F$25:$BK$25 PeriodsPast and PeriodsForward are defined as small constants like 26 and 13. CurCol is defined as Control!$C$3. All the named ranges are generated by VBA code from row titles, so it is possible to shorten them, but I would need a lookup table to convert from the "plain english" titles to condensed names if that is required to get under some limit. Thanks in advance.. -- Peter |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Dynamic Range with unused formula messing up x axis on dynamic graph | [email protected] | Charts and Charting | 2 | February 2nd, 2006 08:02 PM |
Chart -- Source Data... -- Series dialog window | Sarah Jane | Charts and Charting | 2 | January 24th, 2006 10:27 AM |
Series Formula Help | Josh O. | Worksheet Functions | 1 | January 5th, 2006 07:06 PM |
Maintain fixed section of formula in series | T Wesley | Worksheet Functions | 2 | June 21st, 2004 06:21 PM |
Convert Chart Series Formula to Array Via VBA? | Kevin G | Charts and Charting | 1 | May 5th, 2004 04:52 AM |