How to set calculation mode to manual within Excel application scope when opening an excel file?

Help IT Automation
1

I have an excel which is full of formulae,
i noticed when i perform operations in excel scope like copy paste range and delete range its faster when the “Formula>Calculation Options>Manual” is selected.
image

I tried using send hot key, ALT + MXM, however its not reliable.

Any vba or faster way to switch between the modes?

was thinking to update the mode to manual first, update the processing and then update it back to automatic mode.

Any feedback or suggestions will be greatly appreciated.

Regards

2

Hi All,
Those who face similar issue, i found a solution as explained below:

use excel application scope
add activity “Invoke VBA code”
image

Save this code as => VBA.ManualCalculation.txt

        Public Function UpdateCalculation()

          Application.Calculation = xlCalculationManual
          Application.ScreenUpdating = False
            
        End Function

*Use the above code in excel application scope before processing, for eg: if you want to delete range or use copy paste range *

After the processing your logic, invoke VBA again as below:
image

Save this code as => VBA.AutomaticCalculation.txt

Public Function UpdateCalculation()
   
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True

End Function

Before using the Manual Calculation mode the overall processing took 10 min to update the excel, now its just 34 seconds. Give it a try…!!

If you face any issue invoking the VBA, refer to the settings here:

thanks to @Palaniyappan.

Best Regards.

1 Like
3

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.