Turn On Automatic Calculations

0
votes

I am using Excel 2013 and we are coming across random Excel files set to manual calculations and they do not seem to go away after resetting to automatic.

Those files seem to stay as automatic, but on a random day a different Excel file or the same Excel reverts back to manual. I want to auto execute a macro when loading any Excel file or just the program to set Excel to automatic calculations.

I tried the following macro:

Private Sub Auto_Open()
Application.Calculation = xlCalculationAutomatic

I received the following error message upon loading Excel:

"Run-time error '1004': Method 'Calculation' of object'_Application'failed

Troubleshoot: An Auto_Open macro runs before any other workbooks open. Therefore, if you record actions that you want Excel to perform on the default Book1 workbook or on a workbook that is loaded from the XLStart folder, the Auto_Open macro will fail when you restart Excel, because the macro runs before the default and startup workbooks open.

If you encounter these limitations, instead of recording an Auto_Open macro, you must create a VBA procedure for the Open event as described in the next section of this article.

Question: is there a way to create a macro to reset any Excel file back to Automatic? I stored the macro in my personal workbook as I am hoping that the macro will execute on any Excel file I load.

1
vbaexcel
If you turn on record macro and then do the steps to enable automatic calculations, it will make the command for you. It's this: Application.Calculation = xlAutomatic. - RLH
Hi. Yes, that is exactly what I did and received the above error message. - NPark
Your code isn't the same. xlCalculationAutomatic vs xlAutomatic - RLH

1 Answers

2
votes

I tried what you wrote and it works.

Just in case, here is my code :)

Private Sub auto_open()

    Application.Calculation = xlAutomatic

End Sub