I have recently run into a problem where I need to switch to manual calc in
order to speed up the work I was doing. However, the act of switching from
automatic calc to manual calc both by-hand and within VBA causes my project
to stall for a good minute or two before continuing with whatever it was
doing. For comparison, doing a full calculation of my workbook would normally
take about 4 seconds.

I am simply executing this line of code:
Application.Calculation = xlManual


My question is what exactly is happening when I "switch" from auto to manual
calculation that may be causing this stall?

Pertinent information:
- I currently have references from this workbook to 62 other workbooks.
- Many of these references are array formulas (but remember that a normal
full calculation would only take about 4 seconds)

After further examination of my program I discovered that the problem lies
within rebuilding dependencies of one of my worksheets.

Specifically, I have a VBA subroutine that fills this worksheet with array
formulas, which reference my 62 database files. Without rebuilding the
dependencies of these thousands of array formulas before switching to manual
calculation, Application.Calculation = xlManual would cause Excel to stall,
presumedly doing its own dependency check (albiet inefficiently).

The fix to this problem was to call the method:

Application.CalculateFullRebuild

after I had created my new array formulas, thereby saving Calculate =
xlManual the trouble of fixing missing dependencies.

"Tokenekie" wrote:

I have recently run into a problem where I need to switch to manual calc in
order to speed up the work I was doing. However, the act of switching from
automatic calc to manual calc both by-hand and within VBA causes my project
to stall for a good minute or two before continuing with whatever it was
doing. For comparison, doing a full calculation of my workbook would normally
take about 4 seconds.

I am simply executing this line of code:
Application.Calculation = xlManual


My question is what exactly is happening when I "switch" from auto to manual
calculation that may be causing this stall?

Pertinent information:
- I currently have references from this workbook to 62 other workbooks.
- Many of these references are array formulas (but remember that a normal
full calculation would only take about 4 seconds)