Working with Sparkline Properties in Office 2010
Programmatically Working with Office 2010 Charts: Learn how to programmatically add a sparkline to a workbook and then set various properties in Microsoft Excel 2010.
Applies to: Microsoft Excel 2010
Published: June 2011
Provided by: Frank Rice, Microsoft Corporation
In Microsoft Excel 2010, sparklines can show a single time-series or the occurrence of events. Unlike a traditional chart, a sparkline is placed inside a cell, allowing you to easily create a large number of sparklines. In this topic, you add a sparkline to a workbook and then set various properties. To complete these tasks, you must do the following:
Add the Code to the Visual Basic Editor
In this task, you add programming code that creates the sparkline with data.
To add code to the Visual Basic Editor
Press Alt + F11 to open the Visual Basic Editor.
In the Project pane, double-click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code in the window that appears.
Sub WorkWithSparklines() ' Make sure you press Shift+F8 to step over this procedure. FillRandomData Dim rng As Range Set rng = Range("C2", "N11") ' Add sparklines to the second column Dim slg As SparklineGroup Dim slRng As Range Set slRng = Range("B2", "B11") Set slg = slRng.SparklineGroups.Add(XlSparkType.xlSparkLine, rng.Address) slg.Points.Highpoint.Visible = True ' Settings for the series: With slg.SeriesColor .ThemeColor = 5 .TintAndShade = 0 End With ' Marker settings: With slg.Points.Markers .Visible = True With .Color .ThemeColor = 6 .TintAndShade = 0.5 End With End With ' High point settings: With slg.Points.Highpoint .Visible = True With .Color .ThemeColor = 7 .TintAndShade = 0.5 End With End With ' Low point settings: With slg.Points.Lowpoint .Visible = True With .Color .ThemeColor = 2 .TintAndShade = 0.5 End With End With ' Now change the spark line type: slg.Type = xlSparkColumn End Sub Function FillRandomData() As Range ' No need to stop through this procedure. Dim month As Integer For month = 1 To 12 Cells(1, month + 2).Value = MonthName(month, True) Next month ' Fill in rows with random data. Dim i As Integer Dim j As Integer For i = 1 To 10 Cells(i + 1, 1).Value = "Sales " & i For j = 1 To 12 Cells(i + 1, j + 2) = Round(Rnd * 100) Next j Next i Range("C1").CurrentRegion.HorizontalAlignment = xlCenter Range("B:B").ColumnWidth = 15 End Function
Test the Solution
In this task, you step through the VBA code that adds a sparkline to a worksheet and then applies various settings.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.
Now, place the cursor in the WorkWithSparklines procedure and then press F8 to start debugging, then Shift+F8 to single-step through the code (stepping over any called procedures).