Forum Discussion
Formula speed / Laptop specification
I have found that Microsoft broke the Worksheet calculation in 64bit versions later than 1902. Stay away from xlCalculationAutomatic in newer versions of Excel you have large spreadsheets or spreadsheets in which cells are periodically changed. You must turn off calculation (Application.Calculation = xlCalculationManual) an fire the calculation of sheet only when you need to otherwise risk the total crash of Excel. I have spent a considerable amount of time and headache, including a ticket with Microsoft until I finally found that Microsoft updates specifically 2012 and 2101 have been totally screwed up!
I have about 15 spreadsheet VBA applications running at customer facilities that poll and populate sheets with operational data through a Modbus communication active X (MBAXP), all running anywhere between versions 1708 to 1902, these applications running flawlessly for weeks on end no problem whatsoever. Then the last two applications I ran into a huge problem. The same application would hang as soon as it started, I couldn't select any cell it would run for about 10 minutes then the darn Excel would crash and restart with recovered versions of the same application, the recovered application having no difference. I originally thought it was a computer problem until I started experimenting with other versions Excel! I downgraded to earlier versions and found that all was good, polling selecting was normal. Then I experimented with the newer version, specifically 2012, thinking it could be the MBAXP. After replacing the MBAXP with a timer and a random number generated sheet populating macro I found that the hangup was the sheet calculation, timing the difference between 2012 (2101 was a little better) and the version 1902 at sometimes over 50 times as long in the new versions!! I had to limit the timer for - again - after about 10 minutes the darn Excel would totally crash and restart. No one at Microsoft (or anywhere else) seems to know why there is such a huge loss in calculation speed. I can only guess the programmers at Microsoft don't know what they are doing. While I finally solve the mystery, I am left to wonder whether Microsoft is intent on screwing up the product, leaving me to consider alternatives. Its quite a bummer. BTW the following link is a step by step instruction on downgrading to a working Excel version. Try v1902 it works. https://answers.microsoft.com/en-us/msoffice/forum/msoffice_install-mso_win10-mso_o365b/revert-to-a-previous-version-of-office-365/79138c95-80b4-48fb-bef7-08619af3593e
Thank you rpbenz. Noted about turning off automatic calculation, I will make more use of this than I am currently doing.
I also get a lot of 'Excel not responding' messages and am just left sitting and waiting, or putting a wash on, making a brew, doing a bit of deadheading in the garden (summer obviously).
I am not sure i'll be able to swap between excel versions as I am on my employers network which is very restricted but I have lots of tips to work through and if they don't speed things up for me I will certainly be asking the question of our IT department. I do find 2010 64 bit more problematic and not helpful in that I can't have multiple windows open.
Thank you for sharing, its all helpful and informative and I am grateful
Allison