Periodic Status Bar Updates Based On Calculation

NamssoB

Board Regular
Joined
Jul 8, 2005
Messages
69
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm processing a worksheet with thousands of records, and I'd like to keep the user updated with the progress using the status bar. BUT - I don't want to show the current record number EVERY time it changes (assuming this is VERY I/O intensive). So I want to display an updated status at even 10% increments. How do I do this?

Here's what I tried, but it's not even close. In essence, I try to convert a ROUNDED number to a 2-digit string (10.45 = "10"), then I use RIGHT to check if there is a zero.

Code:
        RowCountStatus = Round((1 - (lastrow / OrginalRowCount)) * 100, 0)
        If Right(Str(RowCountStatus), 1) = "0" Then
            Application.StatusBar = RowCountStatus & "% complete..."
        End If

Any ideas?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

NamssoB

Board Regular
Joined
Jul 8, 2005
Messages
69
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Got it. Clumsy, but it works. B90, etc are booleans. Without the booleans, it would just display every single number, every time. So I used the booleans to "turn off" any repeats.

Code:
        RowCountStatus = 1 - (lastrow / OriginalRowCount)
        If RowCountStatus > 0.9 And Not b90 Then
            Application.StatusBar = Format(RowCountStatus * 100, "##") & "% complete..."
            b90 = True
        Else
            If RowCountStatus > 0.75 And Not b75 Then
                Application.StatusBar = Format(RowCountStatus * 100, "##") & "% complete..."
                b75 = True
            Else
                If RowCountStatus > 0.5 And Not b50 Then
                    Application.StatusBar = Format(RowCountStatus * 100, "##") & "% complete..."
                    b50 = True
                Else
                    If RowCountStatus > 0.25 And Not b25 Then
                        Application.StatusBar = Format(RowCountStatus * 100, "##") & "% complete..."
                        b25 = True
                    End If
                End If
            End If
        End If
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,418
Maybe something like this...

Code:
        [color=green]'Initialize[/color]
        Application.StatusBar = "0% complete..."
        NextStatus = 0.25 [color=green]'%Increment[/color]
        
        [color=green]'loop Records[/color]
            [color=darkblue]If[/color] NextStatus <= OriginalRowCount / lastrow [color=darkblue]Then[/color]
                Application.StatusBar = Format(NextStatus, "0%") & " complete..."
                NextStatus = NextStatus + 0.25  [color=green]'%Increment[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=green]'Next[/color]
 

NamssoB

Board Regular
Joined
Jul 8, 2005
Messages
69
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Maybe something like this...

Code:
        [color=green]'Initialize[/color]
        Application.StatusBar = "0% complete..."
        NextStatus = 0.25 [color=green]'%Increment[/color]
        
        [color=green]'loop Records[/color]
            [color=darkblue]If[/color] NextStatus <= OriginalRowCount / lastrow [color=darkblue]Then[/color]
                Application.StatusBar = Format(NextStatus, "0%") & " complete..."
                NextStatus = NextStatus + 0.25  [color=green]'%Increment[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=green]'Next[/color]

Wow! Great idea, much simpler than mine. And it works....thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,133,527
Messages
5,659,335
Members
418,497
Latest member
VAllen79

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top