Macro takes more time to be executed when activated through button inside sheet in Excel 2019

Question

I have a macro in Excel 2019 which runs in less than one second directly through VBE (by pressing F5), or when I configure a button to the macro in the Ribbon (through options>customize ribbon).

When I create a button (FormControlButton) inside the sheet area, and associate the macro, it takes at least seven seconds.

The macro runs without any error message. Other macros are slower as well, but this one is the most noticeable.

My macro builds a jagged array with data (~4000 records) that is in another sheet, then sorts the array by bubble/quicksorting (tested both to check if the problem could be here, and it wasn't), then filters it and returns data in a new sheet.

The macros where designed in Excel 2010, and I noticed the problem right after our company updated Microsoft Office from 2010 to 2019. (Windows was updated the same day from 2007 to 10, but I think the problem is in Excel, as I tested it again in some PCs that still had Office 2010 and the macros worked as fast as if run through VBE). Creating and editing macros is not prohibited by administrators.

Adding more information as requested:

I didn't add code because it's not a problem of a specific macro, but I noticed the ones that slowed most are the ones that interact with arrays. Besides that, as it didn't happen when I used buttons inside a sheet in Office 2010, maybe it's a bug in Office 2019.

One thing in common in all my macros is that I follow Microsoft's recommendations to speed up macros, and I use this chunk of code:

Sub SubName()

    Call DeactivateSystemFunctions

    'Rest of the code

    Call ReactivateSystemFunctions

    End Sub

Where

Sub DeactivateSystemFunctions()
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual
    Application.ActiveSheet.DisplayPageBreaks = False
    Application.EnableEvents = False
End Sub


Sub ReactivateSystemFunctions()
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic
    Application.ActiveSheet.DisplayPageBreaks = True
    Application.EnableEvents = True
End Sub

I don't use .activate or .select in any of my macros, and while formatting I always try to put the max inside a With/End With.

Answer 1

My macro was working fine through VBE but taking too many time when activated through a FormControlButton inside my sheet. As @RonRosenfeld suggested, I had to set a timer to each specific part of my code to find where the problem was. I put the timer at the beggining of my code and I had to move the command to stop the timer to each part of it until I found where it was getting slow.

My macro creates a jagged array and then sorts it through Quicksorting, and as the quicksort I made takes more than one criteria to sort, I thought the problem might be occurring there, as it is a recursive method.

But actually the problem was happening when I was printing the results of the sorted jagged array in another worksheet I create using the same macro. I print data this way:

NewSheet.Cells(NewSheetRow, Column1) = SortedArray(RecordNumber)(DesiredInfo1 - 1)
NewSheet.Cells(NewSheetRow, Column2) = SortedArray(RecordNumber)(DesiredInfo7 - 1)
NewSheet.Cells(NewSheetRow, Column3) = SortedArray(RecordNumber)(DesiredInfo14 - 1)

'As my jagged array is built with data from a Source Worksheet:
'RecordNumber is the (Row - 1) in the source worksheet
'DesiredInfoX is the Column in the source worksheet

The problem happened only when printing specific Columns. The source sheet has different columns, each with a different data format. The only data format that slowed things down was strings.

I went to the source worksheet and noticed some problems:

• As the file went from excel 2000 to 2010 to 2019 and data was not migrated but simply saved from .xls to .xlsm, when I went to the end of the source sheet, I noticed it had only 65536 rows (not 1048576 as expected), but had 16384 columns (last=XFD). It was only happening with the source sheet, which is the one we have more data in. Other sheets in the same workbook had the expected 1048576 rows and 16384 columns.

• After we started using excel 2019, some of the data that was supposed to be String(Text), was formatted as GENERAL/NUMBER. I can't affirm it was not human error, but our source sheet is filled by macro, not by human, and the macro forces formatting of each data.

What I did to solve the problem: I migrated all data from all sheets, to a new workbook using VBA, not copy/paste. After passing the values to the new source sheet, I forced the formatting of each column. All macros had to be migrated as well.

After that, the FormControlButton inside the sheet is working as fast as activating the macro directly through VBE by pressing F5.

If anybody needs:

'###Timer code
'Got it from https://www.thespreadsheetguru.com/the-code-vault/2015/1/28/vba-calculate-macro-run-time
'Put this part in the beggining of your code
Dim StartTime As Double
Dim SecondsElapsed As Double

'Remember time when macro starts
  StartTime = Timer



'Put this part where you want the timer to stop
'Determine how many seconds code took to run
  SecondsElapsed = Round(Timer - StartTime, 2)

'Notify user in seconds
  MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation


'###Migration macro:
Sub Migrate()

Call DeactivateSystemFunctions

'Source File
Dim XLApp As Object
Dim WbSource As Object
Dim WsSource As Object

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False    

Set WbSource = XLApp.Workbooks.Open("C:\FolderFoo\FolderBar\Desktop\SourceFileName.Extension")
Set WsSource = WbSource.Worksheets("SourceWorksheetName")


'Destination File. May be set as source file or if using this workbook by simply:
Dim WsDest As Worksheet    
Set WsDest = ThisWorkbook.Worksheets("DestinationSheetName")


Dim BDR As Long
Dim BDC As Long

Dim UltR As Long
Dim UltC As Long

UltR = WsSource.Cells(Rows.Count, 1).End(xlUp).Row
UltC = WsSource.Cells(1, Columns.Count).End(xlToLeft).Column

For BDR = 1 To UltR

    For BDC = 1 To UltC
        
        If WsSource.Cells(BDR, BDC) <> vbEmpty Then
        
            WsDest.Cells(BDR, BDC) = WsSource.Cells(BDR, BDC)
   
        End If
    
    Next BDC

Next BDR

'Format your columns as needed    
With WsDest
.Columns(Column1Number).NumberFormat = "0"
.Columns(Column2Number).NumberFormat = "dd/mm/yyyy"
.Columns(Column3Number).NumberFormat = "@"
.Columns(Column4Number).NumberFormat = "@"
.Columns(Column5Number).NumberFormat = "0.000"
End With


WbSource.Close SaveChanges:=False

Call ReactivateSystemFunctions

End Sub

Answer 2

TLDR: I had the same problem and I think it's the fault of the mouse pointer.

My solution:

Dim Cursor As XlMousePointer
Cursor = Application.Cursor
Application.Cursor = xlWait

-- YOUR CODE HERE --

Application.Cursor = Cursor

Long Version: I noticed a similar problem in one of my macros and it seems to occur when lots of cells are manipulated individually (i.e. looping through hundreds or thousands of cells and changing their values).

You might notice the mousepointer changing eratically when you start it from the FormButton or ActiveX Button, but when you start the macro from VBE, it does not do that.

In my case, the macro even started to run faster if I took the focus off the excel window - something as simple as moving the mouse over the windows start-menu button or over an application in the windows taskbar improved the performance of the macro.

I did not research this further, but I concluded that the erratic changes to the mousepointer were in fact the killer to the performance, so I did the following:

Before changing the cells, I set the mousepointer to something other than "Default". When you do that it stays there and does not change while you manipulate the cells. After that I set the mousepointer to whatever status it had before my macro.

I did not have the same problem since.