Results 1 to 10 of 10

Thread: Excel 365 Macros running slow

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Excel 365 Macros running slow

    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)
    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


  2. #2
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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


  3. #3
    Board Regular
    Join Date
    Jan 2018
    Posts
    266
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel 365 Macros running slow

    Quote Originally Posted by JackDanIce View Post
    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 by MoshiM; Sep 19th, 2019 at 01:26 PM.

  4. #4
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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.


  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Excel 365 Macros running slow

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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.


  7. #7
    Board Regular
    Join Date
    Jan 2018
    Posts
    266
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Excel 365 Macros running slow

    Quote Originally Posted by JackDanIce View Post
    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] ?

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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 by JackDanIce; Sep 20th, 2019 at 04:15 AM.


  9. #9
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,668
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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.
    Looking for opportunities

  10. #10
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Excel 365 Macros running slow

    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


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •