extrememly slow macros in 365 on windows 10

[scottleger4]

It seems that nearly all of the macros I coded in the previous version of Excel have had some significant performance degradation since we migrated over to 365 and windows 10. Even simple code to just select, copy, and paste takes an inordinate amount of time to run. The exact same code runs on my "old" machine on the exact same build on excel. Here is an example macro...

Sub Clear_Yesterday_Data_64Bit()

Windows("Prep Sheet 64-bit.xlsm").Activate
Sheets("DD").Select

Dim Lsheet As Worksheet
Set Lsheet = ActiveWorkbook.Worksheets("DD")

Dim AirportLoc As Range
Dim Airport As String

Set AirportLoc = Lsheet.Range("A2")

Do Until AirportLoc.Value = ""
Airport = AirportLoc.Text
Sheets(Airport).Select

Range("V3:V24").Select
Application.CutCopyMode = False
Selection.ClearContents

Range("O3:O24").Select
Application.CutCopyMode = False
Selection.ClearContents

Range("M3:M24").Select
Application.CutCopyMode = False
Selection.ClearContents

Range("H30:H51").Select
Application.CutCopyMode = False
Selection.ClearContents

Range("K30:K51").Select
Application.CutCopyMode = False
Selection.ClearContents

Range("A59").Select

Set AirportLoc = AirportLoc.Offset(1, 0)
Loop

End Sub

Any ideas why the new "high performance" machine takes significantly longer to run the exact same macro on the exact same build of excel? Thanks in advance.

 

[Joe4]

What exactly is your code supposed to be doing?
It looks like you are looping through column A until you find a blank cell, but it looks like you are clearing the same ranges for every loop, which doesn't seem to make much sense to me.

A few things you can do to help speed up your code:

1. Turn off auto calc until the macro is done. You do that by adding a line like this at the beginning of your code:

Application.Calculation = xlCalculationManual

and then add a line like this at the end to turn auto calc back on:

Application.Calculation = xlCalculationAutomatic

2. Turn off screen dating until the macro is done.
You do that by adding a line like this at the beginning of your code:

Application.ScreenUpdating = False

and then add a line like this at the end to turn auto calc back on:

Application.ScreenUpdating = True

3. Remove as many "Select" or "Activate" statements as you can. The Macro Recorder is very literal, and records the selection of a range before doing something, but usually you do not actually need to select the range first before working on it.
So blocks of code like this:

[COLOR=#333333]Range("K30:K51").Select[/COLOR]
[COLOR=#333333]Selection.ClearContents[/COLOR]

can be simplified to this:

[COLOR=#333333]Range("K30:K51").[/COLOR][COLOR=#333333]ClearContents[/COLOR]

4. Eliminate Loops, if possible. Loops tend to be very slow and inefficient. Sometimes they are necesary, but sometimes they are not. If you can replace them with another method, it may help to speed up your code.

 

[scottleger4]

Thanks, I'll give those a try. Any explanation as to why I don't have any problems on my "old" PC where as the "new" high performance one on windows 10 seems to be so bogged down?

I'll report back after I tweak my code with the results.

 

[Joe4]

I don't understand in your code why you would be looping through the values in column A, but the ranges you are clearing are the same every time. So after your first run, they will already be blank, so why keep looping through and clearing ranges which are already blank? What am I missing here?

Any explanation as to why I don't have any problems on my "old" PC where as the "new" high performance one on windows 10 seems to be so bogged down?

I do not know, I am not much of a hardware or configuration guy, but you may want to make sure that you are using the correct version of Excel.
You may want to take a look at this: https://plumsolutions.com.au/32-bit-vs-64-bit-excel/

 

[scottleger4]

The first part...the loop. This particular workbook is a "dashboard" I created to compile a summary of a bunch of different reports together, with each market having their own individual worksheet. I reference the list of markets on one tab, the have the macro select the individual market's tab to clear out old data. The loop is to clear the same ranges, but all on different tabs/worksheets. The list I reference (item named in code as "Set AirportLoc = Lsheet.Range("A2")") is starts in A2 and just offsets 1 cell down after each loop until it gets to a blank cell (the end of the list) and is really just a control over how many times to run the loop.

The second part...the hardware/version. I have no control over that, our company has a third party that builds out the software and hardware packages. That's a whole other can of worms that has been a nightmare in and of itself.

If the solution is truly to go and eliminate ".select" and ".activate" as much as possible, it's going to take forever. I've written and recorded a TON over the last 3 years...never having much on an issue until they "upgraded" us. =(

 

[Joe4]

OK, I missed the part of it changing sheets. That makes more sense now.
You don't have any event procedure code (code that runs automatically on ranges being selected or changed), do you? That could be interfering with things and slowing things down.

If the solution is truly to go and eliminate ".select" and ".activate" as much as possible, it's going to take forever. I've written and recorded a TON over the last 3 years...never having much on an issue until they "upgraded" us. =(

Yes, unfortunately sometimes good performance can overshadow inefficient code. For maximum effect, if you just focused on the screen updating part, and removing the select from inside loops (where they are hit over and over), that may help.

Perhaps if you included the version of Excel and specs of the computer you used before, and then the same thing for what you are using now, someone with more experience regarding hardware and configuration will have some explanations.

 

[scottleger4]

Configuration for the "old" computer...the one that works just fine:
Windows 7 Enterprise SP1, Intel Core i7-6700 @ 3.4GHz, 16.0 GB RAM, 64-bit OS
Microsoft Office 365 ProPlus (16.0.9126.2351), 64-bit, version 1803

And the "new" high performance one that's really slow:
Windows 10 Enterprise, Intel Core i7-7700 @ 3.6GHz, 16.0GB RAM, 64-bit OS, x64-based processor
Microsoft Office 365 ProPlus (16.0.9126.2351), 64-bit, version 1803

I'll try to work with some other code, but my first impression after making the fixes you described don't seem to make any significant improvements.

Thanks again for the help so far.

 

[Joe4]

It looks like you are not the only person to see this.
Do a Google search on "is Windows 10 slower than Windows 7", and you will see a bunch of threads.
Take a look at some of them, and see if there is anything that applies to you that might help explain things.

 

[Bagnon]

Same issue for me and have not found a solution. Does anyone know if there is a setting in Windows 10 that can speed things up?

I do notice that hovering the cursor over a non-Excel window speeds it up, but not nearly as fast I got with Excel 2010.