Share via

Working with Excel Data Bars

  • Article

Office Quick Note banner

Programmatically Working with Office 2010 Charts: Learn how to create and manipulate Microsoft Excel 2010 data bars.

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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

  • Test the Solution

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

  1. Open a new Excel 2010 workbook.

  2. Press Alt + F11 to open the Visual Basic Editor.

  3. In the Project pane, double-click Sheet1.

  4. 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

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust both windows until you can see them both.

  3. 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.

Next Steps