Progress indicator, becoming not responding in vba

Question

I have been reading through stackoverflow on how to use progressbar.

It works fine , but after a short time 6%, row 14 in my array of 235 it says not responding, and does so until the loop finishes.

So I am running it vbmodeless:

ProgressBar.Show vbModeless

ProgressBar.Label_WhatsGoingOn.Caption = "Reading data from database.."

projectNumber = "32966"
docOutArray = Post.helpRequest("xdnjgjrdin.asp?Dok4=" & projectNumber)

If CPearson.IsArrayEmpty(docOutArray) Then
    MsgBox "No document registered in database!"
End If

ProgressBar.Label_WhatsGoingOn.Caption = "Creating Docout.."

Set doc_ = NEwDocOut.createDocOutDocument(projectNumber)

numOfRows = UBound(docOutArray, 1)


For i = LBound(docOutArray, 1) To numOfRows
    ProgressBar.Label_WhatsGoingOn.Caption = "Creating Row.."

    sPercentage = (i / numOfRows) * 100
    ProgressBar.progress (sPercentage)

My progressbar code:

Private Sub UserForm_Initialize()
Me.Text.Caption = "0% Completed"
End Sub

Public Sub progress(pctCompl As Single)

Me.Text.Caption = Format(pctCompl, "##") & "% Completed"
Me.Bar.Width = pctCompl * 2
Me.Repaint
DoEvents

End Sub

Any ideas why this happens?

Preston · Accepted Answer · 2016-10-07T13:34:58.640

2

This has been annoying me for a while and you've prompted me to look for an answer- thank you.

It's actually remarkably simple, just drop DoEvents inside your loop, in your case:

For i = LBound(docOutArray, 1) To numOfRows
    'Add this line here:
    DoEvents

    ProgressBar.Label_WhatsGoingOn.Caption = "Creating Row.."

    sPercentage = (i / numOfRows) * 100
    ProgressBar.progress (sPercentage)

edited Oct 07 '16 at 13:34

answered Oct 07 '16 at 13:22

Preston

7,399
8
54
84

I think "DoEvents" considers all events, not just the events that update the progress bar. Generally this is not a problem but if you have other buttons/forms that might be open which people could click you should take precautions to ensure those clicks won't interfere with your already running code. – Shimeon Oct 07 '16 at 21:36
Yes, @tompreston it works kinda, the problem now is that some times the progressbar gets in the background and that sometimes the code fails saying that progressbar can not be displayed vbmodeless because its already exist or sth. I am looking into this right now. – skatun Oct 11 '16 at 06:14

skatun · Answer 2 · 2016-10-14T11:40:00.597

Here is my updated code suggestion:

Public Sub progress(pctCompl As Single)
    Me.Text.Caption = Format(pctCompl, "##") & "% Completed"
    Me.Bar.Width = pctCompl * 2
    Me.Repaint
End Sub

Public Sub setMessage(message As String, Optional percentage As Single = 0#)
    If Not ProgressBar.Visible Then ProgressBar.Show vbModeless
    ProgressBar.Label_WhatsGoingOn.Caption = message
    If percentage <> 0 Then Call ProgressBar.progress(percentage)
End Sub

RatherB · Answer 3 · 2019-07-26T06:49:01.257

0

Try adding blanks to the end of your caption - then (Not Responding) will be out of view.

ProgressBar.Label_WhatsGoingOn.Caption = message & space(40)

edited Jul 26 '19 at 06:49

answered Jul 24 '19 at 08:55

RatherB

1
1

Progress indicator, becoming not responding in vba

3 Answers3