Calculate on Status Bar causing VBA to run very slow

[Darren Elsom]

I have a workbook which has exceeded Excels limit of 65,536 dependencies.
Consequently I have the 'Calculate' message on the status bar. This does not
pose any problems. However, if the user executes some VBA code, the length
of time time taken to execute can be several minutes for code that can
normally executes in seconds.

Is there a way to force a recalc (F9 or alt+ctrl+F9) and then instruct VBA
to execute procedures without being slowed down by Excel?

Or have I interpreted incorrectly what Excel/VBA is doing?

Any help will be greatly appreciated.

Kind regards,

Darren.

 

[Jake Marx]

Hi Darren,

You could do something like this to see if it helps:

Sub demo()
Dim lCalcMode As Long

With Application
lCalcMode = .Calculation
.Calculation = xlCalculationManual
.Calculate
End With

'/ do your stuff here

With Application
.Calculate
.Calculation = lCalcMode
End With
End Sub


Lots more details on calculation available here:

http://www.decisionmodels.com/calcsecretsh.htm


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Darren Elsom]

Thanks for the help. The codes now executes in the normal length of time as
it did before I put in too many dependencies!!

One question.

When the application is told to calculate within VBA. Will it calculate all
open workbooks, or just the active one? As my code creates a new workbook
and tranfers info from the source to the new workbook.

Regards,

Darren.

Hi Darren,

You could do something like this to see if it helps:

Sub demo()
Dim lCalcMode As Long
With Application
lCalcMode = .Calculation
.Calculation = xlCalculationManual
.Calculate
End With
'/ do your stuff here
With Application
.Calculate
.Calculation = lCalcMode
End With
End Sub


Lots more details on calculation available here:

http://www.decisionmodels.com/calcsecretsh.htm


--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have a workbook which has exceeded Excels limit of 65,536
dependencies. Consequently I have the 'Calculate' message on the
status bar. This does not pose any problems. However, if the user
executes some VBA code, the length of time time taken to execute can
be several minutes for code that can normally executes in seconds.

Is there a way to force a recalc (F9 or alt+ctrl+F9) and then
instruct VBA to execute procedures without being slowed down by Excel?

Or have I interpreted incorrectly what Excel/VBA is doing?

Any help will be greatly appreciated.

Kind regards,

Darren.

 

[Jake Marx]

Hi Darren,

Thanks for the help. The codes now executes in the normal length of
time as it did before I put in too many dependencies!!
Great.

One question.

When the application is told to calculate within VBA. Will it
calculate all open workbooks, or just the active one? As my code
creates a new workbook and tranfers info from the source to the new
workbook.

If you use Application.Calculate, it should calculate all open workbooks.
You can also specify the workbook or worksheet to calculate. But IIRC,
there are some oddities to these methods - the link I gave you to Charles'
site goes into some of those I think.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]