How to use a VBA macro to add labels to data points in an XY (scatter) chart or in a bubble chart in Excel 2007
This article contains a sample VBA macro that adds data labels to an XY (scatter) chart. You can also use this macro to add data labels to a bubble chart.
- The first column contains the data labels.
- The second column contains the x values for the XY (scatter) chart.
- The third column and subsequent columns contain the y values for the XY (scatter) chart.
Note Although the following data has only one column of data for the y values, you can use additional columns for y values in your own worksheets.
|A1:||Data labels||B1:||X values||C1:||Y values|
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
To add data labels to an XY (scatter) chart, follow these steps:
- On the worksheet that contains the sample data, select the cell range B1:C6.
Note Do not select the whole cell range of the sample data. Select only cell range B1:C6.
- On the Insert tab, in the Illustrations group, click Chart.
- In the Create Chart dialog box, click the type of XY (scatter) chart that you want, and then click OK. The XY (scatter) chart is added to the worksheet.
- In the worksheet, click the XY (scatter) chart. Then, on the Design tab, in the Location group, click Move Chart.
- In the Move Chart dialog box, click New Sheet, name the new chart sheet, and then click OK.
- Press ALT+F11 to start Visual Basic Editor.
- On the Insert menu, click Module.
- Type the following sample macro code in the module sheet.
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 CounterEnd Sub
- Press ALT+Q to return to Excel 2007.
- Click the Developer tab.
Note If the Developer tab does not appear, click the File tab, click Excel Options, click Personalize, and then click to select the Show Developer tab in the Ribbon check box.
- On the Ribbon, click Macros.
- In the Macro dialog box, click the AttachLabelsToPoints macro, and then click Run.
The macro attaches the data labels that are in the cell range A2:A6 to the data points that are on the chart.
Article ID: 914813 - Last Review: 10/15/2007 21:37:00 - Revision: 1.5
- kbhowto kbinfo kbmacro kbchart kbprogramming kbautomation kbvba kbexpertiseinter KB914813