Macro, while Running, Stops Excel from Responding

Thanks buddy .. This "DoEvents" really helped me a lot :)

Hi Allen,
True, I was able 'trigger' it to continue by adding DoEvents before the next loop
This removed the "Not Responding" which is Window's way of telling you I don't see anything happening, a nuisance but... my solution helped.

I face exactly the same problem in excel 2016 with windows 10, while with excel 2007 and windows XP no problem

This is what I hate most about migrating from XP to 7: VBA hanging only happens in the latter, not the former. No matter which version of Excel, a lot of iterations in a For/do while = freeze.

DoEvents is the way to go, but unfortunately it adds A LOT of execution time. I generally tend to execute it in 1% of my iterations (through some sort of IF implementation).

If only this didn't happen, I would be more proficient at Project Euler...

Tim
23/10 = 2.3 but 2310 returns an error.

Sub t()
Dim i As Double
i = 23 / 10
MsgBox i

i = 23 10
MsgBox i
End Sub

Works for me

My thanks to all of you who contributed to the solution to my question.

The status bar update was not limited to large files. I had a process where the update stopped at about 220 of 4000 rows. I assume that the coordinate geometry processing being done by that particular macro became a little too much!

However, the addition of the "DoEvents" line of code appears to have cleared things up nicely, with no apparent deterioration in the time taken to run the process. That is one to be remembered!

Again, my thanks to all.
Dave

You can use the INT function for the integer part of the result: =INT(23/10) resolves to 2.

Tim:

The "" operator is apparently only recognized in VBA. If you open the VBA editor and enter "? 2310" into the immediate window, you will get the answer of 2 returned.

Peter, 23/10 = 2.3 but 2310 returns an error.

One point not addressed in the article is integer division. Say you want to show the update after each 10% is processed. You would use NumberofRows10 not NumberofRows/10. Say N was a prime number 23 then we would want it to update after processing every 2 rows 23/2 produces 2.3 while 232 returns 2. If we give this value the name lMult we can use the phrase:
If i Mod lMult = 0 Then
Application.StatusBar = msg & i
End If

Also expect a decrease in speed. I've seen an increase in time taken from 25 for a large N Value to 60 for a smaller value.

Nive tip, but it doesn't always work, for some reason the fact that it's 'backgroud' processing makes it display the 'not reponding' messag while it's working perfectly.