Excel 365 Macros running slow

[JackDanIce]

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)

 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

 

[JackDanIce]

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

 

[MoshiM]

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

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

 

[JackDanIce]

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.

 

[Fluff]

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.

 

[JackDanIce]

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.

 

[MoshiM]

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] ?

 

[JackDanIce]

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.

 

[steve the fish]

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.

 

[JackDanIce]

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