{"id":670,"date":"2010-06-17T12:34:30","date_gmt":"2010-06-17T16:34:30","guid":{"rendered":"http:\/\/blog.tomleslie.ca\/?p=670"},"modified":"2010-06-18T12:06:50","modified_gmt":"2010-06-18T16:06:50","slug":"670","status":"publish","type":"post","link":"http:\/\/blog.tomleslie.ca\/?p=670","title":{"rendered":"Sparklines"},"content":{"rendered":"

A sparkline<\/a> is a miniature line graph that shows trending information over a time period. It is designed to be shown next to the name of a data type in a table of data to show a quick “at a glance” reference of how the data is trending over time. Microsoft is introducing sparklines as a patented new feature in Excel 2010, which is causing some controversy as the concept was actually invented by Edward Tufte. In any case, I wanted to use sparklines now, not wait for Office 2010, so I wrote the macro below.<\/p>\n

To use it, select your data set (must be in a single row but need not be contiguous) and then (holding down the control button) the cell where you want the sparkline inserted. Then run the macro, which should create a nice little line graph and align it to the cell you’ve chosen.<\/p>\n

Please note that the macro has no error detection & handling logic of any kind so if your experience is not a happy one you are completely on your own. Have a nice day.
\n
\n
\nSub Sparkline()
\n'
\n' Sparkline Macro
\n' Macro written 14\/06\/2010 by Tom Leslie
\n'
\n' Creates a sparkline graph and inserts it into the selected cell (the last in the range)<\/code><\/p>\n

Dim iRangeWidth As Integer
\nDim rTargetCell As Range
\nDim rDataRange As Range
\nDim iMin, iMax As Double
\nDim sSheet As Worksheet
\nDim cChart As Chart
\nDim strChartName As String<\/p>\n

Set rTargetCell = Selection.Areas(Selection.Areas.Count).Cells(Selection.Areas(Selection.Areas.Count).Cells.Count)<\/p>\n

If Selection.Areas.Count = 1 Then
\niRangeWidth = Selection.Cells.Count – 1
\nSet rDataRange = Range(Selection.Cells(1), Selection.Cells(iRangeWidth))
\nElse
\nSet rDataRange = Selection.Areas(1)
\niRangeWidth = rDataRange.Cells.Count
\nFor i = 2 To Selection.Areas.Count – 1
\nSet rDataRange = Union(rDataRange, Selection.Areas(i))
\niRangeWidth = rDataRange.Cells.Count
\nNext i
\nEnd If<\/p>\n

iMin = Application.Min(rDataRange)
\niMax = Application.Max(rDataRange)<\/p>\n

rDataRange.Select
\nSet sSheet = ActiveSheet<\/p>\n

Charts.Add
\nSet cChart = ActiveChart
\nActiveChart.ChartType = xlLine
\nActiveChart.SetSourceData Source:=rDataRange, PlotBy:=xlRows
\nActiveChart.Location Where:=xlLocationAsObject, Name:=sSheet.Name
\nWith ActiveChart.Axes(xlValue)
\n.MinimumScale = iMin – (iMax – iMin) * (1 \/ rTargetCell.Height)
\n.MaximumScale = iMax + (iMax – iMin) * (1 \/ rTargetCell.Height)
\n.ReversePlotOrder = False
\n.ScaleType = xlLinear
\n.DisplayUnit = xlNone
\nEnd With
\nWith ActiveChart
\n.HasAxis(xlCategory, xlPrimary) = False
\n.HasAxis(xlValue, xlPrimary) = False
\nEnd With
\nActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlAutomatic
\nWith ActiveChart.Axes(xlCategory)
\n.HasMajorGridlines = False
\n.HasMinorGridlines = False
\nEnd With
\nWith ActiveChart.Axes(xlValue)
\n.HasMajorGridlines = False
\n.HasMinorGridlines = False
\nEnd With
\nActiveChart.HasLegend = False
\nWith Selection.Border
\n.Weight = 1
\n.LineStyle = 0
\nEnd With
\nSelection.Interior.ColorIndex = xlNone
\nActiveChart.PlotArea.Select
\nWith Selection.Border
\n.Weight = xlThin
\n.LineStyle = xlNone
\nEnd With
\nSelection.Interior.ColorIndex = xlNone
\nActiveChart.SeriesCollection(1).Select
\nWith Selection.Border
\n.ColorIndex = 17
\n.Weight = xlMedium
\n.LineStyle = xlContinuous
\nEnd With
\nWith Selection
\n.MarkerBackgroundColorIndex = xlNone
\n.MarkerForegroundColorIndex = xlNone
\n.MarkerStyle = xlNone
\n.Smooth = False
\n.MarkerSize = 3
\n.Shadow = False
\nEnd With
\nActiveChart.PlotArea.Height = rTargetCell.Height \/ 2
\nActiveChart.Refresh
\nDebug.Print ActiveChart.PlotArea.Height
\nstrChartName = Right(ActiveChart.Name, Len(ActiveChart.Name) – Len(sSheet.Name))
\nWith ActiveSheet.Shapes(strChartName)
\n.Left = rTargetCell.Left
\n.Top = rTargetCell.Top – 3
\n.LockAspectRatio = False
\n.Width = rTargetCell.Width
\n.Height = rTargetCell.Height + 6
\nDebug.Print “Target:” & .Height
\nDebug.Print ActiveChart.ChartArea.Height
\nEnd With
\nActiveChart.Refresh
\nWith ActiveChart.PlotArea
\n.Left = 0
\n.Top = 0
\nEnd With
\nActiveSheet.Cells(1, 1).Select
\nsSheet.ChartObjects(strChartName).Select
\nActiveChart.PlotArea.Height = rTargetCell.Height
\nActiveChart.PlotArea.Width = rTargetCell.Width
\nDebug.Print “Height:” & ActiveChart.ChartArea.Height & “:” & ActiveChart.PlotArea.Height<\/p>\n

End Sub<\/p>\n","protected":false},"excerpt":{"rendered":"

A sparkline is a miniature line graph that shows trending information over a time period. It is designed to be shown next to the name of a data type in a table of data to show a quick “at a glance” reference of how the data is trending over time. Microsoft is introducing sparklines as […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,15],"tags":[18,17],"class_list":["post-670","post","type-post","status-publish","format-standard","hentry","category-office","category-technical-tips","tag-excel","tag-sparkline"],"_links":{"self":[{"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/posts\/670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=670"}],"version-history":[{"count":7,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/posts\/670\/revisions"}],"predecessor-version":[{"id":676,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=\/wp\/v2\/posts\/670\/revisions\/676"}],"wp:attachment":[{"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=670"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.tomleslie.ca\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}