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


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:

Data Bar Object

Working with Excel Data Bars

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.