VBA code for Calculation Full not working

[tiredandemotional]

I have a large workbook of 70 pages containing many arrays.

Only after enabling macros, does the workbook give #Value errors.

The workbook contains the following Switch to break iterative calculations:

=IF(Switch,AVERAGE(+CashMonthlyBf,+CashMonthlyCf),0)

The workbook opens with the following settings:
Calculations set to: Automatic except for data tables
Iterative calculations: Enabled
Maximum iterations: 100
Maximum change: 0.001
Switch set to FALSE

Only by pressing CTRL-ALT-F9 or Calculate Full from the Quick Access Toolbar with the Switch set to FALSE, are the #Value errors resolved.

Setting the Switch to TRUE enables the iterative calculation to work.

The only change that I can think of that may have caused the problem was the recent introduction of a significant number of SUMIFS.

I have tried to achieve the above programmatically (i.e. recalculate (CTRL-ALT-F9) and then set the Switch to TRUE) as follows:

                Application.CalculateFull
                Do While Application.CalculationState <> xlDone
                                DoEvents
                Loop
                Range("Switch") = True

However, this does not work.

I should be very grateful for any assistance in resolve this problem, including any thoughts about the use of arrays and/or SUMIFS in potentially causing it.

Thank you