Excel 365 Macros running slow

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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

Active Member
Joined
Jan 31, 2018
Messages
303
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:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,558
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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

Active Member
Joined
Jan 31, 2018
Messages
303
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,882
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,476
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 :(
 

Forum statistics

Threads
1,089,331
Messages
5,407,625
Members
403,155
Latest member
ValenBaez

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top