Excel.DataBar Method in MS Excel to Apply Conditional Formatting in VB.NET

In this article I am going to explain about how to work with data bars in Microsoft Excel 2010.
  • 5151

Introduction

In this article I am going to explain about how to work with data bars in Microsoft Excel 2010. For this we use Excel.DataBar Method. Using this method it fill, axis position, and the color of negative and positive values.

Microsoft Office 2010 offer some powerful tools, using this tools you can create application. Using Microsoft Visual Basic for Applications (VBA) you can create your own application according to your need. These application can performer some specific task.

For creating application we can use

  • VBA host of Excel 2010
  • VBA host of PowerPoint 2010
  • VBA host of Word 2010

NOTE : OneNote 2010 is not a VBA host.

Code that we use in this application are given below

Sub TestDataBars()
    Dim a As Range
    a = FillRange(-25, 25, 20)
    a.FormatConditions.Delete()
    Dim b As Databar
    b = a.FormatConditions.AddDatabar
    ' Set the endpoints for the data bars:
    b.MinPoint.Modify(xlConditionValueNumber, -25)
    b.MaxPoint.Modify(xlConditionValueNumber, 25)
     ' Set the axis position.
    b.AxisPosition = xlDataBarAxisMidpoint
     ' You can use a gradient or a solid fill type.
    b.BarFillType = xlDataBarFillSolid
    Dim c As FormatColor
    With b.BarColor
        .Color = vbBlue
        .TintAndShade = -0.2
    End With
     ' Modify the behavior of positive and negative
     ' bar borders:
    With b.BarBorder
        .Type = xlDataBarBorderSolid
        .Color.Color = vbGreen
    End With
    b.AxisColor.Color = vbRed
    b.Direction = xlRTL
    With b.NegativeBarFormat
        .BorderColorType = xlDataBarSameAsPositive
        .ColorType = xlDataBarColor
        .Color.Color = vbRed
    End 
With
End Sub
Function FillRange(minValue As Integer, maxValue As Integer, count As IntegerAs Range
    Dim i As Integer
    For i = 1 To count
        ' Generate random numbers between minValue and maxValue
        Me.Range("A" & i).Value = Int((maxValue - minValue + 1) * Rnd + minValue)
    Next i
    FillRange = Me.Range("A1""A" & count)
End Function

Step for creating Application

Step 1 : Start MS Excel 2010 :

1.jpg

2.jpg
 

Step 2 : Using Alt + F11 Key Start Visual Basic for Applications (VBA) Window :

3.jpg
 

Step 3 : Select on Sheet 1 :

4.jpg
 

Step 4 : Right Click on Sheet 1 and select View Code :

5.jpg
 

Step 5 : Write Code in Visual Basic for Applications (VBA) Window :

6.jpg
 

Step 6 : Run Application using F5 :

Step 7 : Macros window will open, Select Macros name and click on Run Button :

7.jpg
 

Step 8 : Output of Application :

8.jpg
 

 

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.