Excel: Editing a Chart SERIES Formula with a MacroLast reviewed: September 2, 1997Article ID: Q67454 |
The information in this article applies to:
SUMMARYTo manipulate a Microsoft Excel chart's SERIES formula with a macro, the GET.FORMULA(reference) command can be used to return the entire SERIES formula as text. The resultant text string can then be manipulated with Excel's text functions. When the "reference" argument of the GET.FORMULA command is entered in the form "S#P#" (Series#Point#), the SERIES formula for the specified series and point number will be returned. For example, if a chart is created from the values in cells A1:A5 on a worksheet and information is added to cell A6, the following macro will update the existing chart to include the new information:
A1: AddRow A2: =GET.FORMULA("S1P1") A3: =LEFT(A2,SEARCH(":",A2)+1) A4: =SEARCH(":",A2)+2 A5: =SEARCH("C",A2,A4) A6: =A5-A4 A7: =MID(A2,A4,A6)+1 A8: =RIGHT(A2,LEN(A2)-(SEARCH(":",A2)+1+A6)) A9: =A3&A7&A8 A10: =SELECT("S1P1") A11: =FORMULA(A9) A12: =RETURN() MORE INFORMATION
A1 = Macro name. A2 = Returns the SERIES formula as text from series 1, point 1 on the active chart. A3 = Returns the left side of the formula, up to the row number to be incremented. A4 = Returns the position of the row number to incremented. If the row number has more than one digit, returns the first digit. A5 = Returns the position of the character "C", which will follow the row number to be incremented. A6 = Returns the number of digits in the row number. A7 = Identifies the entire row number and adds one to it. A8 = Returns the right side of the formula, beginning at the first non-digit after the row number. A9 = Concatenates the left side of the formula, new row number, and right side of the formula.A10 = Selects series 1, point 1 on the active chart. A11 = Enters the new formula into the formula bar, replacing the old formula.A12 = Ends the macro.
REFERENCESFor more information on the GET.FORMULA command, see pages 199-200 of the "Microsoft Excel Function Reference," version 4.0. If you are using version 3.0, see page 110 of the "Microsoft Excel Function Reference," version 3.0 manual. If you are using Excel 2.20, see page 238 of the "Microsoft Excel Functions and Macros" version 2.2 manual.
|
Additional reference words: noupd
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |