Show Percent Complete When Running Excel Macro

Question

I am using a 'Worksheet_Change' event in a macro that takes about 15-20 seconds to run each time a user selects an option in a dropdown due to accessing a large data range. I want to show a very basic % complete status in Excel to let the user know it is processing. Using the default Excel Application.StatusBar would suffice, but it is not visible on my workbook.

My working hide/show columns macro:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R, V
If Target.Address = ("$K$7") Then
    V = [K7].Value
    For Each R In Range("R3:GJU3")
        If IsError(R.Value) Then
            R.EntireColumn.Hidden = True
        Else
            R.EntireColumn.Hidden = R.Value <> V
        End If
    Next
End If

End Sub

I found the code below on a separate thread, but am unsure how to modify for my purposes based on my macro above.

Code below credit to @eykanal at Progress bar in VBA Excel

Option Explicit

Sub StatusBar()

Dim x               As Integer
Dim MyTimer         As Double

    'Change this loop as needed.
    For x = 1 To 250

        'Dummy Loop here just to waste time.
        'Replace this loop with your actual code.
        MyTimer = Timer
        Do
        Loop While Timer - MyTimer < 0.03

        Application.StatusBar = "Progress: " & x & " of 250: " & Format(x / 250, "Percent")
        DoEvents

    Next x

    Application.StatusBar = False

End Sub

this is not a progress bar but a show percent in statusbar. For progressbar you need to know how to work with userforms. Second thing, your code is wayyyyyy to slow. Use vba array. — Patrick Lepelletier, Jul 15 '17 at 00:25
@PatrickLepelletier I am not familiar with vba arrays. Do you have an example that would speed up my macro? — vash1422, Jul 15 '17 at 00:50
search on google or on StackOverflow. You'll find many examples. I cannot add this code here, since it's not the question of the Thread. (or make a new one) — Patrick Lepelletier, Jul 15 '17 at 14:23

gr8tech · Answer 1 · 2017-07-15T00:49:43.330

0

Update your code as follows

Private Sub Worksheet_Change(ByVal Target As Range)

Dim R, V
Dim x As Range
Dim counter As Long
counter = 1

If Target.Address = ("$K$7") Then
    V = [K7].Value
    Set x = Range("R3:GJU3")

    For Each R In Range("R3:GJU3")
        If IsError(R.Value) Then
            R.EntireColumn.Hidden = True
        Else
            R.EntireColumn.Hidden = R.Value <> V
        End If
        DoEvents
        Application.StatusBar = Format(counter / x.Columns.Count, "Percent")
        counter = counter + 1
    Next R
End If

End Sub

edited Jul 15 '17 at 00:49

answered Jul 15 '17 at 00:18

gr8tech

174
1
7

Hi - thanks for your reply. I updated with your code, but do not see the status bar dynamically changing. The worksheet looks the same while it is running the macro, then just displays '100'. It does not show the % progression while running. – vash1422 Jul 15 '17 at 00:41
try again with the modification I have made – gr8tech Jul 15 '17 at 00:43
same thing as before -- functionality remains but no progress is dynamically being displayed. This time end result is "100.00%" after macro is done running. – vash1422 Jul 15 '17 at 00:46
Okay yet another modification. I was trying to avoid the DoEvents as it slows things down. Try and see if works out for you – gr8tech Jul 15 '17 at 00:50
the progress is now dynamically displaying, however it seems to be disconnected with the timing of my show/hide columns macro. For instance, my show/hide columns macro is complete and the % complete is around 4% continues to run...also very slow like you said. – vash1422 Jul 15 '17 at 01:04
any chance you have time to look at this again? – vash1422 Jul 19 '17 at 17:43

score 0 · Answer 2 · answered Jul 15 '17 at 14:33

You could use a Userform for ProgressBar, ending up with something like

My Progressbar is on the lower part of my userform. When looping, you need to increase the width of the progressbar (here a blu-ish button)

The code for doing this is elementary (simple rule of tree).

Show Percent Complete When Running Excel Macro

2 Answers2