Advanced Excel Sparklines (Win/Loss) Chart VBA, C#, VB.Net example

Advanced Excel Sparklines (Win/Loss) Chart VBA, C#, VB.Net example

Sparklines 

are tiny charts inside single worksheet cells that can be used to visually represent and show 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.

Consider below data as source to create Win/Loss chart:

figure 1.0
  1. Select first row data as shown below figure 1.1
  2. Click on Win/Loss button on Insert tab under Sparklines group as shown below figure 1.2
  3. Select Location range where you want to see Win/Loss result as shown in figure 1.3
figure 1.1
figure 1.2
figure 1.3

Finally the win/loss result is populated as below, same can be dragged down or copy pasted through below to get win/loss charts.

figure 1.4

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 btnTrendLine_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.xlSparkColumnStacked100, 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.xlSparkColumnStacked100, 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 SparkLines()
    '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.xlSparkColumnStacked100, 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.