I was recently doing some work for a client in which I build an Excel WorkBook that processed a series of large CSV files to clean them up, format the content, … because of their size the process could take several minutes and they contacted me thinking it wasn’t working and that they thought the process had hung.
So I set out to provide them with a visual display of the process’ progress, a progress bar!
The Solution
I wanted to keep things simple and didn’t feel like getting into building a userform in this instance and devised a simple solution.
I was aware that Excel had conditional formatting that could display progress such as: Data Bars and Color Scales.
I thought to myself that I could automate these to display the progress to the user and the solution was born!
The general concept is very straight forward, either approach visually changes based on the value of a cell, so I needed only to update the value of a conditionally formatted cell and Excel would take care of the rest for me.
Hence, it was a question of:
- Setting up a cell with conditional formatting
- while performing a task, iteration, … update that cell’s value
What I came up with was the following:
Using Color Scales
Color Scales are interesting because visually you see the progress via the changing of color (and the percentage value as well). Furthermore, you can use whatever colors you want as they are configured in the VBA procedure.
Sub SetupColorScales(ByVal rng As Excel.Range)
With rng
.Value = 0
.ColumnWidth = 65
If rng.FormatConditions.Count = 0 Then 'Primitive validation
'Gradient Background (red, orange, then green)
.FormatConditions.AddColorScale ColorScaleType:=3
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).ColorScaleCriteria(1).Type = _
xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(1).Value = 0
With .FormatConditions(1).ColorScaleCriteria(1).FormatColor
.Color = 5263615
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(2).Type = _
xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(2).Value = 0.5
With .FormatConditions(1).ColorScaleCriteria(2).FormatColor
.Color = 8711167
.TintAndShade = 0
End With
.FormatConditions(1).ColorScaleCriteria(3).Type = _
xlConditionValueNumber
.FormatConditions(1).ColorScaleCriteria(3).Value = 1
With .FormatConditions(1).ColorScaleCriteria(3).FormatColor
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.249977111117893
End With
'General Value Formatting
.Style = "Percent"
.NumberFormat = "0.00%"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Bold = True
End If
End With
End Sub
Sub ProcessData()
Dim rng As Excel.Range
Dim lLastRow As Long
Dim i As Long
Dim lRowOffset As Long
Dim lTotalRows As Long
Const lStartRow As Long = 2
Const lProgressUpdateFrequency As Long = 10 'Update progress every 10 cycles
lRowOffset = lStartRow - 1
Set rng = Range("J4")
Call SetupColorScales(rng)
With ActiveSheet
lLastRow = .Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
lTotalRows = lLastRow - lRowOffset
For i = lLastRow To 2 Step -1
If Range("E" & i).Value = "F" Then .Rows(i & ":" & i).Delete Shift:=xlUp
If (i Mod lProgressUpdateFrequency = 0) Then
' rng.Value = 1 - (i / (lLastRow - 1))
rng.Value = 1 - (i - lRowOffset) / lTotalRows
DoEvents
End If
Next i
rng.Value = 1
End With
Set rng = Nothing
End Sub
x
Using Data Bars
Data Bar are interesting because visually you see the progress bar grow as the process progresses (and the percentage value as well).
Sub SetupDataBar(ByVal rng As Excel.Range)
With rng
.Value = 0
.ColumnWidth = 65
If rng.FormatConditions.Count = 0 Then 'Primitive validation
'Data bar (grows)
.FormatConditions.AddDatabar
.FormatConditions(.FormatConditions.Count).ShowValue = True
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=1
End With
With .FormatConditions(1).BarColor
.ThemeColor = xlThemeColorAccent3
.TintAndShade = -0.249977111117893
End With
.FormatConditions(1).BarFillType = xlDataBarFillSolid
.FormatConditions(1).Direction = xlContext
.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
With .FormatConditions(1).AxisColor
.Color = 0
.TintAndShade = 0
End With
With .FormatConditions(1).NegativeBarFormat.Color
.Color = 255
.TintAndShade = 0
End With
'General Value Formatting
.Style = "Percent"
.NumberFormat = "0.00%"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Bold = True
End If
End With
End Sub
Sub ProcessData()
Dim rng As Excel.Range
Dim lLastRow As Long
Dim i As Long
Dim lRowOffset As Long
Dim lTotalRows As Long
Const lStartRow As Long = 2
Const lProgressUpdateFrequency As Long = 10 'Update progress every 10 cycles
lRowOffset = lStartRow - 1
Set rng = Range("J4")
Call SetupDataBar(rng)
With ActiveSheet
lLastRow = .Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
lTotalRows = lLastRow - lRowOffset
For i = lLastRow To 2 Step -1
If Range("E" & i).Value = "F" Then .Rows(i & ":" & i).Delete Shift:=xlUp
If (i Mod lProgressUpdateFrequency = 0) Then
' rng.Value = 1 - (i / (lLastRow - 1))
rng.Value = 1 - (i - lRowOffset) / lTotalRows
DoEvents
End If
Next i
rng.Value = 1
End With
Set rng = Nothing
End Sub
How Does It All Work?
There are 2 procedures: ProcessData and SetupColorScales/SetupDataBar.
Basically, ProcessData is the main sub that is executed by the user. One of the first things it does is call SetupColorScales/SetupDataBar which sets up the Conditional Formatting on a cell to show the ‘Progress Bar’, then it continues with the processing and updates the value of the cell over the iterative process’ lifetime. So as it performs its work, it continuously updates the cells value with the percentage completed and Excel automatically updates the visuals of the Conditionally Formatted cell resulting in a ‘Progress Bar’ style output to the end-user.
That’s it! Truly not complex.
This technique cal be utilized for any iterative process or any process that involves multiple steps for which you can calculate a percentage completion value.
The beauty here is that it is all very straightforward plain VBA and can be implemented in any Excel WorkBook/WorkSheet with a simple Copy/Paste.