Macro to Add Labels to Points
in an XY (Scatter) Chart

A macro is nothing more than a small program which automates a task. What you are going to do is to automatically place labels on the chart you previously created.

 

It is not difficult to run macros...and to do this, you are going to open up a program called Visual Basic, copy and paste some text into the program and then move back into excel and run the program. It is not difficult if you follow the following steps EXACTLY.

  1. Open up the Excel file with your plot data and "map" (actually scatter chart) already created. If you have not done this yet - STOP and go to Making a Map.
  2. Have your Scatter Map open on your screen
  3. On the top menu bar open up TOOLS -->MACRO-->VISUAL BASIC EDITOR
  4. Visual Basic will open
  5. On the top menu open INSERT--->MODULE
  6. When you complete step #5, an empty box will open on your screen
  7. Paste the text located at the bottom of this page into this box - HINT: highlight this text, Go to the menu bar to EDIT -->Copy. Click back to Excel and place your cursor in the empty box and PASTE (EDIT--->PASTE).
  8. On the Menu bar - go to VIEW--->MICROSOFT EXCEL- this should take you back to your Excel Scatter Map
  9. Go to TOOLS --> MACRO --> MACROS.....
  10. A box will open with a macro called "AttachLabelsToPoints" - hit the RUN button
  11. PRESTO - your labels should be added to your map.
  12. SAVE this file -- you don't want to lose all this work!

 

Macro text is below - Highlight only the code - Copy this code and then paste it into Visual Basic Editor.

 

Sub AttachLabelsToPoints()
'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String
' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False
'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).Formula
'Extract the range for the data from xVals.
xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
Loop
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter
End Sub