ProgressBar
Cell-based visual progress indicator on a worksheet. Tints cells to reflect completion ratio and optionally writes status text to a companion cell.
Depends on: ProjectError
Version: 1.0 (2026-02-09)
Lifecycle
Create #
create
Factory returning an initialised interface
Signature:
Public Function Create(ByVal barRange As Range, Optional ByVal maximum As Long = DEFAULT_MAXIMUM) As IProgressBar
Parameters:
barRange: Range. Range that will host the indicator.maximum: Long. Maximum value. Defaults to DEFAULT_MAXIMUM.
Returns: IProgressBar. Ready to use.
Public API
Attach #
attach
Bind the progress bar to a worksheet range
Signature:
Public Sub Attach(ByVal barRange As Range, Optional ByVal maximum As Long = DEFAULT_MAXIMUM)
Parameters:
barRange: Range. Hosting the progress indicator.maximum: Long. Maximum value. Defaults to DEFAULT_MAXIMUM.
ConfigureColors #
configure-colors
Configure the tint for completed and pending cells
Signature:
Public Sub ConfigureColors(ByVal completedColor As Long, ByVal pendingColor As Long)
Parameters:
completedColor: Long. Colour applied to completed cells.pendingColor: Long. Colour applied to remaining cells.
ConfigureValueFormat #
configure-value-format
Configure the number format for progress text
Signature:
Public Sub ConfigureValueFormat(ByVal formatPattern As String)
Parameters:
formatPattern: String. VBA format pattern.
AttachStatusCell #
attach-status-cell
Bind a cell for textual status messages
Signature:
Public Sub AttachStatusCell(ByVal statusRange As Range)
Parameters:
statusRange: Range. Single-cell range that will receive status text.
Update #
update
Update progress to a specific value
Signature:
Public Sub Update(ByVal currentValue As Long, Optional ByVal statusMessage As String = vbNullString)
Parameters:
currentValue: Long. Absolute progress value.statusMessage: String. Message pushed to the status cell. Defaults to vbNullString.
StepBy #
step-by
Increment progress by the provided step
Signature:
Public Sub StepBy(Optional ByVal stepValue As Long = 1, Optional ByVal statusMessage As String = vbNullString)
Parameters:
stepValue: Long. Increment. Defaults to 1.statusMessage: String. Message pushed to the status cell. Defaults to vbNullString.
Complete #
complete
Set progress to its maximum state
Signature:
Public Sub Complete(Optional ByVal statusMessage As String = vbNullString)
Parameters:
statusMessage: String. Message pushed to the status cell. Defaults to vbNullString.
Reset #
reset
Reset progress to zero
Signature:
Public Sub Reset(Optional ByVal statusMessage As String = vbNullString)
Parameters:
statusMessage: String. Message pushed to the status cell. Defaults to vbNullString.
Properties
BarRange #
bar-range
Range hosting the progress bar
Signature:
Public Property Get BarRange() As Range
Returns: Range. The bar range.
StatusCell #
status-cell
Status cell receiving text messages
Signature:
Public Property Get StatusCell() As Range
Returns: Range. The status cell, or Nothing.
Maximum #
maximum
Maximum expected value
Signature:
Public Property Get Maximum() As Long
Returns: Long. The maximum value.
Maximum #
maximum-set
Update the maximum expected value
Signature:
Public Property Let Maximum(ByVal value As Long)
Parameters:
value: Long. New maximum (must be greater than zero).
Value #
value
Current progress value
Signature:
Public Property Get Value() As Long
Returns: Long. The current value.
PercentComplete #
percent-complete
Compute the current completion percentage
Signature:
Public Property Get PercentComplete() As Double
Returns: Double. Ratio between 0 and 1.
Internal members (not exported)
Lifecycle
Class_Initialize #
class-initialize
Set default values for colours, format, and maximum
Signature:
Private Sub Class_Initialize()
Self #
self
Self-reference as IProgressBar
Signature:
Public Property Get Self() As IProgressBar
Returns: IProgressBar. Self-reference.
Internal Helpers
EnsureInitialised #
ensure-initialised
Guard against use before Attach is called
Signature:
Private Sub EnsureInitialised()
ValidateBarRange #
validate-bar-range
Validate the bar range argument
Signature:
Private Sub ValidateBarRange(ByVal barRange As Range)
ValidateStatusRange #
validate-status-range
Validate the status range argument
Signature:
Private Sub ValidateStatusRange(ByVal statusRange As Range)
ClampValue #
clamp-value
Clamp a value to the 0..maximum range
Signature:
Private Function ClampValue(ByVal candidateValue As Long) As Long
Returns: Long. The clamped value.
ComputeRatio #
compute-ratio
Compute the completion ratio for a value
Signature:
Private Function ComputeRatio(ByVal candidateValue As Long) As Double
Returns: Double. Ratio between 0 and 1.
ClampRatio #
clamp-ratio
Clamp a ratio to 0..1
Signature:
Private Function ClampRatio(ByVal ratio As Double) As Double
Returns: Double. The clamped ratio.
RenderProgress #
render-progress
Re-render the bar and value text
Signature:
Private Sub RenderProgress()
RenderStatus #
render-status
Write the latest status text to the status cell
Signature:
Private Sub RenderStatus(ByVal statusMessage As String)
PrepareBarRange #
prepare-bar-range
Clear all cells and set them to the pending colour
Signature:
Private Sub PrepareBarRange()
PaintBar #
paint-bar
Tint cells according to the completion ratio
Signature:
Private Sub PaintBar(ByVal ratio As Double)
WriteValue #
write-value
Write the formatted progress text to the first cell
Signature:
Private Sub WriteValue()
ResolveMaximum #
resolve-maximum
Validate and return a positive maximum
Signature:
Private Function ResolveMaximum(ByVal candidateMaximum As Long) As Long
Returns: Long. The validated maximum.
ThrowError #
throw-error
Raise a typed project error
Signature:
Private Sub ThrowError(ByVal errNumber As ProjectError, ByVal message As String)