Forum Discussion
Excel Macro Runs *PAINFULLY* Slow
I have an issue running an Excel Macro. I have read a lot of threads and tried all of the suggestions but no change in performance. I have run the same file/macro on 2 machines, details below. No mat...
There are many approaches to make a VBA code faster.
Consider inittially
Sub YourRoutine()
BeforeYourCodeStarts
'....
'....
AfterYourCodeFinishes
End Sub
Sub BeforeYourCodeStarts()
'Turn off Screen Updating
Application.ScreenUpdating = False
'Turn off ‘Automatic Calculations’
Application.Calculation = xlCalculationManual
'Disable Events
Application.EnableEvents = False
End Sub
Sub AfterYourCodeFinishes()
'Turn on Screen Updating
Application.ScreenUpdating = True
'Turn on ‘Automatic Calculations’
Application.Calculation = xlCalculationAutomatic
'Enable Events
Application.EnableEvents = True
End SubI also recommend you read some https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/
Thank you for responding to my problem. The code is not the issue. Same file, same code on two different machines. Original machine has been consistently running 11 seconds per iteration. New machine, which by the specs is far superior, runs 24 seconds per iteration. Once I address the performance of the file, I might look to optimize the code based on my needs, but I’m not there quite yet.
- Well, optmization means take into consideration the code as well.
Refactoring a code its painfull sometimes, but its important reassess the approach and variables, etc. to achieve the same result considering different environments.
Also consider the machine settings (Memory, processor, OS versions, etc.)
I had problems in the past where my codes was running very slow, but after refactoring the code it was improved a lot.
As I told you, it depends of a lot of things consider review the code is a part of improvement too.- Thank you for your consideration. I agree bad code can run poorly, but that is not the issue causing me pain.
My issue is that *the same* code and *the same* macro using *the same* dataset running on 2 different machines has disparate results. One is a 6 year old machine with half the cores/threads/RAM and an HHD, the other machine was assembled/built a little over 4 months ago and has twice as many cores/threads/RAM with an SSD. By all accounts it should run much faster but it is instead running much slower.
I know that everyone says it’s not their code, and I’m not that guy. I fully acknowledge the code is not optimized, but on my old machine it runs acceptably. On my new machine, the performance is unacceptable. Before I run down the rabbit hole of code optimization, I want to get to the bottom of the hardware/software issue that is causing my new machine to run at essentially half the speed.
As for code optimization, when I originally wrote the code 6 months ago, I did try several changes including turning off auto calculations and only running them as needed, turning off screen refresh, etc, etc.. and had very strange results there, too. My iteration time actually slowed down, so I reverted the change that made a difference (screen refresh). I have not run through the same exercise on the new machine, but I’m hesitant to do so until I address the performance issue.- 32 bits or 64 Bits either for Windows and Office?
VBA codes from an older Excel version or other third‐party Active Xs, you could encounter all sorts of problems. This is because many Excel add‐ons are 32‐bit versions that are not fully compatible with the 64‐bit Excel.