Regular chart version of a sparkline

In one of the social.answers.microsoft.com web forums (Microsoft’s replacement for the newsgroups no longer hosted on its own server) someone wanted to see a regular chart version of a sparkline when s/he clicked on the cell. I thought that was an interesting idea since one can see much more detail in a large object than a tiny sparkline. Here’s my take on it. The version below improves on what I posted in social.answers by correctly handling cases where the source of a sparkline is another sheet / book and by hiding the chart if the selected range contains multiple cells or if the selected cell does not contain a sparkline. This version is also more modularized.

In the code module of the workbook that contains the sparklines:

Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    AutoBigChart Target
    End Sub

Then, the code below goes in a regular module.

The function getBigChart returns a reference to an existing chart that was previously created by the code or creates a new one, if necessary. If it finds an existing chartobject named SparklineBigSis it does not adjust any format or size attribute. This allows the user to customize the chart once it is created.

The subroutine setChartType selects a chart type based on the type of sparkline in the selected cell.

The setChartSource routine adds a series to the chart and sets the series’ source to that of the sparkline. It also accounts for the different formats in which a sparkline returns its SourceData — if the data are in the same sheet as the sparkline there is no sheet information.

The AutoBigChart routine then essentially just calls each of the support routines as needed.

Option Explicit
Const BigSisRatio As Integer = 5
    'big chart is this much larger than cell size
Const BigSisName As String = "SparklineBigSis"
Function getBigChart(aCell As Range) As ChartObject
    On Error Resume Next
    Dim aChartObj As ChartObject
    Set aChartObj = aCell.Parent.ChartObjects(BigSisName)
    On Error GoTo 0
    If aChartObj Is Nothing Then
        Set aChartObj = aCell.Parent.ChartObjects.Add( _
            aCell.Left + aCell.Width, aCell.Top, _
            aCell.Width * BigSisRatio, aCell.Height * BigSisRatio)
        aChartObj.Name = BigSisName
    Else
        With aChartObj
        .Left = aCell.Left + aCell.Width
        .Top = aCell.Top
            End With
        End If
    aChartObj.Visible = True
    Set getBigChart = aChartObj
    End Function
Sub deleteAllSeries(aChart As Chart)
    Do While aChart.SeriesCollection.Count > 0
        aChart.SeriesCollection(1).Delete
        Loop
    End Sub
Sub setChartType(aSparkline As SparklineGroup, aChart As Chart)
    Select Case aSparkline.Type
        Case xlsparkline:
            aChart.ChartType = xlLine
        Case xlSparkColumn:
            aChart.ChartType = xlColumnClustered
        Case xlSparkColumnStacked100:
            aChart.ChartType = xlColumnStacked100
        Case Else:
            MsgBox "Unknown type of sparkline chart (=" _
                & aSparkline.Type
            End Select
    End Sub
Sub hideChartObj(aRng As Range)
    On Error Resume Next
    aRng.Parent.ChartObjects(BigSisName).Visible = False
    End Sub
Sub setChartSource(ByRef aChart As Chart, _
        ByVal aSparkGroup As SparklineGroup)
    Dim aRng As Range
    Set aRng = Range(aSparkGroup.SourceData)
    aChart.SeriesCollection.Add "=" & aRng.Address(True, True, xlA1, True)
    End Sub
Sub AutoBigChart(aRng As Range)
    With aRng
    If .Cells.Count <> 1 Then GoTo XIT
    If .SparklineGroups.Count = 0 Then GoTo XIT
    Dim aChartObj As ChartObject
    Set aChartObj = getBigChart(.Cells(1))
    deleteAllSeries aChartObj.Chart
    setChartSource aChartObj.Chart, .SparklineGroups(1)
    setChartType .SparklineGroups(1), aChartObj.Chart
        End With
    Exit Sub
XIT:
    hideChartObj aRng
    End Sub
Sub makeBigChart()
    AutoBigChart ActiveCell
    End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>