My Excel recently has become exceedingly slow.
Trying to find whereabouts the slowness lies, I stumbled upon the following code, which, on a newly created table, takes up to one minute to execute, the “Add” command. There maybe more issues, as Excel regularly becomes unresponsive, takes a long time to save files etc. However this is a simple sheet with very little VBA, and I cannot imagine why the “Add” takes so long, especially the initial Add past ListObject creation.
Set tExDividends = ws.ListObjects.Add(xlSrcRange, ws.Range(ws.Cells(iHeaderRow, iStartCol), ws.Cells(iHeaderRow, iStartCol + iCol - 1)), False, xlYes, 0)
tExDividends.Name = "ExDividends"
Dim lrow As ListRow
Set lrow = tExDividends.ListRows.Add
3
If you have the version 2408 (Build 16.0.17928.20114) – maybe also valid for other build versions –, released the 2024 August 26: the update seems to have greatly slowed VBA execution for several people.
After some investigation, for us, it seems to be the update of cells that takes a lot longer since the update.
The problem seems to impact both 32 and 64 bits versions.
EDIT
The slowness problems have been fixed in the new versions (starting from Version 2408 (Build 17928.20156)).
Previous answer
Rolling back to the version 2407 solves the problem.
Here is a link to a discussion about that in the community forum:
https://answers.microsoft.com/en-us/msoffice/forum/all/excel-with-macros-running-very-slow-after-latest/0c520822-8f56-4f1f-b191-0c24d66f946a
Did you start with a workbook and a large(er) amount of VBA code, then cut it down to just the bare minimum? If so, it could be that the Excel workbook itself is corrupt. Start with a brand new workbook and use the test code below. In my tests, it added the new row way too fast to even bother timing it.
Option Explicit
Sub AddTableRowTest()
Dim myTable As ListObject
Set myTable = AttachToTable("test_table")
Dim lRow As ListRow
Set lRow = myTable.ListRows.Add
Debug.Print "the table now has " & myTable.ListRows.Count & " rows"
End Sub
Function AttachToTable(ByVal tableName As String) As ListObject
Dim theTable As ListObject
On Error Resume Next
Set theTable = ActiveSheet.ListObjects(tableName)
On Error GoTo 0
If theTable Is Nothing Then
Set theTable = ActiveSheet.ListObjects.Add(xlSrcRange, Sheet1.Range("A1").Resize(10, 4))
theTable.Name = tableName
End If
Set AttachToTable = theTable
End Function
Not sure what content you have in your workbook, but you can speed up things by setting:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
and when done setting these to:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
1