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

Red Adair

New Member
Joined
Mar 25, 2021
Messages
7
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
As an example, the following very simple code executes in 0.03 seconds using Excel 2003 but takes 4.28 seconds using Excel 2019.

VBA Code:
'==============================================================================
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

Red Adair

New Member
Joined
Mar 25, 2021
Messages
7
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
@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

New Member
Joined
Mar 25, 2021
Messages
7
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,119
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Have you tried saving the files as xlsb rather than xlsm?
 

Red Adair

New Member
Joined
Mar 25, 2021
Messages
7
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

@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

New Member
Joined
Mar 25, 2021
Messages
7
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
@jasonb75 Not only is your code cleaner but it runs much, much faster than my code, so thank you.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,935
Members
418,253
Latest member
TheJackal26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top