Excel 2010 Macro Create Sparklines
Microsoft figures that you will usually be creating a group of sparklines. The main VBA object for sparklines is the SparklineGroup. To create sparklines, you apply the SparklineGroups Add method to the range where you want the sparklines to appear. In the Add method, specify a type for the sparkline and the location of the source data. In this case, let’s say that you apply the add method to a three-cell range of B2:D2. Then the source must be a range that is either three columns wide or three rows tall.
The type parameter can be xlSparkLine for a line, xlSparkColumn for a column, or xlSpark-Column100 for Win/Loss. If the SourceData parameter refers to ranges on the current worksheet, it can be as simple as “D3:F100”. If it is pointing to another worksheet, use “Data!D3:F100” or “’My Data’!D3:F100”. If you’ve defined a named range, you can specify the name of the range as the source data.
FinalRow = WSD.[A1].CurrentRegion.Rows.Count
The .CurrentRegion property will start from Cell A1 and extend in all directions until it hits the edge of the worksheet or the edge of the data. In this case, the CurrentRegion will report that row 253 is the final row, even if A253 and D253 are blank. For this example, the sparklines will be created in a row of three cells. Since each cell is showing 250 points, fairly large sparklines are used.
The sparkline will grow to the size of the cell, so this code will make each cell quite wide and tall:
With WSL.Range(“B1:D1”)
.Value = array(2007,2008,2009)
.HorizontalAlignment = xlCenter
.Style = “Title”
.ColumnWidth = 39
.Offset(1, 0).RowHeight = 100
End With
To create three default sparklines:
Dim SG as SparklineGroup
Set SG = WSL.Range(“B2:D2”).SparklineGroups.Add( _
Type:=xlSparkLine, _
SourceData:=”Data!D2:F” & FinalRow
There are a number of problems with the default sparklines. Think about the vertical axis of a chart. Sparklines always default to have the scale automatically selected. Because you cannot see what the scale is, you cannot tell the range of the change.