Milestone Message Boxes that timeout and show the progress through steps in a Excel Macro

[DRILL]

I have an Excel 2016 VBA macro for processing a large volume of data.
The macro can take up to 14 minutes to run due to the volume of data.
I have used the usual methods for speeding up the macro which means screen refresh is switched off.
So when the macro is running I do not know if all is going well or if the macro has entered and infinite loop somewhere.

So what I would like to do is to set points in the code where I would show a comment on the screen (for example - 'initial data sorting complete') and in that way I would know the macro is working properly through the various stages of the code.

Here comes the problem. I cannot find any method where the Message Box showing the comment does not time out as specified (I want the programme to continue unattended and not require any responses to any Milestone Message Boxes that appear). I have trawled the internet and tried many different permutations but nothing seems to work.

I would be grateful if anybody has any tried and successful method for doing this.

Many thanks,

DRILL

 

[Yongle]

Before doing anything else look again at your VBA
- it is likely that your macro could be speeded up significantly

eg
Are you writing values to cells individually? Multiple values can often be written together
Are you copying & pasting? Consider attributing values instead
Are you communicating unnecessarily with the workbook? Consider using arrays to be more efficient
Are you looping and using IF condition? Consider filtering to get conditional data instantly
Are you needlessly selecting ranges, sheets, workbooks? Selecting anything adds time and VBA rarely requires it
etc

If you want help, open a new thread and ask for help optimising your code, and post the whole of any relevant VBA
Use code tags to make it more readable
Click on # (hashtag icon) above post window and paste code between the tags
[ CODE ] - code goes here - [ /CODE ]

 

[DRILL]

Hi Yongle,
Many thanks for the quick response.
The macro I use has been developed over 10years and no doubt there are optimisations that can be made.
However at this time the macro is working 99% of the time with any problems normally being due to a change in the input data (I am taking +/-120pages of technical pdf reports and then reformatting the data into a tabular form that can then be further processed within the macro).
So right now I am happy to keep the macro as it is but would like to have the Milestone Message Box enhancement if it can be achieved.
Thanks,
DRILL

 

[Yongle]

Doing what you want will slow your code down further
Screen updating must be switched to TRUE to send anything to the screen and will update eveything when this happens

Instead of using a message box (which demands a response and disrupts the VBA), consider using the Status Bar
VBA below updates the Status Bar \ runs a dummy task \ Updates the status Bar \ runs a dummy task \... etc
Screen updating is briefly enabled to allow the Status Bar to be refreshed

Run the code and watch the status bar change

Code goes in a standard module

Sub UsingStatusBar()
    Dim job As String
    job = "Running ... "
    Call UpdateScreen(job)
    MyTask (job)
    
    job = "Importing ... "
    Call UpdateScreen(job)
    MyTask (job)
    
    job = "Exporting ... "
    Call UpdateScreen(job)
    MyTask (job)
    
    job = "Done ... "
    Call UpdateScreen(job)
End Sub

Private Sub UpdateScreen(aString As String)
    Application.StatusBar = aString
    With Application
        .ScreenUpdating = True
        .ScreenUpdating = False
    End With
End Sub

Private Sub MyTask(aString As String)
    Dim x As Long
    For x = 1 To 2000
        Debug.Print "Simulate " & aString
    Next
End Sub

 

[DRILL]

Many thanks again for your response.
I will try the code you suggested and let you know how it goes.
I was not sure if the Status Bar option is still available In Excel 2016?
Cheers. DRILL

 

[DRILL]

Hi Yongle,
I added your code to the base of my code and then made a call to the

UsingStatusBar subroutine half way through my code.
When the macro ran I did not see any Status Bar notification. The macro finished successfully.
Do I have to change my screen configuration to include a status bar before running the code?
Thanks for your help/advice...
​

 

[Yongle]

It may be something in your workbook causing this problem

Did you test my macro in a new workbook?
- close Excel down completely (to reset most things)
- open Excel again with blank new workbook
- test my macro in the new workbook


Status bar text should be visible unless it has been hidden. This code hides the status bar text:

Application.DisplayStatusBar = False

in which case this should reveal it again

Application.DisplayStatusBar = True

 

[DRILL]

That's cracked it.

I had the subroutine below to speed up the code and this included disabling the Status Bar (which I have now disabled).
Running with your code without this in my macro works perfectly!

Many thanks for your quick and engineered approach.

DRILL

Sub start_speed_vba_code()
screenUpdateState = Application.ScreenUpdating
statusBarState = Application.DisplayStatusBar
calcState = Application.Calculation
eventsState = Application.EnableEvents
displayPageBreakState = ActiveSheet.DisplayPageBreaks 'note this is a sheet-level setting
'turn off some Excel functionality so your code runs faster
Application.ScreenUpdating = False
'Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
End Sub

Milestone Message ​

 

[Yongle]

thanks for the feedback