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