Macros Run Slower in Newer Excel?

I'm using also version 1902 in Windows 7 and 2012 in Windows 10 (2101 since a few minutes ago) and all my Excel macros that export data to Word documents got about 6x slower in Win 10. This is a big problem because I often have projects that involve generating thousands of docs like this, today is the case.

By debugging the code I confirmed what was obvious from observing the execution, that the Range method Find.Execute was now very slow.
Since all my macros of previous projects that do this processing were affected in Win 10 / Excel and Word v2012, the problem had to be in Win 10 or Office, because the macros continue to work fine in Win7 / v1902.

Even if CPU usage by Excel and Word were low, as was overall CPU usage, I took one more look at the Task Manager and the only thing I could see were some very frequent spikes in Microsoft Defender (less than 20% of one CPU though). Turned off real-time protection and the performance went back to normal.
Go figure because no file is written while the find and replace is running, only at the end of replacing tens of tags in the documents but still each execution of Range.Find.Execute was very slow.

Well I found that the version 2012 is more than 100 times slower than version 1902 in processing a do loop in finding a string in an column of a sheet. It is so slow I am wondering if the Do Loop is to be avoided if not removed as it totally breaks and finally crashes the Excel program.

Thanks for this tip. Was running macro on 3 different machines. 2 of them took over 2 min to run and the 3rd took less than 10 seconds. I disabled the network on one of the slow machines and macro ran in less than 10 seconds. Tried adjusting excel settings but couldn't figure it out. Finally realized fast computer had a pdf printer as default vs the network printer. A google search led me to this article and putting Application.PrintCommunication = False in my macro fixed it. Also added Application.ScreenUpdating = False and gained a little more speed.

You have gone from a Windows XP / Excel 97 system to a Windows 7 / Excel 2010 system, with the same amount of memory (4 Gb) and comparable processors.

I'm no hardware expert, but it has been my experience that new operating systems and software generally require faster processors and more RAM. This may be contributing to some of the slowness.

I'm also stumped by wildly different performance for the same functions and files on different PCs. We have 4 that are all the same model, same configuration, and same software (built from a "golden image". One of the four took about 100 times longer to calculate a sheet with a few thousand functions in it. A second PC, that had been running the excel/vba process quickly also became about as slow as the worst PC when it was "upgraded" from Office 2010 to 2013. When a sheet is calculated with multiple cells calling the same VBA function, does the function "compile" multiple times, or does it compile once and run multiple times? What are the steps in the process where things could be affected by SSD read issues or slow sectors on a hard drive? What else am I missing?

Thanks for the ideas there. I'll try them out. A couple of comments...

Presumably to output to the status bar less frequently implies a progress calculator to decide when to output the message. Does this not exchange one bit of processing for another?

Mu macro runs sequential processing of a number of rows of data. There is no search involved. While there may be 500 rows to process, they are copied individually to a second workbook into a sheet which has only one record or row of data. Five values calculated in a second sheet are copied back to the original.

At the end of the day, my question is not so much about how to make the macro more efficient (although that always helps) but more about why the exact same code on the exact same files should run so much slower on the later version of Excel running on a later version of Windows.

This is becoming academic at this point as my colleague is so pleased at having reduced the processing time from a couple of days to a couple of minutes.

I will say this though. Another colleague has a particularly troublesome workbook that derives data using VLOOKUP and other interesting functions. On his PC (later s/w and o/s versions) it takes up to 30 minutes to open and recalculate. On mine... a few seconds. It definitely seems to me as if some system overhead is getting in the way.

Anyway, thanks for your contributions, everyone. They are much appreciated.

Dave

What I find to help speed up searches through large data lists is to set a range variable for the list and use that variable to do the searching. It's like puting the entire list into memory.

For example...

Dim MyDataList as Range

Set MyDataList = WorkSheetName.Range("Where_My_Data_Is")

This may help. I used it on a data list with over 2200 rows and over 30 columns of data, and I don't even notice it working.

It sounds like you don't want to get rid of the status bar, so perhaps you can update the status less frequently (at 10% intervals, for example).

Hi guys. Thanks for the tips. The structure of my code is as follows:

"Dim" statements for 2 integer counters and 3 worksheet variables;
turn screen updating off (already there, thanks Aldo);
assign source and target worksheets to variables;
count the number of rows to process using the following code:
iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row ;

within the loop of the program...
output the number of the current row being processed to the status bar (this is very useful for watching the speed and progress) with the code
Application.StatusBar = "Processing row " & i
directly assign values, for example:
wsTarget.Range("E6").Value = wsSource.Range("C" & i).Value
(17 cell values are assigned);
followed by 5 similar assignments to return the results to the Source sheet;
exit program loop;
select one cell and display a "processing complete" message;
clear the status bar;
turn on screen updating;

That's it! No rocket science there.
I'll try running the process on other PC's and see what happens.

Thanks for your help,
Dave.

Also try to avoid the "Select" command

One thing I do if the macro I create runs into some heavy work is to turn off Screen Updating in the macro...

Application.ScreenUpdating = False

This will suspending the worksheet screen from updating with data until you turn on screen updating again...

Application.ScreenUpdating = True