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 

1. On the worksheet, select the range B1:C6, and press F11.

2. In the ChartWizard - Step 1 of 5 dialog box, click the Next button.

3. In the ChartWizard - Step 2 of 5 dialog box, click XY (Scatter) and then
   click the Next button.

4. In the ChartWizard - Step 3 of 5 dialog box, click the Next button.

5. 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: 

   a. Select one of the series in the xy (scatter) chart.

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



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. 

1. 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("")
   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.

2. Select cell A1.

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

4. Click the Command option button.

5. Click OK to accept the change.


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

1. 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 "This X-Y scatter chart is using assumed X values." & _
           " The macro cannot continue."

         ' Exit the subroutine if "assumed" 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 ("DataPoint1", 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("""")"
   End Sub

2. Activate the chart sheet.

3. On the Tools menu, click Macro.

4. In the Macro Name/Reference list, click AttachLabelsToPoints, and click
   Run.



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.
