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

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

Sparklines 

are tiny charts inside single cells that can be used to graphically represent the data. Sparklines can be used to show trends in your worksheet. Following is the data source that would consider to create sparklines.

figure 1.0

Steps to create Sparklines in a cell:

  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

To format Sparklines, select the cell containing Sparkline and toggle Design tab where you can perform various formatting options like Show/Hide points, Style, Axis etc.

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.xlSparkColumn, 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 btnSparkLinesCode_Click(sender As Object, e As RibbonControlEventArgs) Handles btnSparkLinesCode.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.xlSparkColumn, 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 SparkLinesExampleCode()
    '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.xlSparkColumn, 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.