vba excel macros become very slow after the latest update

Question

question

AmirBahrami-0136 asked Feb 6, '22 DusanDjurdjevic commented Mar 7, '22

vba excel macros become very slow after the latest update

Hi
I have a vba macro that opens an excel file, reads some data, does some calculations and closes that file and then opens another file from the list. I used this macro before the latest update with lists up to 50 files, each being of order of 10 MB. The speed was acceptable. After the lates office upgrade, this macro has become very slow, even with much shorter lists and smaller input files. Checking on task manager, I noticed that the memory taken by excel encreases with each file opening, but not released when the file is closed. It sums up to about 3.5 GB and stops there making it very slow. I tried a few options, disabled hardware graphic acceleration, ignored other applications that use Dynamic Data Exchange (DDE) and cleaned the .temp folder in my C drives. Nothing seemed to help. Please advise.

office-vba-dev

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ShawnG-3174 · Feb 16 at 06:58 PM

I am also experiencing the similar problem. The macro slows down with use or duration of the file being open. File size is17MB.
I have the usual speed enhancement of:

'Speed enhancements
Application.PrintCommunication = False
Application.DisplayStatusBar = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

The only workaround I have come with is to constantly restart excel before executing the macro.

Curious to hear the response.

1 ·

Aafra-7698 ShawnG-3174 · Feb 17 at 10:43 PM

I had exactly the same problems.
The latest update 14827.20198 seems to solve alle issues.
Thank you very much

1 ·

DusanDjurdjevic · Mar 07 at 01:21 PM

I have noticed major slowdown of my VBA Macros in the last few months and in some cases XL files are abruptly closed for longer running macros. Today I have found on https://techcommunity.microsoft.com/t5/excel/excel-macro-runs-painfully-slow/m-p/2772538/page/2 the actual solution that lead to speed improvement - adding the folder with excel file into Trusted Locations.

Once the folder is in trusted locations, Macros started to be executed in "fast mode".

Hope that this can be helpful to others.

0 ·

image.png (63.9 KiB)

Answer 1 · 2022-02-17T22:52:00.817Z

AmirBahrami-0136 answered Feb 17, '22 Aafra-7698 commented Feb 18, '22

Hi Aafra
Thanks for the suggested work around. However, in my case, the macro opens and reads a list of excel files so , I won't be able to close and restart excel everytime. This is a new problem with how excel handles RAM memory. Aparently, it won't release the part of memory that is not needed after a file is closed, or something like that!

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Aafra-7698 · Feb 18 at 11:05 AM

Hi AmirBahrami,
This problem has been reported twice (problem of SivanSivaprakash-256)
The suggested work around of restarting excel was mentioned there.
My problem was solved when update 16.0.14827.20198 was installed.
Hopefully it works for you too

0 ·

Answer 2 · 2022-02-20T00:17:20.663Z

AmirBahrami-0136 answered Feb 20, '22

Hi
I have just noticed that this was due to using worksheet functions in my code. As soon as I removed them, the RAM being occupied by data dissapeared.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question