Screen redraw question

[Mr930]

I have a bunch of cells that call some lengthy VBA code. If I turn off the redraw in VBA, it comes back on when it leaves VBA, which does not do me any good since it repeats for the next cell. My question is, what is the best way to turn off redraw for the whole duration of recalc?

thanks
Fred

 

[Smitty]

Have you tried Application.ScreenUpdating = False?

Just set it back to True at the end.

HTH,

Smitty

 

[Mr930]

I do that but it turns it off and on in the VBA, but since it does the same VBA for each cell, it redraws after it leaves the VBA code, then moves on to the next cell. I need to be able to turn it off, do a recalc (all cells recalced) then turn it back on.

thanks
Fred

 

[Smitty]

I guess I'm missing what you're after then.

Can you post some of the code so we have a better idea of what you're doing?

Smitty

 

[Mr930]

The specific code is not really the issue, but here is what it does:

I have 22,500 cells that call the same VBA function-it returns a single number to the calling cell. The VBA code takes some time to execute, does a few big loops.

If I turn off screen updating at the start of the function and on at the end, it does not help me, because the cell is updated after the VBA function returns.

So I need to be able to turn off redraw, have the sheet recalc, then turn it back on. Is this possible?

thanks
Fred

 

[erik.van.geit]

Hi,

seems like you are turning on and off screendupdating at the wrong place
would this be your problem ?

WRONG

Sub main()
    For i = 1 to 10
    Call othersub
    next i
End Sub

Sub othersub()
Application.ScreenUpdating = False
...
Application.ScreenUpdating  = True
End Sub

OK

Sub main()
Application.ScreenUpdating  = False
    For i = 1 to 10
    Call othersub
    next i
Application.ScreenUpdating  = True
End Sub

Sub othersub()
...
End Sub

kind regards,
Erik

 

[Legacy 98055]

I think the OP is speaking of a UDF. Application screen updating automatically returns to true then the UDF is exited. This procedure is being called from each cell being calculated so screenupdating is useless in this case. It is actually providing overhead. You might think that you could utilize the sheet calculate event, but, unfortunately this event does not fire until after all UDF's have been executed. Go figure? Anyway, you could use an API function as a workaround.

Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long

Download the example to test. See sheet1 and sheet2. Drag the functions down to copy from A2 and B2 on down to several thousand or more cells. Unless there are many, many function calls, this method may not really make much of a difference at all. It cut my time by about 20% for 140,00 function calls. Sheet1 calls UDF Function1 which utilizes app.screenupdating. Sheet2 uses the API function.

SpeedUpCalc.zip

 

[Mr930]

Mine was something like that. I since moved all my code into VBA and at the end paste the results back into the sheet. It saved some time but not as much as I hoped.

thanks
Fred