Calculate on Status Bar causing VBA to run very slow
 

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.

Calculate on Status Bar causing VBA to run very slow
 

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 he

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 wrote:
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.

Calculate on Status Bar causing VBA to run very slow
 

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.


"Jake Marx" wrote in message
...
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 he

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 wrote:
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.

Calculate on Status Bar causing VBA to run very slow
 

Hi Darren,

Darren Elsom wrote:
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]