Forum Discussion

Hershner04's avatar
Hershner04
Copper Contributor
Sep 22, 2021

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 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

  • Butch716's avatar
    Butch716
    Copper Contributor

    Hershner04 

    I found a clumsy work around the slow macro problem. 

    First it appears that my macro runs like a champ on the first time, however if I try to run it again, it runs very slow. So what I did was created a small macro that opens my original macro and it runs beautifully.  then I added some code that actually saved and closed my real macro. Thereby throwing control back to the calling macro. When I touch the start button on the main macro, it re initializes everything and once again runs beautifully. Hope this helps. 

  • CliveW85's avatar
    CliveW85
    Copper Contributor

    I have been experiencing the same issue with 2 machines which have been updated recently. However I think I may have found a fix, it certainly worked for me, making the macros (simple file read, extract and write to table tasks) run just like before.
    I opened XL, went to the Developer tab ... Macro Security... Trusted Locations.
    I then added the directory of the XL file with the macro.
    Closed XL and then opened the file with the macro and it ran as fast as it used to "back in the day" 🙂
    Hope that helps someone.

    • Mabandl1's avatar
      Mabandl1
      Copper Contributor
      What if the files are stored on SharePoint?
      • BeenCodingVBATooLong's avatar
        BeenCodingVBATooLong
        Copper Contributor

        Mabandl1 

         

        Once I added the the file locations as Trusted Locations, I saw a large improvement.  I did not see any changes moving them to SharePoint. 

         

        Sometimes they still run incredibly slow, and I don't know what the issue is.  However, my monster programs have hit a level of maturity where I finally feel comfortable shutting off screen updating and that helps quite a bit as well. 

    • Vik162004's avatar
      Vik162004
      Copper Contributor
      I had the intuition that is something about the macros security, but I didn't have the idea to include them in the Trusted Location, that's a brilliant solution. You really saved me CliveW85, great idea. Thanks a lot for sharing!
      • Steve_Pigsoft's avatar
        Steve_Pigsoft
        Copper Contributor

        Brilliant! Thanks very much for this.

        As you suggested - Add the directories of the Excel file(s) with the macro(s) within to Excel's Trusted Locations, and the macros then run at a good speed once more, AND exiting from Excel after running the macros is restored to being swift, too.

         

        Presumably, before recent updates of Microsoft 365, listing Trusted Locations was not an essential requirement for running Excel Macros at a reasonable speed, and now it is -

         

        Case closed for me. Thank you CliveW85 for this fix.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    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. 

    • Adriaan1's avatar
      Adriaan1
      Copper Contributor

      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...

      • BeenCodingVBATooLong's avatar
        BeenCodingVBATooLong
        Copper Contributor
        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.
  • 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

    • Hershner04's avatar
      Hershner04
      Copper Contributor
      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.
      • Juliano-Petrukio's avatar
        Juliano-Petrukio
        Bronze Contributor
        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.

Resources