Why is VBA so slow to execute on any version after Excel 2003?

[Red Adair]

As an example, the following very simple code executes in 0.03 seconds using Excel 2003 but takes 4.28 seconds using Excel 2019.

'==============================================================================
Sub modInsertNewData()
'==============================================================================
  Sheets("TST").Select
  Range("B5:E5").Cells.Insert
  Range("B5:E5").RowHeight = 12.75
  '------------------------------------------------------------------------------
  Range("B6").Copy
  Range("B5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("B5").Formula = "=R[1]C+7"
  Range("B5").Copy
  Range("B5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  '------------------------------------------------------------------------------
  Range("C6").Copy
  Range("C5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  '------------------------------------------------------------------------------
  Range("D6").Copy
  Range("D5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("D5").Formula = "=IF(RC[-1]="""","""",(ROUND(((RC[-1]-R[1]C[-1])/7),2)))"
  '------------------------------------------------------------------------------
  Range("E6").Copy
  Range("E5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Range("E5").Formula = "=RC[-2]-R[1]C[-2]"
  '------------------------------------------------------------------------------
  Range("C5").Select
End Sub

 

[johnnyL]

Where is your timing code?

 

[jasonb75]

Could be any number of reasons, that code wouldn't take so long without something else slowing it down, it is instant for me using 365.
If you have formulas that look at entire rows (or columns) then they will take longer to calculate due to the much bigger sheet size from 2007 onward.

Cutting out the unnecessary copy and paste activities might help to speed it up a little, as will turning off calculation and screen refresh while it runs.

Sub modInsertNewData()
With Application
    .ScreenUpdating = False
    .Calculation = xlManual

Sheets("TST").Select
    With Range("B5:E5")
        .Cells.Insert , CopyOrigin:=xlFormatFromRightOrBelow
        .RowHeight = 12.75
    End With
    
    Range("B5").Value = Range("B6").Value +7

    Range("D5").Formula = "=IF(RC[-1]="""","""",(ROUND(((RC[-1]-R[1]C[-1])/7),2)))"
    
    Range("E5").Formula = "=RC[-2]-R[1]C[-2]"

    Range("C5").Select
    
    .Calculation = xlAutomatic
    .ScreenUpdating = True
End With
End Sub

 

[Red Adair]

@jasonb75 Thank you for your reply. This code example is running on an [xls] file so only 65536 rows to address. As regards my VBA code example, this is just one of many, many VBA routines that I regularly use, so I am not attempting to speed up this specific code. I chose this code example because it should not be affected by the file format, [xls] versus [xlsx], as it does not perform a row or column insertion.

I have had this issue since I purchased Excel 2007 when it was first released. I spent an eternity corresponding with Microsoft to rectify this issue but to no avail. Eventually, I asked Microsoft to refund my purchase price which they refused.

I know that this is not machine specific as I would have run this type of code on over 20 computers.

My only conclusion is that Microsoft coded Excel 2007 to always address 1048576 rows and 16384 columns, irrespective of the actual file format. However, you make an interesting point about it running instantaneously on Office 365. I will now test Office 365.

Regards

 

[Red Adair]

@jasonb75 Also, thank you for your code which is much cleaner than my example.

 

[MARK858]

Have you tried saving the files as xlsb rather than xlsm?

 

[Red Adair]

@MARK858 Thank you for your suggestion but at the moment, I am saving the files as [xls] so that they are backwardly compatible with Excel 2003.

 

[Red Adair]

@jasonb75 Not only is your code cleaner but it runs much, much faster than my code, so thank you.

 

[jasonb75]

My only conclusion is that Microsoft coded Excel 2007 to always address 1048576 rows and 16384 columns, irrespective of the actual file format.

I don't remember what was specified when the sheet size was increased in 2007, but the basic principle would be that the sheet size will be based on the version of excel opening the file.
Anything used in the workbook. Formulas, conditional formatting, vba, etc. will be based on how you have set them up. If you have something that is running on an entire column then that is 65536 cells in 2003, or 1048576 in anything newer. Even in the unlikely event that every one of those cells is used in 2003, it will still mean 93% of the processing effort is wasted in 2007 or newer.

There are many reasons for slow running code. It is quite possible that your code was triggering a chain of events which caused it to take longer by the way that it was written. This would almost certainly be the same for excel 2003, but may be less noticeable.