Macro to Add Labels to
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
- 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.
- Have your Scatter Map open on your screen
- On the top menu bar open up TOOLS -->MACRO-->VISUAL BASIC EDITOR
- Visual Basic will open
- On the top menu open INSERT--->MODULE
- When you complete step #5, an empty box will open on your screen
- 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).
- On the Menu bar - go to VIEW--->MICROSOFT EXCEL- this should take
you back to your Excel Scatter Map
- Go to TOOLS --> MACRO --> MACROS.....
- A box will open with a macro called "AttachLabelsToPoints"
- hit the RUN button
- PRESTO - your labels should be added to your map.
- 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.
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, ",")
Do While Left(xVals, 1) = ","
xVals = Mid(xVals, 2)
'Attach a label to each data point in the chart.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value