Working with Excel Data Bars
Programmatically Working with Office 2010 Charts: Learn how to create and manipulate Microsoft Excel 2010 data bars.
Applies to: Microsoft Excel 2010
Published: March 2011
Provided by: Frank Rice, Microsoft Corporation
Data bars in Microsoft Excel 2010 make it easy to visually compare a list of numbers; similar to the way a bar chart makes it easy to compare that same list. In this topic, you programmatically add a range of data to a worksheet, create data bars on that data, and then manipulate different characteristics of those bars. To complete these tasks, you must do the following:
Add the Code to the Visual Basic Editor
In this task, you add programming code that adds a range of data, creates data bars on that data, and then manipulates the bars. Read the comments before each section of the code to learn more about what it does.
To add code to the Visual Basic Editor
Open a new Excel 2010 workbook.
Press Alt + F11 to open the Visual Basic Editor.
In the Project pane, double-click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code in the window that appears.
Sub TestDataBars() ' Demonstrate DataBar: ' BarFillType ' AxisPosition ' AxisColor ' Direction ' NegativeBarFormat ' BarBorder Dim rng As Range Set rng = FillRange(-25, 25, 20) rng.FormatConditions.Delete Dim db As dataBar Set db = rng.FormatConditions.AddDatabar ' Set the endpoints for the data bars: db.MinPoint.Modify xlConditionValueNumber, -25 db.MaxPoint.Modify xlConditionValueNumber, 25 ' Set the axis position. ' The options are automatic, midpoint, or none. ' By default, if there are negative values, the ' axis will appear in the middle. Force it to ' use the midpoint: db.AxisPosition = xlDataBarAxisMidpoint ' You can use a gradient or a solid fill type. db.BarFillType = xlDataBarFillSolid Dim fc As FormatColor With db.BarColor .Color = vbBlue .TintAndShade = -0.2 End With ' Modify the behavior of positive and negative ' bar borders: With db.BarBorder .Type = xlDataBarBorderSolid ' Unfortunately, the BarBorder.Color property returns ' a ColorFormat object, so you'll end up setting ' the Color property of the Color property: .Color.Color = vbGreen End With ' Don't be misled: The AxisColor property is read-only. ' But it is, itself, a FormatColor object. Therefore, the ' reference to the FormatColor object is read-only, but the ' properties of that object are not read-only. You can ' modify any of the FormatColor object's properties: db.AxisColor.Color = vbRed ' You can set the direction of the bars. ' The default value is xlContext, which uses the ' direction of the current locale. If you want to ' force right-to-left direction (in other words, force ' the values to be reversed for left-to-right languages such ' as English), specify xlRTL. To do the same for RTL ' language such as Arabic or Hebrew, specify xlLTR. ' The following forces the negative/positive values to ' be reverse from their normal direction, for LTR languages: db.Direction = xlRTL ' You can set negative bars to appear different than positive bars. ' You can modify BorderColor, BorderColorType, Color, ColorType: With db.NegativeBarFormat ' Specify that you want to use the same, or a a different color, than the positive bars. ' Note that you must specify this before you specify the BorderColor ' property value, if you want to alter the color: .BorderColorType = xlDataBarSameAsPositive ' Specify that you want to use the same, or a a different color, than the positive bars. ' Note that you must specify this before you specify the BorderColor ' property value, if you want to alter the color: .ColorType = xlDataBarColor .Color.Color = vbRed End With End Sub Function FillRange(minValue As Integer, maxValue As Integer, count As Integer) As 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 Set FillRange = Me.Range("A1", "A" & count) End Function
Test the Solution
In this task, you run the VBA code that creates the data bars and then manipulates various properties of those bars. The best way to watch the interaction is to place the Visual Basic Editor window next to the Excel window and then single-step through each line of code.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.
Click the Visual Basic Editor window, place the cursor in the TestDataBars module, and then press F8 to step through the code line-by-line and watch how each line affects the data bars.