Spread Sheet Integrated Progress Bar

• Download source - 14.9 KB

Introduction

Just recently, I was starting to spend some more time on writing macros in Excel. Sometimes, there is a demand where you want to run a macro and show some form of a progress bar to give the user some feedback. This approach embeds the progress bar into the spread sheet. Hence, there is no need to open a form and show progress in e.g. an ActiveX control.

In the straight forward case of using simple single threaded VBA, this would look like:

• Using a cell to hold a percent value in the interval [0%..100%]
• Using EXCEL's conditional formatting capabilities to render the value as a bar
• Running a macro which at certain times updates the progress cell value and calls DoEvents to refresh the displayed bar

The focus of this article is not on discussing conditional formatting in detail. There are many articles available on this topic. Instead, the article shows how to use/build a simple progress bar prototype and eventually apply the concept to your own Excel spreadsheet.

Background

The use of conditional formatting in Excel to show progress is not new. Project managers use it all the time to show task or project progress. In my case, I was looking for a simple way to implement concept for a task like a macro. In the case of VBA, there is not much you can do. You are stuck in one UI thread and therefore you can only update one bar at a time. I agree that may not impress everybody. But it can come in handy if you use a package like Excel-DNA to add C# capabilities and embed your code into an Excel add-in. With the mentioned tool, this is actually quite easy and straight forward. Or even in a scenario where you run multiple macros or macro steps in sequence.

Be aware though that even if .NET allows you to run multiple background threads, you cannot just write back to Excel from these threads. You can only write to Excel from a single UI thread. I am currently thinking of coming up with a pattern which gives control back to the UI thread to update the current state of multiple parallel running threads.

Using the Code

The easiest way to understand the concept is to open the sample demo spreadsheet and investigate the conditional formatting settings.

I am using the merged cells $C$4:$B:4 to hold the progress value and the conditional formatting. And give the range a name of "PROGRESSBAR1" to make the access from VBA code easier. By the way, I do the same thing for the cell holding the value for seconds (="SECS").

The conditional format takes a low/minimum value and a high/maximum value. In this scenario, the values are set to 0 and 1 and the Format Style is set to "Data Bar". Choose any colour you like. You can also set a background colour and/or borders in the cell format if you like.

This sample only uses a value range from [0%..100%] = [0..1]. But it should be easy to extend the concept to any value range [a..b].

//
// This snippet shows you how to update the progress bar cell from within a macro
//
Public Sub Macro_Process1()
    
	...
    For i = 1 To steps
        'some code to execute
		...
        'when we've reached the end of a single iteration we update the progress bar cell
        Set progressBar = Application.ActiveSheet.Range("PROGRESSBAR1")
        progressBar.Value = {the new value between 0 and 1}
        DoEvents
        Set progressBar = Nothing
    
    
	Next i
	...
    
End Sub

The sample spread sheet includes a macro which simulates a macro which needs "SECS" seconds to execute and update the cell periodically. To make more "simulation", calculate a random value for each step. It would be boring otherwise, wouldn't it.

The sample macro Macro_Progress() can be invoked by hitting Ctrl-Shift P.

Use the value specified for Secs: to determine how long the progress bar simulation shall take (e.g. 2).

//
// This macro generates a sequence of steps with random step execution time and updates the progress bar
//
Option Explicit

'change this if you want to update more often than twice a second
Const stepsPerSec = 2

'I use the win32 function to simulate that the macro is busy for n milliseconds
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub Macro_Progress()
Dim progressBar As Range
Dim progressDelta
Dim Progress
Dim Secs
Dim steps
Dim stepArray()
Dim i
Dim total
 
    'Initialize the random numbers
    Randomize
    Set progressBar = Application.ActiveSheet.Range("PROGRESSBAR1")
    progressBar.Value = 0
    Secs = Application.ActiveSheet.Range("SECS").Value
    steps = Secs * stepsPerSec
    ReDim stepArray(steps - 1)
    total = 0
    'fill an array with random numbers and calculate the total of all random numbers
    For i = LBound(stepArray) To UBound(stepArray)
        stepArray(i) = Rnd()
        total = total + stepArray(i)
    Next i
    'loop through the generated numbers and divide by the total. This will give us values\
    'which add up to 1
    For i = LBound(stepArray) To UBound(stepArray)
        stepArray(i) = stepArray(i) / total
    Next i
    'finally loop through the values and wait n milliseconds so that the total execution time
    'adds up to the value specified in "SECS" in the spread sheet
    For i = LBound(stepArray) To UBound(stepArray)
        Application.ScreenUpdating = False
        Progress = progressBar.Value
        progressDelta = stepArray(i)
        'Debug.Print Round(progressDelta, 4)
        Progress = Progress + progressDelta
        'just to make sure we don't get anything larger than 1
        If Progress > 1 Then Progress = 1 Else Sleep ((Secs * 1000) / steps)
        progressBar.Value = Progress
        Application.ScreenUpdating = True
        DoEvents
    Next i
    Set progressBar = Nothing

End Sub

Points of Interest

I recommend everyone who is interested in C# and Excel Automation to have a look at this:

• Excel-DNA

If you're interested in learning about conditional formatting in Excel:

• Excel - Add, change, find, or clear conditional formats

History

• 18/04/2015 - Just a quick trick to get a simple progress bar going - Initial version