Update Userform Progress Bar

[Jess]

Hi Forum Members
I have developed an Excel 2003 vba programme to calculate the values of commissions payable to a number of different organisations.
Following a series of calculations initiated via a userform, a bespoke statement is then generated in Word the whole process taking approximately 7 seconds.
Because of the timescale I have introduced a progress bar on the form where the length of a label is increased in length at predefined points in the code with a status label text changing as the programme progresses.
All works fine until the word application is initiated where a delay of approx 2+ Secs occurs. Using the timeGetTime API I've indicated the timings in the adapted code snippet below.
So to my question: How can I detect when the Word application is loaded using a Do loop statement in order to update the progress bar during this delay.

'Declared at module level.
Public Declare Function timeGetTime Lib "winmm.dll" () As Long
dim lngStart as long

Sub Load_MSWord()
Dim wdApp As Object

lngStart = timeGetTime
If blArchive Then
    Call Update_ProgressBar(5, "Opening Word") '24ms elapsed
End If
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
    Err.Clear
    'Debug.Print "Post-GetObject: " & Finish '25ms elapsed  <<===
    Set wdApp = CreateObject("Word.Application")
    
    'Debug.Print "Post-Create oWord: " & Finish '2183ms elapsed  <<===
    'Populate the statement document.
    Write_Doc wdApp
    'Debug.Print "Post-Write Doc: " & Finish '4554ms
End If
Set wdApp = Nothing
Application.Cursor = xlDefault
On Error GoTo 0
Exit Sub
End Sub
Function Finish()
    Finish = timeGetTime() - lngStart
End Function

 

[Ken Puls]

I'm not clear why you can't just call your UpdateProgressBar routine after your Set wdApp line? It seems to me that it wouldn't fire until the app has been loaded. Or call it in the first line of your Word_Doc wdApp line, as the app will definitely have control by that point...

 

[Jess]

Hi Ken, Thanks for responding.
The timings are from the app running on my local computer, but it is actually located on our company server and run from a remote office on a direct line via Remote Desktop (Ouch!).
Depending on the line bandwidth the time timescale can become extended leaving the user with no visual indication that the procedures are progressing and this is what I was trying to address to sooth non-computer savvy users jittery nerves.
So my aim was to introduce a loop after the Set wdApp = CreateObject line, terminating when the Word application was detected as loaded, updating the progress bar as the loop progressed. If the Set wdApp = GetObject line returns returns an existing Word instance then the problem goes away.
I'm not 100% up on inter-application programming so any suggestions would be much appreciated.

 

[Ken Puls]

So there are two ways that code runs. Synchronous and Asynchronous.

Asynch is where one process kicks off another, then let's it run to completion without monitoring progress. To find out where it is, you'd need to write calls to check on the progress.
Synch is where things progress in order, with one thing happening once the previous one is finished.

While Windows processes are often Asynchronous, Office VBA programming is synchronous. So short answer is that you're kind of stuck with the way it is, unfortunately.

The reality here is that, even if you were able to update things in the middle, you still won't have any idea how long it will take, as that is dependant on things you don't have control over.

As a suggestion, could you make your progress bar say something like "please be patient as this could take up to 5 minutes to complete"? Or... "Coffee time!" or something?

 

[Jess]

Now I understand the stumbling block I came up against, wasn't totally aware of the Office vBA aspect you outlined.

As you suggest, time to touch-up the make-up, discuss the latest Club scene or whatever.

Thanks for your your assistance Ken, a great forum

Jess