ProgressBar

Cell-based visual progress indicator on a worksheet. Tints cells to reflect completion ratio and optionally writes status text to a companion cell.

THE BAR PAINTS IN THE INSTANCE IT LIVES IN

Every render writes the cells and calls DoEvents once, so the paint lands before the caller goes on. The bar leaves the screen switch alone. It is driven from an instance whose screen is on, where a cell write paints by itself, and a bar that switched the screen on and off would be the flicker the in-place build keeps out.

THE BAR CELLS CARRY COLOUR ONLY

The bar shows progress by tint. Attach clears its cells and they stay empty for the whole run, so one band of colour moving along the range is the whole display.

The bar used to write the value pair into its first cell. It gave the user a second thing to read at every tick, and Excel parsed the pair a milestone count produces -- "3 / 12" -- as a date, so the cell showed 12-Mar under a dd-mmm format while the band moved.

Words go to the status cell. It is set to the text format at the point of write, since the driver's text can be anything, a setup file named for a date included.

Depends on: ProjectError

Version: 1.3 (2026-08-27)

Lifecycle

Create #

create

Factory returning an initialised interface

Signature:

Public Function Create(ByVal barRange As Range, Optional ByVal maximum As Long = DEFAULT_MAXIMUM) As ProgressBar

Parameters:

  • barRange: Range. Range that will host the indicator.
  • maximum: Long. Maximum value. Defaults to DEFAULT_MAXIMUM.

Returns: ProgressBar. 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.

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 and maximum

Signature:

Private Sub Class_Initialize()

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

Signature:

Private Sub RenderProgress()

Settle #

settle

Let the latest render paint before the caller goes on

Signature:

Private Sub Settle()

One DoEvents. The screen of the instance the bar lives in is on, so the cell writes paint on their own once the message loop gets a turn.

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)

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)
Used in (4 file(s))