Databar Object (Excel)

Office 2013 and later
GitHub-Mark-64px

Contribute to this content

Use GitHub to suggest and submit changes. See our guidelines for contributing to VBA documentation.

Represents a data bar conditional formating rule. Applying a data bar to a range helps you see the value of a cell relative to other cells.

All conditional formatting objects are contained within a FormatConditions collection object, which is a child of a Range collection. You can create a data bar formatting rule by using either the Add or AddDatabar methods of the FormatConditions collection.

You use the MinPoint and MaxPoint properties of the Databar object to set the values of the shortest bar and longest bar of a range of data. These properites return a ConditionValue object, with which you can specify how the thresholds are evaluated.

The Databar object also provides properties that enable you to specify an axis line that is displayed when negative values are present, and to specify the color and formatting of data bars.

The following example creates a range of data and then applies a data bar to the range. You will notice that because there is an extremely low and high value in the range, the middle values have data bars that are of similiar length. To disambiguate the middle values, the sample code uses the ConditionValue object to change how the thresholds are evaluated to percentiles.

Sub CreateDataBarCF() 
 
 Dim cfDataBar As Databar 
 
 ' Create a range of data with a couple of extreme values 
 With ActiveSheet 
 .Range("D1") = 1 
 .Range("D2") = 45 
 .Range("D3") = 50 
 .Range("D2:D3").AutoFill Destination:=Range("D2:D8") 
 .Range("D9") = 500 
 End With 
 
 Range("D1:D9").Select 
 
 ' Create a data bar with default behavior 
 Set cfDataBar = Selection.FormatConditions.AddDatabar 
 MsgBox "Because of the extreme values, middle data bars are very similar" 
 
 ' The MinPoint and MaxPoint properties return a ConditionValue object 
 ' which you can use to change threshold parameters 
 cfDataBar.MinPoint.Modify newtype:=xlConditionValuePercentile, _ 
 newvalue:=5 
 cfDataBar.MaxPoint.Modify newtype:=xlConditionValuePercentile, _ 
 newvalue:=75 
 
End Sub
Show: