Working with Sparkline Properties in Office 2010

Office 2010

Office Quick Note banner

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.

Last modified: May 19, 2011

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | VBA | Word 2010

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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:

In this task, you add programming code that creates the sparkline with data.

To add code to the Visual Basic Editor

  1. Start Excel.

  2. Press Alt + F11 to open the Visual Basic Editor.

  3. In the Project pane, double-click Sheet1.

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

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

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.

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