Sep 21 2021 09:39 PM
Sep 21 2021 09:39 PM
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.
Dell Precision M4800
Intel i7-4800MQ @2.70GHz, 32GB 64-bit
Windows 10 Enterprise
Excel 16.0.13127.21734 64-bit
Macbook Pro 15" mid-2020
Intel i9-9980HK @2.4GHz, 64GB 64-bit
Windows 10 Home
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
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
Sep 22 2021 04:52 AM
There are many approaches to make a VBA code faster.
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
Sep 22 2021 05:56 AM
Sep 22 2021 06:11 AM
Sep 22 2021 07:39 AM
Sep 22 2021 09:10 AM
Sep 22 2021 10:06 AM
Oct 29 2021 12:30 PM
Oct 29 2021 02:18 PM
Nov 02 2021 07:55 PM
@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.
Nov 03 2021 03:07 AM
@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.
Nov 03 2021 04:21 AM
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...
Nov 05 2021 08:05 AM
Nov 05 2021 08:45 AM
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.
Nov 06 2021 01:20 PM
Feb 09 2022 05:17 AM - edited Feb 09 2022 12:21 PM
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.
Feb 09 2022 06:21 AM
Feb 09 2022 02:30 PM
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