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.

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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 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.

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]