Prevent excel from freezing while running for/next code

[JasperD]

Hi guys,

I've got a code that goes down a column with numbers, looks up a value on a website (through MSXML2.XMLHTTP method (thanks Kyle!)) and paste the result in a cell three columns next to the number.
It's about 200 ~ 300 numbers (variable) and since every lookup takes about half a second, people get impatient if they have to wait.
To "prevent" this, I do the following .

For Each cell In Range("C7:C" & Range("C65536").End(xlUp).Row)
If IsNumeric(cell.Value) = False Then GoTo nextart
cell.Offset(0, 3).Activate
cell.Offset(0, 3).Font.Bold = True
Application.ScreenUpdating = False

.... running webupdate code

Application.ScreenUpdating = True
cell.Offset(0, 3).Font.Bold = False
nextart:
Next cell

Now this works fine, it activates the cell and bolds the text to show the user which cell is being updated.
This however, only works for the first 10 rows it's doing. Then excel freezes and shows "NOT RESPONDING" in the top bar.
This stays on screen until the whole column with numbers is done and then excel recomposes itself, having the last result activated and the macro done.

How can I prevent excel from freezing at this point?
Any suggestion would be more than welcome :-)
Thanks so much!

[ragulduy]

What about using something like:
Application.StatusBar = X & "% Done"
To keep the user updated? Where X represents the current progress as a percentage?

[TMS]

You don't need to activate the cell to make it bold, so take the cell.offset.activate line out.

Not sure it will help but it won't make it worse.


Regards, TMS

[JasperD]

yudlugar : I tried to do that, but doesn't help - freezes at same moment...

TMS : I activate it so the screen will follow the cells after the row it goes "off screen", otherwise the cell will be made bold, but if it's off screen, the user won't see it

[Kyle123]

Couldn't you do that with conditional formatting?

[JasperD]

Perhaps that would be possible - however, it's not about the highlight, it's about "how can I prevent excel from freezing while running code " :-)

[ragulduy]

Does it still freeze and show not responding if you take out the stuff for making the cells activate/bold?

[TMS]

Do you have any other code like Selection or Change event handlers?

You could maybe hide all the columns to the right of column C at the beginning and unhide them at the end. Just trying to avoid unnecessary cell selection.

Regards, TMS

[Kyle123]

Have you tried turning screen updating on?

I suspect that since you are running a web query a few hundred times, this will be the bottle neck and you don't update the screen while this is happening. You could also make the call async with a doEvents loop if it isn't already. Just bear in mind that all this will take even longer (and your're already doing things in the slowest possible way) and your users will still not be able to use their machines as clicking in the wrong place will royally screw things up.

[Marcol]

Maybe this, but I suspect it is the web call that is using the resources.

    Application.ScreenUpdating = False
    For Each Cell In Range("C7:C" & Range("C" & Rows.Count).End(xlUp).Row)
        If IsNumeric(Cell.Value) Then
            Cell.Offset(0, 3).Font.Bold = True
            '.... running webupdate code
        Else
            Cell.Offset(0, 3).Font.Bold = False
        End If
    Next Cell
    Application.ScreenUpdating = True

[TMS]

@Marcol: if you have the font bolding/unbolding inside the ScreenUpdating False/True, you're not going to see it.

Regards, TMS

[Marcol]

@ TMS
Why would you want to see it at every step ...
Turn it red and bold if it needs to be clearer.

See the attached

[JasperD]

Hi Marcol,

I want to show it at every step so the user sees which row / value is being processed.
But to be honest, it's not necessary to show anything, as long as EXCEL won't "freeze".
Turning off screenupdating / events / calculation etc all doesn't do the trick, so I've just come to accept the fact that when running this extensive code, excel will just be hanging around for a couple of minutes.
Probably due to the lame computers we have at work here...

[TMS]

when running this extensive code

Perhaps that's what you need to share to see if anyone ... Kyle, maybe ... can make it faster.

Turning off screenupdating / events / calculation etc all doesn't do the trick

Do you have event handlers?

Regards, TMS

[TMS]

@Marcol: First post ...

it activates the cell and bolds the text to show the user which cell is being updated.

Regards, TMS

[TMS]

@Marcol: OT (sorry) ... your PM store is full.