Excel Macro Runs *PAINFULLY* Slow

%3CLINGO-SUB%20id%3D%22lingo-sub-2772538%22%20slang%3D%22en-US%22%3EExcel%20Macro%20Runs%20*PAINFULLY*%20Slow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2772538%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20an%20issue%20running%20an%20Excel%20Macro.%20I%20have%20read%20a%20lot%20of%20threads%20and%20tried%20all%20of%20the%20suggestions%20but%20no%20change%20in%20performance.%20I%20have%20run%20the%20same%20file%2Fmacro%20on%202%20machines%2C%20details%20below.%20No%20matter%20what%20change%20on%20Laptop%202%20(problematic%20machine)%2C%20iteration%20time%20was%20unchanged.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELaptop%201%3A%3CBR%20%2F%3EDell%20Precision%20M4800%3CBR%20%2F%3E2015%3CBR%20%2F%3EIntel%20i7-4800MQ%20%402.70GHz%2C%2032GB%2064-bit%3CBR%20%2F%3EWindows%2010%20Enterprise%3CBR%20%2F%3EVersion%201909%3CBR%20%2F%3EOS%2018363.1734%3CBR%20%2F%3EExcel%2016.0.13127.21734%2064-bit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELaptop%202%3A%3CBR%20%2F%3EMacbook%20Pro%2015%22%20mid-2020%3CBR%20%2F%3E2021%3CBR%20%2F%3EIntel%20i9-9980HK%20%402.4GHz%2C%2064GB%2064-bit%3CBR%20%2F%3EWindows%2010%20Home%3CBR%20%2F%3EVersion%2020H2%3CBR%20%2F%3EOS%2019042.1237%3CBR%20%2F%3EExcel%2016.0.14326.20384%2064-bit%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERunning%20the%20same%20file%2C%20same%20macro%20on%20both%20machines%3CBR%20%2F%3E1.5M%2B%20calculations%20per%20iteration%20(cell%20formulas)%2C%207%20output%20cells%3CBR%20%2F%3ELaptop%201%20%3D%20%3CSTRONG%3E11%20seconds%3C%2FSTRONG%3E%20per%20iteration%3B%20Outlook%2C%20multiple%20Excel%20files%20open%2C%20Teams%20chat%2C%20Teams%20meeting%2C%203%20sessions%20File%20Explorer%2C%202%20sessions%20Chrome%20(multiple%2Fmany%20tabs%20each)%2C%20has%20not%20rebooted%20in%20over%20a%20week%20(I%20know..%20hard%20to%20believe)%3B%20noticed%20slower%20operation%20of%20other%20programs%2C%20no%20change%20in%20performance%20of%20macro%20with%20other%20programs%20open%3CBR%20%2F%3ELaptop%202%20%3D%20%3CSTRONG%3E24%20seconds%3C%2FSTRONG%3E%20per%20iteration%3B%20no%20additional%20programs%20open%20or%20running%2C%20freezes%20system%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELaptop%202%3A%3CBR%20%2F%3ERebooted%3CBR%20%2F%3ERan%20in%20Normal%20view%20(was%20not%20in%20Page%20Layout)%3CBR%20%2F%3EDisabled%20hardware%20acceleration%3CBR%20%2F%3ERan%20with%20Wifi%20on%20and%20off%3CBR%20%2F%3ERan%20clean%20boot%3CBR%20%2F%3ERan%20diagnostic%20mode%3CBR%20%2F%3EDisabled%20all%20startup%20programs%3C%2FP%3E%3CP%3ERan%20repair%20on%2032-bit%20version%3CBR%20%2F%3EUninstalled%2032-bit%2Fre-installed%2064-bit%20Office%20365%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2772538%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773700%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%20Runs%20*PAINFULLY*%20Slow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773700%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1162918%22%20target%3D%22_blank%22%3E%40Hershner04%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20many%20approaches%20to%20make%20a%20VBA%20code%20faster.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsider%20inittially%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20YourRoutine()%0A%20%20%20%20BeforeYourCodeStarts%0A%20%20%20%20%0A%20%20%20%20'....%0A%20%20%20%20'....%0A%20%20%20%20%0A%20%20%20%20AfterYourCodeFinishes%0AEnd%20Sub%0A%0ASub%20BeforeYourCodeStarts()%0A%20%20%20%20'Turn%20off%20Screen%20Updating%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20'Turn%20off%20%E2%80%98Automatic%20Calculations%E2%80%99%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationManual%0A%20%20%20%20'Disable%20Events%0A%20%20%20%20Application.EnableEvents%20%3D%20False%0AEnd%20Sub%0A%0ASub%20AfterYourCodeFinishes()%0A%20%20%20%20'Turn%20on%20Screen%20Updating%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20'Turn%20on%20%E2%80%98Automatic%20Calculations%E2%80%99%0A%20%20%20%20Application.Calculation%20%3D%20xlCalculationAutomatic%0A%20%20%20%20'Enable%20Events%0A%20%20%20%20Application.EnableEvents%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20also%20recommend%20you%20read%20some%20%3CA%20href%3D%22https%3A%2F%2Fwww.soa.org%2Fnews-and-publications%2Fnewsletters%2Fcompact%2F2012%2Fjanuary%2Fcom-2012-iss42%2Fexcel-vba-speed-and-efficiency%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Earticles%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2773882%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%20Runs%20*PAINFULLY*%20Slow%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2773882%22%20slang%3D%22en-US%22%3EThank%20you%20for%20responding%20to%20my%20problem.%20The%20code%20is%20not%20the%20issue.%20Same%20file%2C%20same%20code%20on%20two%20different%20machines.%20Original%20machine%20has%20been%20consistently%20running%2011%20seconds%20per%20iteration.%20New%20machine%2C%20which%20by%20the%20specs%20is%20far%20superior%2C%20runs%2024%20seconds%20per%20iteration.%20Once%20I%20address%20the%20performance%20of%20the%20file%2C%20I%20might%20look%20to%20optimize%20the%20code%20based%20on%20my%20needs%2C%20but%20I%E2%80%99m%20not%20there%20quite%20yet.%3C%2FLINGO-BODY%3E
New Contributor

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 matter what change on Laptop 2 (problematic machine), iteration time was unchanged.

 

Laptop 1:
Dell Precision M4800
2015
Intel i7-4800MQ @2.70GHz, 32GB 64-bit
Windows 10 Enterprise
Version 1909
OS 18363.1734
Excel 16.0.13127.21734 64-bit

 

Laptop 2:
Macbook Pro 15" mid-2020
2021
Intel i9-9980HK @2.4GHz, 64GB 64-bit
Windows 10 Home
Version 20H2
OS 19042.1237
Excel 16.0.14326.20384 64-bit

 

Running the same file, same macro on both machines
1.5M+ calculations per iteration (cell formulas), 7 output cells
Laptop 1 = 11 seconds per iteration; Outlook, multiple Excel files open, Teams chat, Teams meeting, 3 sessions File Explorer, 2 sessions Chrome (multiple/many tabs each), has not rebooted in over a week (I know.. hard to believe); noticed slower operation of other programs, no change in performance of macro with other programs open
Laptop 2 = 24 seconds per iteration; no additional programs open or running, freezes system

 

Laptop 2:
Rebooted
Ran in Normal view (was not in Page Layout)
Disabled hardware acceleration
Ran with Wifi on and off
Ran clean boot
Ran diagnostic mode
Disabled all startup programs

Ran repair on 32-bit version
Uninstalled 32-bit/re-installed 64-bit Office 365

6 Replies

@Hershner04 

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 Sub

I also recommend you read some articles

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.

Thanks for your response! My older machine has always been a 64-bit OS and Office 365 packages with an iteration speed of 11 seconds. My new machine was a 64-bit OS and 32-bit Office 365 package, but I have since uninstalled the 32-bit package and installed the 64-bit version. In both situations, the iteration time was the same (24 seconds). Both Office 365 installations are 16.0.

I have not checked Active X installations but I will do that tonight.