Screen redraw question

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Have you tried Application.ScreenUpdating = False?

Just set it back to True at the end.

HTH,

Smitty
 

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
585
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Well-known Member
Joined
Aug 31, 2006
Messages
585

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

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

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

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

OK
Code:
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
 
L

Legacy 98055

Guest
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

Well-known Member
Joined
Aug 31, 2006
Messages
585
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
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top