Working with Excel Data Bars

Office 2010

Office Quick Note banner

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

Last modified: April 22, 2011

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:

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)
        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

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.