Advanced Excel Sparklines (Line) Chart VBA, C#, VB.Net example

Advanced Excel Sparklines (Line) Chart VBA, C#, VB.Net example

Sparklines

are tiny charts inside single worksheet cells that can be used to visually represent a trend in your data. Sparklines can be used to show trends in your worksheet data which can be useful, especially when you’re sharing your data with other people.

figure 1.0

Create Sparkline

  1. Select a blank cell near the data you want to show in a sparkline.
  2. On the Insert tab, in the Sparklines group, click Line.
figure 1.1

3. In the Data Range box, enter the range of cells that has the data you want to show in the sparkline. And select Range where you wish to put Sparkline under Location Range.

figure 1.2

Since a Sparkline is embedded in a cell, any text you enter in the cell uses the Sparkline as its background, as shown below:

figure 1.3

To format various features of Sparklines, select the cell containing Sparkline and toggle Design tab where you can Show/Hide points, Style, Axis etc. Lets put code around to automate the same. In this article we are using VSTO (Visual Studio Tools for office) in both flavors (C#, VB.Net) and VBA (Visual Basic for Applications) hence following prerequisites are recommended:

Prerequisites

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example

private void btnSparkLine_Click(object sender, RibbonControlEventArgs e)
{
	//Retain user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	if (oRange != null)
	{
		//Capture user response
		var userRange = Globals.ThisAddIn.Application.InputBox("Select range to create Sparkline:",
			"VBAOVERALL Range Selector", string.Empty, System.Reflection.Missing.Value,
			System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, 8);
		
		//Cast user range
		if (userRange != null)
		{
			//Cast user response into valid Range object
			excel.Range sourceRange = (excel.Range) userRange;

			//Add Sparkline group
			excel.SparklineGroup oSparklineGroup=oRange.SparklineGroups.Add(excel.XlSparkType.xlSparkLine, sourceRange.Address);
			//Add various property
			oSparklineGroup.SeriesColor.Color = 9592887;
			oSparklineGroup.SeriesColor.TintAndShade = 0;
			oSparklineGroup.Points.Negative.Color.Color = 208;
			oSparklineGroup.Points.Negative.Color.TintAndShade = 0;
			oSparklineGroup.Points.Markers.Color.Color = 208;
			oSparklineGroup.Points.Markers.Color.TintAndShade = 0;
			oSparklineGroup.Points.Highpoint.Color.Color = 208;
			oSparklineGroup.Points.Highpoint.Color.TintAndShade = 0;
			oSparklineGroup.Points.Lowpoint.Color.Color = 208;
			oSparklineGroup.Points.Lowpoint.Color.TintAndShade = 0;
			oSparklineGroup.Points.Firstpoint.Color.Color = 208;
			oSparklineGroup.Points.Firstpoint.Color.TintAndShade = 0;
			oSparklineGroup.Points.Lastpoint.Color.Color = 208;
			oSparklineGroup.Points.Lastpoint.Color.TintAndShade = 0;

			//Visible all points
			oSparklineGroup.Points.Highpoint.Visible = true;
			oSparklineGroup.Points.Lowpoint.Visible = true;
			oSparklineGroup.Points.Negative.Visible = true;
			oSparklineGroup.Points.Firstpoint.Visible = true;
			oSparklineGroup.Points.Lastpoint.Visible = true;
			oSparklineGroup.Points.Markers.Visible = true;
		}
	}
	else
	{
		MessageBox.Show(@"Select a valid range");
	}
}

VB.Net code example

Private Sub btnSparkLines_Click(sender As Object, e As RibbonControlEventArgs) Handles btnSparkLines.Click
	'Retain user selection
	Dim oRange As excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Capture user response
	dim userRange as Object
	userRange = Globals.ThisAddIn.Application.InputBox("Select range to create Sparkline:",
		"VBAOVERALL Range Selector", string.Empty, System.Reflection.Missing.Value,
		System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, 8)

	If Not oRange Is Nothing Then
		'Cast user response into valid Range object
		Dim sourceRange As excel.Range
		sourceRange=CType(userRange,excel.Range)

		'Add Sparkline group
		Dim oSparklineGroup as excel.SparklineGroup
		oSparklineGroup=oRange.SparklineGroups.Add(excel.XlSparkType.xlSparkLine, sourceRange.Address)

		'Add various property
		oSparklineGroup.SeriesColor.Color = 9592887
		oSparklineGroup.SeriesColor.TintAndShade = 0
		oSparklineGroup.Points.Negative.Color.Color = 208
		oSparklineGroup.Points.Negative.Color.TintAndShade = 0
		oSparklineGroup.Points.Markers.Color.Color = 208
		oSparklineGroup.Points.Markers.Color.TintAndShade = 0
		oSparklineGroup.Points.Highpoint.Color.Color = 208
		oSparklineGroup.Points.Highpoint.Color.TintAndShade = 0
		oSparklineGroup.Points.Lowpoint.Color.Color = 208
		oSparklineGroup.Points.Lowpoint.Color.TintAndShade = 0
		oSparklineGroup.Points.Firstpoint.Color.Color = 208
		oSparklineGroup.Points.Firstpoint.Color.TintAndShade = 0
		oSparklineGroup.Points.Lastpoint.Color.Color = 208
		oSparklineGroup.Points.Lastpoint.Color.TintAndShade = 0

		'Visible all points
		oSparklineGroup.Points.Highpoint.Visible = True
		oSparklineGroup.Points.Lowpoint.Visible = True
		oSparklineGroup.Points.Negative.Visible = True
		oSparklineGroup.Points.Firstpoint.Visible = True
		oSparklineGroup.Points.Lastpoint.Visible = True
		oSparklineGroup.Points.Markers.Visible = True
	Else
		MsgBox("Select a valid range") 
	End If
End Sub

VBA code example

Private Sub SparkLinesCodeExample()
    'Retain user selection
    Dim oRange As Range
    Set oRange = Selection

    'Capture user response
    Dim userRange As Range
    Set userRange = Application.InputBox(prompt:="Select Range", Title:="VBAOVERALL Range Selector", Type:=8)

    If Not oRange Is Nothing Then
        'Cast user response into valid Range object
        Dim sourceRange As Range
        Set sourceRange = userRange

        'Add Sparkline group
        Dim oSparklineGroup As SparklineGroup
        Set oSparklineGroup = oRange.SparklineGroups.Add(Excel.XlSparkType.xlSparkLine, sourceRange.Address)

        'Add various property
        oSparklineGroup.SeriesColor.Color = 9592887
        oSparklineGroup.SeriesColor.TintAndShade = 0
        oSparklineGroup.Points.Negative.Color.Color = 208
        oSparklineGroup.Points.Negative.Color.TintAndShade = 0
        oSparklineGroup.Points.Markers.Color.Color = 208
        oSparklineGroup.Points.Markers.Color.TintAndShade = 0
        oSparklineGroup.Points.Highpoint.Color.Color = 208
        oSparklineGroup.Points.Highpoint.Color.TintAndShade = 0
        oSparklineGroup.Points.Lowpoint.Color.Color = 208
        oSparklineGroup.Points.Lowpoint.Color.TintAndShade = 0
        oSparklineGroup.Points.Firstpoint.Color.Color = 208
        oSparklineGroup.Points.Firstpoint.Color.TintAndShade = 0
        oSparklineGroup.Points.Lastpoint.Color.Color = 208
        oSparklineGroup.Points.Lastpoint.Color.TintAndShade = 0

        'Visible all points
        oSparklineGroup.Points.Highpoint.Visible = True
        oSparklineGroup.Points.Lowpoint.Visible = True
        oSparklineGroup.Points.Negative.Visible = True
        oSparklineGroup.Points.Firstpoint.Visible = True
        oSparklineGroup.Points.Lastpoint.Visible = True
        oSparklineGroup.Points.Markers.Visible = True
    Else
        MsgBox ("Select a valid range")
    End If
End Sub

Please leave your comments or queries under comment section also please do subscribe to out blogs to keep your self upto date.

Leave a Reply

Your email address will not be published.