none
Access VBA unexpected 15 second code delay when leaving function RRS feed

  • Question

  • I have a series of macros that open Word populate a document, then open Excel to create 3 charts and paste them into the open document. All of that is taking between 12 to 15 seconds. the problem I'm having is when the code continues form the function that opens the document to the other macros. I've added timers to track where the delay is. The delay is leaving the Word macro and returning the calling macro. You can see the delay in writing the time from word to the completion of the code. The timers are in the last lines of code before closing.

    A quick flow chart of how the macro is laid out. There is other logic

    Test Sub -> Word Function -> Excel Function -> Chart1-3 -> Excel Function -> Word Function -> (delay here) Test Sub

    Below is a code sample where the delay is happening ECOS/ECOE are my timers.

        ObjDoc.SaveAs2 ReportName, 17
    ClearObjects:
        ObjDoc.Close savechanges:=wdDoNotSaveChanges
    '    ObjDoc.Application.Quit
        ObjApp.Application.Quit wdDoNotSaveChanges
        Debug.Print "Build Gap"
        ECOE = Round(Timer - ECOS, 2)
        Debug.Print "Word Build: " & ECOE
        Exit Function

    Timer results. Word build include the time for opening and closing Word/Excel and copying and pasting the charts over. The overall timer is in the test sub . The exit function line of code is the only line between the overall timer and the Word build timer.

    Word Build: 12.57
    Total time: 27.4

    Thursday, May 9, 2019 5:44 PM