Advanced Excel DataBar using VBA, C#, VB.Net example

Advanced Excel DataBar using VBA, C#, VB.Net example

DataBar

Shows how the shortest bar or longest bar is evaluated for a data bar conditional formatting rule. The ConditionValue object is returned by using either the MaxPoint or MinPoint property of the DataBar object.

We can change the type of evaluation from the default setting (lowest value for the shortest bar and highest value for the longest bar) by using the Modify method. 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 btnDataBar_Click(object sender, RibbonControlEventArgs e)
{
	//Retain user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	if (oRange != null)
	{
		//Declare databar object with default behavior
		excel.Databar oDatabar = oRange.FormatConditions.AddDatabar();

		//The MinPoint property return a ConditionValue object which we can use to change threshold parameters
		oDatabar.MinPoint.Modify(excel.XlConditionValueTypes.xlConditionValuePercentile, 10);

		//The MaxPoint property return a ConditionValue object which we can use to change threshold parameters
		oDatabar.MaxPoint.Modify(excel.XlConditionValueTypes.xlConditionValuePercentile, 100);
	}
	else
	{
		MessageBox.Show(@"Select a valid range");
	}
}

VB.Net code example

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

	If not oRange is Nothing Then
		'Declare databar object with default behavior
		dim oDataBar as excel.Databar
		oDatabar = oRange.FormatConditions.AddDatabar()

		'The MinPoint property return a ConditionValue object which we can use to change threshold parameters
		oDatabar.MinPoint.Modify(excel.XlConditionValueTypes.xlConditionValuePercentile, 10)

		'The MaxPoint property return a ConditionValue object which we can use to change threshold parameters
		oDatabar.MaxPoint.Modify(excel.XlConditionValueTypes.xlConditionValuePercentile, 100)
	Else 
		MsgBox("Select a valid range")
	End If
End Sub

VBA code example

Public Sub DataBar()
 
    Dim oDataBar As DataBar
    Dim oRange As Range
    
    Set oRange = Selection
    
    'Create a data bar with default behavior
    Set oDataBar = oRange.FormatConditions.AddDatabar
     
    'The MinPoint property return a ConditionValue object which we can use to change threshold parameters
    oDataBar.MinPoint.Modify newtype:=xlConditionValuePercentile, newvalue:=10
    
    'The MaxPoint property return a ConditionValue object which we can use to change threshold parameters
    oDataBar.MaxPoint.Modify newtype:=xlConditionValuePercentile, newvalue:=100
     
End Sub

XlConditionValueTypes Enum

  • xlConditionValueAutomaticMax
  • xlConditionValueAutomaticMin
  • xlConditionValueFormula
  • xlConditionValueHighestValue
  • xlConditionValueLowestValue
  • xlConditionValueNone
  • xlConditionValueNumber
  • xlConditionValuePercent
  • xlConditionValuePercentile

Output

figure 1.0

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.