Excel Macro Runs *PAINFULLY* Slow

Copper 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

33 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.
you are right Macros do run slower in general. usually you cant see what the Macro is doing. Now it seems you can pretty much watch it go play by play.
So far, everyone has missed the point. The. Same. Macro.. running the. Same. Data.. on the. Same. OS. BUILD.. (i.e. Windows and Office 64-bit) runnings much, much, *MUCH* slower on the newer machine that is far superior in all regards.

I’m not looking for advice on how to run the macro more efficiently or alternative methods to macros, I’m looking for help to speed up my machine the should have run far faster out of the box.

@Hershner04You are not alone.  I have the same situation and I am fed up with Microsoft making excuses for things like this.  The macro I am having the problem with has nothing but simple copy statements between cells on one sheet and cells on another, along with some formatting.  With Excel 365 is is managing to copy around 1 cell per second.  Can somebody please explain how that can possibly be bad code on my part.

@Hershner04 You mention that you run W10 on a Mac. That means you are running a virtual machine, either via Bootcamp or Parallels. Correct? Did you look at memory allocation to the virtual Windows machine? Am not a VBA person myself and know little about coding, but am running W10 on an almost 10 years old Macbook Pro (via Parallels)  with far inferior specs than yours and notice no severe performance issues when comparing to more modern PCs/Laptops. Just a thought. 

I fully agree.  The code is not the problem.  I have code that I run 10 years ago on the Excel and hardware of the time.  It executed in 20s.  The same code on 2021 hardware and excel now takes 8 minutes to run.

 

This is a one sides Excel problem with their newer versions.  I would really like to know why?  The hardware is much better but the performance is 24 times worse...

I have the same issue. Simple code that is at least 15 years old.

It just started taking forever to run.

If I wasn't so completely trusting in the motives of the product managers, I might think it was to try to force us out of VBA like they tried to before.

I might agree that MS is trying to get us off VBA.  After all VB6 hasn't been supported for many, many years despite that fact that there are massive parts of the world that still use it.

 

I am about to revert to Office 2007 as this excel problem is just one of the many things I don't like about Office 365. 

Just to add to this issue, I have just run a macro which was taking around 1 second to populate each cell and suddenly it started working as it did with Excel 2007. That is overall excepting that every so often it would slow down for a cell or two and then pick up speed again.

So for anyone who has suggested that the problem is with the way the code is written I would suggest that this evidence suggests that the code is not at fault but something in the VBA handling and/or interaction with Windows 10 is causing the problem. If only we could persuade Microsoft that this is the case and get them to fix the underlying problem. Of course, as has been suggested, this may be a deliberate ply to get us to stop using VBA. If so, what are MS offering as an alternative.

@px03afkAny update on this ? I have the same problem since, I believe, a "windows intelligence" update from February 3rd. Macros are much slower (X 100). Seems to be linked with updating cells.

No update. As I said, the code was old. I tarted it up some. It is a little better because of that.

 

Edit: Not much better.  It is still terrible.

I am having the same issue with the macros in my organization, they are too slow.

@Prash Shirolkar 

@CatPidge 

I have the same problem: Current Excel for Microsoft 365 (Win 10) is now slow to run and slow to close after running macros from an excel file. These same files and macros still run fine on Excel 2003 and Excel 2007 on alternative Win 10 PCs, and the same macros ran fine on the now-slow PC UNTIL recent updates of Microsoft 365 / Excel 365 on that PC.

I went back to a reasonable speed by doing all the computation within an array of variant and putting back the array in the sheet (this from MrExcel). It is not the answer we are looking for as it requires a rewrite of the code, but when you are desperate ... (In my case the slowness was caused by a series of update of cells operations). Maybe this can help

 

Sub Test1()
    Dim myArray  As Variant
    Dim i        As Long

    Application.ScreenUpdating = False
    With ThisWorkbook.Worksheets("Sheet1")
        myArray = .Range("A1").Resize(10, 10)
    
        For i = 1 To UBound(myArray)
            myArray(i, 4) = 111
        Next
    
       .Range("A1").Resize(UBound(myArray, 1), UBound(myArray, 2)) = myArray
    End With

End Sub

@Steve_Pigsoft