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
‘
‘
Range(“C2:C13”).Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
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
Range(“C14″).Select
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
Range(“A1”).Select
End Sub
Further reading:
