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.

bigsis1

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

Posted in Uncategorized

14 thoughts on “Regular chart version of a sparkline

  1. Very clever. I like this a lot.
    Your End If / End With / End Sub / End Select tabbing is strange.

  2. Ok, this is probably a rookie question, buy how do you add a sparkline to a cell? I’ve always wanted to know, it’s more than just a really small graph right?

  3. I think it’s hard to read, because it’s distracting, because it’s different from the tabbing most people go by.
    I reckon it’s a rebellious thing… you know… the Excel equivalent of wearing a leather jacket. I can relate to rebel – I cannot relate to an extra tab character! No, Sir.

  4. Hi,
    I am using tiny graphs add-in in excel 2007. I would like to use this to be able to get a regular chart version of a sparkline.When I added the code as mentioned above and tried to compile it , I get an error saying “user-defined type not defined” — highlights the setcharttype.

    Can you help me resolve this issue ?

  5. @Paul. The ‘tiny graphs’ add-in makes a small graph, that doubles as a sparkline without actually being one. So this code isn’t what you need. Instead, you need code that will take a graph (not a sparkline), increase the size, and add the axis and title.

    Note that I prefer small graphs to sparklines myself, because in 2007 and later you can align it to the cell so that it resizes if the cell resizes, and have access to all the formatting options and chart trickery you get with any normal 2007 chart.

    I wrote a comment at http://peltiertech.com/WordPress/sparklines-for-excel-vs-excel-2010-sparklines-guest-post/ that has some screenshots of the kind of thing I was using tinycharts for, if you’re interested.

Leave a Reply

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