2

I just want to underline that such behavior may happen. Refer to:

Why does VBA code run faster when called from a standard module (instead of a userform)?

Excel VBA code execution dramatically faster after breaking and resumiing

I have a vast code and it may contain a lot of “not so nice“ code sections. It has hundreds of sheets and all sheets containing only data but no calculations. Very nice appearance: No screen flickering, most time <ScreenUpdating.false>, only one sheet shows info about jobs done, one progress bar with <ProgressWindow.Show>. The program does its job in an industrial company but some report may take several hours.
From Time to time I try to speed up some sections. I tried all kind of tips and tricks. Subsequently I come across the same behavior and made me lose some hours.

One of the following interactions speeded up the processes:

  • Esc and continue the code
  • Change to another application

Finally I made two option buttons:

  • “info” off: ___ sheet with info about jobs done
  • “bar” off: ____ progress bar
run time “info” “bar” interaction
100% on on --
50% off off --
25% off on --
10% off off either one
10% off on either one

Does someone have ideas/suggestions:

  • Why is the program running faster when progress bar is shown? (IsProgressBarOff=False)
  • Why do such interactions speed up the program?
  • How could I make advantage in the code of these interactions?
Sub ShowProgressBar(info)

    If IsProgressBarOff Then Exit Sub

    'activate progress bar
    ProgressWindow.FullBar.Width = 0

    'progress text
    ProgressWindow.ProgressInfo.Caption = ""

    'progress titel
    ProgressWindow.Caption = info
    ProgressWindow.Show

    ’positioning progress window
    ProgressWindow.Top = Application.Top + (StartUpWindow.Height / 3) + 115      
    ProgressWindow.Left = (Application.Left + 280)
End Sub

Information added after comment of @CLR

Sub Sample
  some code
  ShowProgressBar   Loop1
  nr = 1
    For Each s In Application.Sheets
      
       SetProgressBar_A   nr  s.Name  

       some code 'which will take some seconds

       nr = nr + 1
    next s
End Sub


Sub SetProgressBar_A(incr, info)

     If verifyVisual_IsProgressBarOff Then Exit Sub    
    
    'change bar
    If incr > 100 Then incr = 100
    If incr < 2 Then incr = 2
    ProgressWindow.FullBar.Width = incr * 3
    
    'show info
    ProgressWindow.ProgressInfo.Caption = info
    
    ProgressWindow.Repaint

    DoEvents    
End Sub
CH Oldie
  • 41
  • 5
  • One tip for writing to progress bars is: test that what you're writing is different to that already on the bar. This saves a lot of time. Say you're writing an integer percentage complete value, you only want to actually change that a maximum of 100 times, but it might get written to a million times within a loop - so check that the value has changed before doing anything. – CLR Aug 05 '22 at 07:27
  • I do not understand our tip 100%. However I added some more code in my question. Hopeing this considered your tip. – CH Oldie Aug 05 '22 at 14:35
  • I will better explain it in an answer format - though it's only a small part of the answer you seek. – CLR Aug 05 '22 at 14:56

1 Answers1

0

The following will help you understand how writing to the Status Bar without a simple check can significantly slow your process down.

Imagine you're running a loop, from 1 to 200,000. Your status bar needs to tell the user how far through the loop you are, just as a whole percentage - for example:

Progress: 14%

If you update the status bar every iteration of the loop, you write to the status bar 200,000 times.

If you only update each time the value being written changes, you will only need to write it 101 times (0%, 1% - 100%).

You can see the effect of this difference with the following:

Sub test_status_bar()

    ' declare variables
    Dim sb_start As Long, sb_end As Long, i As Long
    
    'set loop parameters
    sb_start = 1
    sb_end = 200000
    
    'start stopwatch
    t = Now()
    
    'start loop
    For i = 1 To sb_end
    
        'update status bar with percentage of loop complete
        updatestatusbar "Progress: " & Int(i / sb_end * 100) & "%"
        
    Next
    
    'advise how long it took
    Debug.Print "First pass (purely writing to statusbar) took " & Format((Now() - t), "hh:mm:ss")
    
    'start stopwatch
    t = Now()
    
    'start loop
    For i = 1 To sb_end
    
        'update status bar with percentage of loop complete
        updatestatusbar2 "Progress: " & Int(i / sb_end * 100) & "%"
        
    Next
    
    'advise how long it took
    Debug.Print "Second pass (only writing when text different) took " & Format((Now() - t), "hh:mm:ss")
End Sub


'this version just writes whatever is needed to the status bar
Sub updatestatusbar(txt)
    Application.StatusBar = txt
End Sub

'this version checks that the txt being written is different to that
'already displayed and only writes if different
Sub updatestatusbar2(txt)
    If Application.StatusBar <> txt Then Application.StatusBar = txt
End Sub

Output:

First pass (purely writing to statusbar) took 00:00:17

Second pass (only writing when text different) took 00:00:01

CLR
  • 11,284
  • 1
  • 11
  • 29