May 3, 2017

Create Data Bar Using VBA

Today we’ll create a data bar using VBA. Before we do that let’s see how we can create a data bar using conditional formatting. We can also record the complete process while we perform the conditional formatting on our data to get the data bars. We notice that the recorded macro to create the data bars is quite rigid. Using the recorded macro we can write our own macro to create the data bars. This macro first finds the data in the worksheet. Then it identifies the first row, first column, last row and the last column using the find function. Also we automate the process for getting the minimum and maximum values for our data bar. Watch the video below to learn how data bars are created using VBA before studying the recorded and hand-crafted VBA code:


Watch this video on YouTube.

Recorded macro for data bar creation:

Sub Macro5()

‘ Macro5 Macro

With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newValue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newValue:=22000
End With
With Selection.FormatConditions(1).BarColor
.Color = vbBlue
.TintAndShade = 0
End With
Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
Selection.FormatConditions(1).Direction = xlRTL
Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With Selection.FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With Selection.FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
End Sub

Handcrafted VBA code for more flexibility in creating and manipulating data bars that can be used for data visualization or creating dashboards:

Sub createDataBarCF()
Dim cfDataBar As Databar
Dim FirstRow As Long, FirstCol As Long, LastRow As Long, LastCol As Long

FirstRow = Cells.Find(What:=”*”, SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
FirstCol = Cells.Find(What:=”*”, SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
LastRow = Cells.Find(What:=”*”, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:=”*”, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

FirstRow = FirstRow + 1
Range(Cells(FirstRow, FirstCol), Cells(LastRow, FirstCol)).FormatConditions.Delete
Range(Cells(FirstRow, FirstCol), Cells(LastRow, FirstCol)).Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With

Set cfDataBar = Range(Cells(FirstRow, FirstCol), Cells(LastRow, FirstCol)).FormatConditions.AddDatabar

With cfDataBar
.BarFillType = xlDataBarSolid
.Direction = xlRTL
With .BarColor
.ColorIndex = 3
.TintAndShade = 0
End With
End With

With Range(Cells(FirstRow, FirstCol), Cells(LastRow, FirstCol)).FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueAutomaticMin
.MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
End With


End Sub

Further reading:

Data Bar Object

Working with Excel Data Bars