How 2 ntermittently pause recalculation process & read the dispalyed statusbar text?

[agarwaldvk]

Hi Everybody

Is there a way to capture the text displayed on the Excel status bar using VBA? Further, is there a way to interrupt and then resume the recalculation process intermittently?

The exact requirement is as follows :-
If Excel is doing a recalculation - fairly intense recalculation which takes around 5 minutes to complete (I know this is very substantial but that is how it is) - initiated by invoking the appropriate command in VBA, is it possible to display in a message box intermittently to dsplay the extent to which the calcuation has been completed as is 10%, 20% etc. by capturing the text displayed on the status bar.

The reason for needing to do so is that the normal front end is not visible whilst this macro is running since a custom user form is displayed in full screen mode (the status bar is hence not visible to the user).

To realise this, I think (more than likely incorrectly) thatI will need to take the control away from the recalculation process intermittently to read the status bar text and display it in another user form - should I choose to display the progress this way and then resume the recalculation process.

Is that right? And if it is, how do I stop the recalculation process part way and how do I read the status bar text? I know how to display custom text on the status bar not how to read what is displayed.

For the first part, would DoEvents do it or am I on a totally wrong track?
For the second part, I have no idea and need your assistance please!



Best regards



Deepak Agarwal

[lucas]

There are probably several ways to help with this. The first question would be is it possible to close the userform before you start the operation. If that is not an option then you should search the knowledgebase for a progress bar that suits your need possibly. Click on kbar at the top of the page.

[agarwaldvk]

Dear Lucas

I will have a look at the knowledge base as you have pointed out to see what are the options available.

However, here is the answer your question.

If you are referring to the custom user form that is running in full screen mode, yes it can be closed.


But with this form also closed, the status bar doesn't seem to be show the calculation percentage completed - don't know the reason for this (possibly because the screenupdating is set to False). Hence, the search for potentially interrupting the recalculation process and programmatically read the status bar text.

Does that help you at all at providing me with a potential pointer?


Best regards


Deepak Agarwal

[tstav]

Hi agarwaldvk,
If you want to ignore whatever is showing in the StatusBar and produce your own progress status
then maybe this is more or less what you want.
You can design a separate userform with a labelbox, in which you show the
percentage of the calculations' progress.
This userform shows up right before the calculations begin (showing 0%) and
goes away when they end (showing 100%).
The userform is displayed vbmodeless so that it doesn't 'freeze' code execution.
Suppose the name of the labelbox is lblBox.
Sub Calc()
'code
ufmProgress.Show vbmodeless
curIteration = 0
totIteration = 1000 '<-- Supply this number
Do While (condition)
'Calculate the percentage of this iteration
curIteration = curIteration + 1
intProgress = CInt(curIteration / totIteration * 100)
'Show the percentage
ufmProgress.lblBox.Caption = intProgress & " %"
DoEvents
'calculations code
'
Loop
ufmProgress.Hide '<-- or, Unload ufmProgress
'code
End Sub

[asingh]

I had posted a similiar request a while back...wanted to know..is there any VBA object exposed, which shows the % calculation, and this can then be assigned to a variable..and used on a display...form..or status bar.....or a dynamic progress bar..????