Excel: Editing a Chart SERIES Formula with a Macro

Last reviewed: September 2, 1997
Article ID: Q67454
The information in this article applies to:
  • Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0

SUMMARY

To 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.

REFERENCES

For 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
Keywords : kbcode kbmacro kbprg


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.