Excel: Editing a Chart SERIES Formula with a Macro

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

      formula.

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.