Results 1 to 5 of 5

Thread: Update Userform Progress Bar

  1. #1
    Neophyte Jess's Avatar
    Join Date
    May 2011
    Location
    Huntingdon Cambridgeshire England
    Posts
    4
    Articles
    0

    Unhappy Update Userform Progress Bar



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Code:
    '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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Neophyte Jess's Avatar
    Join Date
    May 2011
    Location
    Huntingdon Cambridgeshire England
    Posts
    4
    Articles
    0
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,189
    Articles
    57
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Neophyte Jess's Avatar
    Join Date
    May 2011
    Location
    Huntingdon Cambridgeshire England
    Posts
    4
    Articles
    0
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •