Excel 365 Macros running slow

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

Since yesterday, I've noticed a number of macros running slower than before. I was using Excel 2013 and about a month ago upgraded to Office.365

I have a range A1:AK39 where

A1 = Table name
A2:A39 Months of the year, starting Mar 2019, ending Mar 2022
A2:AK2 Months of the year, starting Mar 2019, ending Feb 2022
B3:AK39 values

The following code to normalise it into 3 columns took 15 seconds to run, which seems slow for the size of data (39 rows by 37 columns)
Rich (BB code):
 Sub Generate_Normalise()    
    Dim r   As Range: Set r = Range("GRT_1")
    Dim x   As Long
    Dim y   As Long
    Dim i   As Long: i = 2
    Dim a   As Variant: a = r.Offset(1).Resize(r.Rows.Count - 1).Value
    Dim s   As Variant: s = Timer
    
    Application.ScreenUpdating = False


    With KPI_N
        .Cells.Value = ""
        .Cells(1, 1).Resize(, 3).Value = Array(r.Cells(1, 1).Value & "_Row_Month", r.Cells(1, 1).Value & "_Column_Month", "Value")
        For x = LBound(a, 1) + 1 To UBound(a, 1)
            For y = LBound(a, 2) + 1 To UBound(a, 2)
                If Len(a(x, y)) > 0 And x - 2 <> y Then
                    .Cells(i, 1).Resize(, 3).Value = Array(a(x, 1), a(1, y), a(x, y))
                    i = i + 1
                End If
            Next y
        Next x
    End With


    Application.ScreenUpdating = True
    Cells(1, 5).Value = Format((Timer - s) / 86400, "hh:mm:ss")
    
    Set r = Nothing: Erase a
    
End Sub
Part in green is only if a value exists in the range and part in blue is to ignore a formula cell.

A few ways to speed up, including pre sizing an output array, but, overall, I would expect above to run sub 5 seconds. I've had more complicated code in the past run faster than this.

Any similar experiences or suggestions?

TIA,
Jack
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Additionally, file is saved to desktop, opened in app and autosave is disabled.

Ran twice just now with run-times: 50 seconds, 1min 5 seconds

Output is 41 rows x 3 columns, not a large range
 
Upvote 0
Additionally, file is saved to desktop, opened in app and autosave is disabled.

Ran twice just now with run-times: 50 seconds, 1min 5 seconds

Output is 41 rows x 3 columns, not a large range
Try this
Code:
Sub Generate_Normalise()
    Dim r   As Range: Set r = Range("GRT_1")
    Dim x   As Long
    Dim y   As Long
    Dim i   As Long: i = 2
    Dim a   As Variant: a = r.Offset(1).Resize(r.Rows.Count - 1).Value
    Dim s   As Double: s = Timer
    Dim TC As New Collection
    Dim Final_Array As Variant
    
    Application.ScreenUpdating = False


    With KPI_N
        .Cells.Value = ""
        .Cells(1, 1).Resize(, 3).Value = Array(r.Cells(1, 1).Value & "_Row_Month", r.Cells(1, 1).Value & "_Column_Month", "Value")
        For x = LBound(a, 1) + 1 To UBound(a, 1)
            For y = LBound(a, 2) + 1 To UBound(a, 2)
                If Len(a(x, y)) > 0 And x - 2 <> y Then
                    TC.Add Array(a(x, 1), a(1, y), a(x, y)) 'Add array to collection
                End If
            Next y
        Next x
    End With


With TC
    
    If .Count > 0 Then 'if collection has items in it
     
        ReDim Final_Array(1 To .Count, 1 To 3) 'redim array to hold all items
        
        For x = 1 To .Count
            For y = 1 To 3
                Final_Array(x, y) = TC(x)(y - 1) 'loop through collection and add values to array
            Next y
        Next x
        
        KPI_N.Cells(2, 1).Resize(.Count, 3).Value2 = Final_Array 'apply to range
                                                                   'Original macro starts at row 2
    End If


End With


    Application.ScreenUpdating = True
    Cells(1, 5).Value = Format((Timer - s) / 86400, "hh:mm:ss")
    
    Set r = Nothing: Erase a
    
End Sub
 
Last edited:
Upvote 0
Thank you for posting, it's not code optimisation though, it's the same code I had last week that ran much much faster.

Now it's running very slowly and unclear why. Right now code is still running over every single range for GRT_* (followed by 4 other 3 letter codes), for about past 2 hours.

Even if I was using .Select and all the other slowness stuff in code, on an older Excel version, it would run faster.
 
Upvote 0
A few suggestions.
Have you checked how large the GRT_1 range is, to make sure it hasn't somehow become 1000s of rows?
Check you don't have any event code running on that sheet.
Add a few Debug.Print lines to output the time taken to do various parts of the code, to see which part is causing the most delay.
 
Upvote 0
The GRT_1 is a named range and always 37R x 39C and there are about 40 in total, however, I'll try the debug.print suggestion, thank you.

I just paused it and it's been running for about 3 hours 20mins, value for i is 6468 and another 30 tables left to iterate through.
 
Upvote 0
The GRT_1 is a named range and always 37R x 39C and there are about 40 in total, however, I'll try the debug.print suggestion, thank you.

I just paused it and it's been running for about 3 hours 20mins, value for i is 6468 and another 30 tables left to iterate through.

Have you tried checking for updates to excel?

Also since "i" will eventually be greater than 6000 , you are essentially writing back to the worksheet 6k+ times. From the original code you posted you will just be writing to a block of cells in the end, so why not just write them all to the worksheet at one time [The reason why I used a collection and then iterated it] ?
 
Upvote 0
Hi Moshim,

Just checked, no updates available.

However, I do understand about minimising read/write actions to the spreadsheet will speed up the execution.
A few ways to speed up, including pre sizing an output array


I just tried your code and it's as slow run-time: just under 10 seconds for a single table.

This is not a code problem or who can write more complex, but faster code i.e trade off between code complexity and memory use and code length.

What I have is very basic and simple and should not take over an hour, to run regardless. I've written, used and observed more complex code that's been faster, that's why this is confusing. And it was running noticibly faster last week (same code).
@Fluff, will try debug.print later and reply back.
 
Last edited:
Upvote 0
I have certain macros that run quickly and to completion in excel 2010 but never complete in my 365 version 1908. They also run much much slower until excel eventually errors seemingly at random places. The macros are opening and closing files to remove information. Why they cant complete or why they run slower i do not know. Like you say its not a question of efficiency of the code as its the same code being used on two different versions of excel. The 365 version is just slower. I have to keep a machine running 2010 just to run these macros.
 
Upvote 0
Thank you Steve, I wasn't aware of this issue, I only recently upgraded to Office 365. For now it is a process that can be left to run, but anticipate future faster demands :(
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top