Working with Sparkline Properties in Office 2010

  • Article

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.

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:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

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

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

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

Next Steps