Microsoft KB Archive/118485

XL: Macro to Add Labels to Points in an XY (Scatter) Chart Article ID: Q118485 Revision Date: 13-SEP-1996

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SUMMARY In Microsoft Excel, although there is no built-in way to automatically attach text labels to data points in an xy (scatter) chart, you can create a macro to do this. This article contains both a Microsoft Excel 4.0 macro and a Visual Basic macro that you can use to perform this task.

MORE INFORMATION Both of the code examples below assume that your data and associated labels are arranged on your worksheet in the following format:


 * The first column contains the data labels
 * The second column contains the X values for the X-Y scatter chart
 * The third column contains the Y values for the X-Y scatter chart

Example

A1: Labels B1: X Values C1: Y Values A2: DataPoint1 B2: 2 C2: 5 A3: DataPoint2 B3: 9 C3: 7 A4: DataPoint3 B4: 5 C4: 3 A5: DataPoint4 B5: 4 C5: 8 A6: DataPoint5 B6: 1 C6: 4

Your table should not contain empty columns, and the column containing the data labels should not be separated from the column containing the x values. The labels and values must be laid out EXACTLY in the format shown above (although the upper-left cell does not necessarily need to be cell A1). To test these macros, create a chart using the above values and use the sample macros below.

Microsoft Excel 5.0

  On the worksheet, select the range B1:C6, and press F11.   In the ChartWizard - Step 1 of 5 dialog box, click the Next button.   In the ChartWizard - Step 2 of 5 dialog box, click XY (Scatter) and then click the Next button.   In the ChartWizard - Step 3 of 5 dialog box, click the Next button.   In the ChartWizard - Step 4 of 5 dialog box, make sure the following settings are selected, and then click Finish: Data Series in: Columns Use First 1 Column(s) for X Data Use First 1 Row(s) for Legend Text 

Microsoft Excel Version 4.0


 * 1) On the worksheet, select the range B1:C6, and press the F11 key.
 * 2) In the New Chart dialog box, click X-Values for XY-Chart, and then click OK.

NOTES:

The following macros will not work when the active chart is currently embedded in a worksheet; the chart must be in its own window, or on its own sheet tab (version 5.0 only) when you run the macro. Also, the following macros will not work if the chart is using “assumed” x values. To tell if an xy (scatter) chart is using “assumed” x values, do the following:

  Select one of the series in the xy (scatter) chart.   Look at the =SERIES formula in the formula bar. The second item (the item following the first comma) inside the parentheses refers to the range of x values on the worksheet. If this item is blank, then the chart is using “assumed” x values, and the macro will not function properly. </li></ol>

Microsoft Excel 4.0 Macro Example Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided as is and Microsoft in no way guaranties that the following code can be used in all situations and will not support modifications of the code to suit specific customer requirements. This macro will run in both Microsoft Excel version 4.0 and Microsoft Excel version 5.0.

  On a new Microsoft Excel 4.0 macro sheet, enter the following code: A1: XLMAttachLabelsToPoints A2: =ECHO(FALSE) A3: ChartName=GET.DOCUMENT(1) A4: xVals=GET.FORMULA(“S1”) A5: SourceWorksheet=LEFT(xVals,SEARCH(“!”,xVals,1)-1) A6: SourceWorksheet=RIGHT(SourceWorksheet,LEN(SourceWorksheet)- SEARCH(“(”,SourceWorksheet,1)) A7: =IF(LEFT(SourceWorksheet,1)=“,”) A8: SourceWorksheet=RIGHT(SourceWorksheet,LEN(SourceWorksheet)-1) A9: =END.IF A10: xVals=SUBSTITUTE(xVals,SourceWorksheet,“xlSheet”) A11: xVals=RIGHT(xVals,LEN(xVals)-SEARCH(“,”,xVals,1)) A12: =IF(LEFT(xVals,1)=“,”) A13: =ALERT(“This X-Y scatter chart is using assumed X values. The macro cannot continue.”) A14: =HALT A15: =END.IF A16: xVals=LEFT(xVals,SEARCH(“,”,xVals,1)-1) A17: xVals=SUBSTITUTE(xVals,“xlSheet”,SourceWorksheet) A18: xVals=TEXTREF(xVals) A19: Counter=1 A20: =FOR.CELL(“CurrentCell”,xVals) A21: xLabel=OFFSET(CurrentCell,0,-1,1,1) A22: =ATTACH.TEXT(4,1,Counter) A23: =SELECT(“Text S1P”&Counter) A24: =FORMULA(xLabel) A25: Counter=Counter+1 A26: =NEXT A27: =SELECT(&quot;&quot;) A28: =RETURN

Explanation of Macro Code
A2: Disables screen updating while the macro runs. A3: Stores the name of the active chart in the name ChartName. A4: Stores the definition of the first series in the chart in the name XVals. A5-A9: These lines determine the name of the worksheet from which the chart was created and store the name as SourceWorksheet. A10: Substitutes “xlSheet” for the name of the worksheet contained in the name XVals. This is done in case the worksheet name contains commas (this step allows the commands in A11 and A16 to function correctly). A11, A16: These lines extract the cell range used to create the chart ($B$2:$ B$6, for example). A12-A15: These lines check to see if the chart is using “assumed” X values. If so, an alert message is displayed and the macro is terminated. If your data is laid out in the correct manner (as shown above), the macro should pass through these lines without displaying the alert message. A17: Substitutes SourceWorksheet back into xVals in place of “xlSheet”. A18: Converts xVals into a proper text reference. A19: Initializes a counter. A20: For each cell in the range xVals (in the example, $B$2:$ B$6)… A21: Gets the value in the cell to the left of the x-value. A22: Attaches text to the current data point in the chart. A23: Selects the attached text. A24: Puts the text of the label into the attached text. A25: Increments the counter. A26: Continues the loop until all labels have been processed. A27: Makes sure nothing is selected in the chart. A28: Ends the macro. </li>  Select cell A1. </li>  In Microsoft Excel version 4.0, click Define Name on the Formula menu. In Microsoft Excel version 5.0, click Define Name on the Insert menu, and then click Define. </li>  Click the Command option button. </li>  Click OK to accept the change. </li></ol>

The macro is now defined and may be run by clicking Run on the Macro menu (version 4.0) or clicking Macro on the Tools menu (version 5.0). Make sure the chart window is active when you run the macro.

Visual Basic Code Example Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided ‘as is’ and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code–comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the “programming Style in This Manual” section in the “Document Conventions” section of the “Visual Basic User’s Guide.”

  In a new module, enter the following code: Sub AttachLabelsToPoints ’ Dimension variables. Dim Counter As Integer, ChartName As Variant Dim SourceWorksheet As Variant, xVals As Variant, xCell As Variant Dim xLabel As Variant ’ Disable screen updating while the subroutine runs. Application.ScreenUpdating = False ’ Store the definition of the first series in “xVals”. xVals = ActiveChart.SeriesCollection(1).Formula ’ These lines extract the name of the source worksheet from xVals. SourceWorksheet = Left(xVals, InStr(1, xVals, “!”) - 1) SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - _ InStr(1, SourceWorksheet, “(”)) If Left(SourceWorksheet, 1) = “,” Then SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - 1) End If ’ Replace the actual source worksheet name with “xlSheet” so that the ’ searches that follow will work correctly if the worksheet name ’ contains commas. xVals = Application.Substitute(xVals, SourceWorksheet, “xlSheet”)  ’ More processing of the xVals name. xVals = Right(xVals, Len(xVals) - InStr(1, xVals, “,”))  ’ If the chart is using “assumed” x-values then show an alert ’ message If Left(xVals, 1) = “,” Then   ' NOTE: the following two lines should be entered  ' as a single line.  MsgBox &quot;This X-Y scatter chart is using assumed X values.&quot; & _    &quot; The macro cannot continue.&quot;

' Exit the subroutine if &quot;assumed&quot; x-values are in use. Exit Sub End If ’ More processing of the xVals name. xVals = Left(xVals, InStr(1, xVals, “,”) - 1) ’ Put the original source worksheet name back into xVals, replacing ’ “xlSheet”. xVals = Application.Substitute(xVals, “xlSheet”, SourceWorksheet) ’ Initialize a counter. Counter = 1 ’ For each cell in the range xVals… For Each xCell In Range(xVals) ' Get the value of the label next to the current x-value. xLabel = xCell.Offset(0, -1).Value

' Attach a label to the current data point in the chart. ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _ True

' Put the text (&quot;DataPoint1&quot;, for example) into the attached ' label. ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _ xLabel

' Increment the counter. Counter = Counter + 1 Next xCell                           'loop until all done ’ Make sure nothing in the chart is selected. Application.ExecuteExcel4Macro “SELECT(”&quot;&quot;“)” End Sub </li>  Activate the chart sheet. </li>  On the Tools menu, click Macro. </li>  In the Macro Name/Reference list, click AttachLabelsToPoints, and click Run. </li></ol>

Additional reference words: 7.00 4.00 4.00a 5.00 5.00a 5.00c

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.

Copyright Microsoft Corporation 1996.