Microsoft Office Forums - View Single Post - Comments sent to Excel status bar aren't shown after some time the code is running

ArviLaanemets · 10-13-2023, 02:17 AM

This post is about issue with my app described in post https://www.msofficeforums.com/excel...activated.html

In my app, I'm sending the info about how much files from total amount are processed [(current file number)/(total number of files)]. The issue is, that after some number of files processed, some long message (probably 50 characters or more) is displayed for very short time (so I don't know what the message was), and after that, the status bar remains empty for some time. Sometimes my messages start to be displayed again (for some short time), but generally the status bar remains empty until end. The total time of script running is about hour and 20 minutes, and totally about 900 files are processed by script (about 5 seconds per file).

The status is sent to status bar of app the script is running from at start of every cycle. After that, the Excel workbook to be read is opened in separate instance, and processed to read data. As last step of cycle, the workbook is closed without saving, the instance it was opened in is closed and set to Nothing, the app is activated, and then the next cycle is started.

The issue is, that most of time no info about process status is displayed in Excel status bar!

I tried to check this, but whenever I debug the script stepwise, all works OK.
When I tried to get Application.StatusBar to be displayed in Watches window, it was deleted from there when the code was running.
When I saved Application.StatusBar values to array, and created a stop point activated after some 50 cycles, the proper status bar values were all saved to array, but most of the weren't displayed.

The issue is really quite serious, as users may be confused, is the app working or not, and try to do something, what can affect the app in wrong way. So has anyone some idea about this?

The code is something like this:

Code:

Sub MySubName()
...
        Dim oInst As Object
        Dim booOldDisplayStatusBar As Boolean
...
        booOldDisplayStatusBar = Application.DisplayStatusBar
        Application.DisplayStatusBar = True
...
        For lngSourceNameRow = 1 To NumberOfSourceFiles
                Application.DisplayStatusBar = True
                Application.StatusBar = SomeTextString        
                strSourceFile = <full path for source file to be opened>
                Set oInst = GetObject(strSourceFile)
...
                The source file is processed
                GoTo ExitSourceNameRow
...
ExitSourceNameRow:
                ' Activate the converter file, and Close the source file
                Workbooks(strSourceFileName).Activate
                Workbooks(strSourceFileName).Close savechanges:=False
                oInst.Close
                Set oInst = Nothing
                Workbooks(strThisFile).Activate

        Next lngSourceNameRow
...
        Application.StatusBar = False
        Application.DisplayStatusBar = booOldDisplayStatusBar
End Sub