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

14 thoughts on “Regular chart version of a sparkline”

“Amazing” doesn’t even begin to describe it!!

Thanks.

How do we get started after we put the code into the workbook?

This is damned cool Tushar. Very nice.

@MB: After putting the code in the correct places, click in any cell containing a sparkline.

@Mike: Thanks. :)

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

Rob, For Tushar’s explanation of his indentation style see here:
http://www.dailydoseofexcel.com/archives/2008/04/28/testing-strings-using-left/#comment-32235

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?

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.

i tried to run this and i dont have sparklinegroup defined

I am going to try to implement this in some of my dashboards created in Excel. Thanks for the post. Additionally, there is some more ideas about sparklines you can find on my site. I have been using them for a while. I’m trying to compile more links and ideas on how to implement…so thanks for this article.

http://www.squidoo.com/what-is-a-sparkline-chart

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 ?

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