Errors in spreadsheets... result in incorrect decisions being made and significant losses incurred.
Beaman, et al (2005)
Research on spreadsheet errors is substantial, compelling, and unanimous.
Panko (2015)
Spreadsheets contain errors at an alarmingly high rate.
Abraham, et al (2005)
Spreadsheet errors are still the rule rather than the exception.
Nixon & O'Hara (2010)
1% of all formulas in operational spreadsheets are in error.
Powell, Baker, & Lawson (2009)
Overconfidence is one of the most substantial causes of spreadsheet errors.
Sakal, et al (2015)
Developing an error-free spreadsheet has been a problem since the beginning of end-user computing.
Mireault (2015)
It is irrational to expect large error-free spreadsheets.
Panko (2013)
Spreadsheets are extraordinarily and unacceptably prone to error.
Dunn (2010)
Spreadsheets are alarmingly error-prone to write.
Paine (2001)
The issue is not whether there is an error but how many errors there are and how serious they are.
Panko (2007)
The results given by spreadsheets are often just wrong.
Sajaniemi (1998)
Untested spreadsheets are riddled with errors.
Miller (2005)
It is now widely accepted that errors in spreadsheets are both common and potentially dangerous.
Nixon & O'Hara (2010)
Every study, without exception, has found error rates much higher than organizations would wish to tolerate.
Panko (1999)
Programmers exhibit unwarranted confidence in the correctness of their spreadsheets.
Krishna, et al (2001)
94% of the 88 spreadsheets audited in 7 studies have contained errors.
Panko (2008)
Spreadsheet errors have resulted in huge financial losses.
Abraham & Erwig (2007)
Spreadsheets are often hard, if not impossible, to understand.
Mireault & Gresham (2015)
Spreadsheets are notoriously error-prone.
Cunha, et al (2011)
60% of large companies feel 'Spreadsheet Hell' describes their reliance on spreadsheets.
Murphy (2007)
Never assume a spreadsheet is right, even your own.
Raffensperger (2001)
Spreadsheets are easy to use and very hard to check.
Chen & Chan (2000)
Spreadsheet errors... a great, often unrecognised, risk to corporate decision making & financial integrity.
Chadwick (2002)
Your spreadsheets may be disasters in the making.
Caulkins, Morrison, & Weidemann (2006)
Spreadsheet shortcomings can significantly hamper an organization's business operation.
Reschenhofer & Matthes (2015)
Most large spreadsheets have dozens or even hundreds of errors.
Panko & Ordway (2005)
Most executives do not really check or verify the accuracy or validity of [their] spreadsheets...
Teo & Tan (1999)
Even obvious, elementary errors in very simple, clearly documented spreadsheets are... difficult to find.
Galletta, et al (1993)
Spreadsheet development must embrace extensive testing in order to be taken seriously as a profession.
Bock (2016)
Spreadsheets are the most popular live programming environments, but they are also notoriously fault-prone.
Hermans & van der Storm (2015)
Despite being staggeringly error prone, spreadsheets are a highly flexible programming environment.
Abreu, et al (2015)
Spreadsheets are dangerous to their authors and others.
Durusau & Hunting (2015)
Spreadsheets have a notoriously high number of faults.
Rust, et al (2006)
A significant proportion of spreadsheets have severe quality problems.
Ayalew (2007)
People tend to believe their spreadsheets are more accurate than they really are.
Caulkins, Morrison, & Weidemann (2006)
Despite overwhelming and unanimous evidence... companies have continued to ignore spreadsheet error risks.
Panko (2014)
A lot of decisions are being made on the basis of some bad numbers.
Ross (1996)
Spreadsheet errors are pervasive, stubborn, ubiquitous and complex.
Irons (2003)
...few incidents of spreadsheet errors are made public and these are usually not revealed by choice.
Kruck & Sheetz (2001)
Every study that has looked for errors has found them... in considerable abundance.
Panko & Halverson (1996)
The software that end users are creating... is riddled with errors.
Burnett & Myers (2014)
Spreadsheets are commonly used and commonly flawed.
Caulkins, Morrison, & Weidemann (2008)
The quality and reliability of spreadsheets is known to be poor.
Bishop & McDaid (2007)
Errors in spreadsheets are as ubiquitous as spreadsheets themselves.
Colbenz (2005)
Spreadsheets are more fault-prone than other software.
Kulesz & Ostberg (2013)
Spreadsheets can be viewed as a highly flexible programming environment for end users.
Abreu, et al (2015)
The untested spreadsheet is as dangerous and untrustworthy as an untested program.
Price (2006)
Spreadsheets... pose a greater threat to your business than almost anything you can imagine.
Howard (2005)
Studies have shown that there is a high incidence of errors in spreadsheets.
Csernoch & Biro (2013)

Improving spreadsheet run time

An important aspect of a spreadsheet's fitness-for-purpose is ensuring that its run time is acceptable to the users. Creating an inefficient spreadsheet is easy, while creating an efficient spreadsheet requires a bit more thought and the application of some techniques to reduce run time.

This article presents an example of using refactoring techniques to vastly improve the run time of a spreadsheet. We focus on an especially slow feature of Excel: the interaction between VBA and the worksheets.

Example of interaction between VBA and the worksheets

The example - which is available for download below - does a simple, but very repetitive, task using VBA.

The task is to:

  • Start with a block of 10,000 random integers between 1 and 100 inclusive.
  • Set an output block of 10,000 values equal to zero.
  • Add one to each input value.
  • Write the results to the output block.
  • Each cell in the output block is used in a subsequent calculation.VBA methods for implementing the task.

VBA methods to complete the task

To illustrate how different approaches to the task can lead to large differences in performance, the example spreadsheet uses two VBA methods:

  • Method 1: A naive, but very common, approach to working with worksheet data - wth VBA selecting and changing each cell individually, which then triggers a workbook recalculation at each step. This method is very inefficient. Using the Application.ScreenUpdating property would reduce the run time somewhat, but the method would still take a long time to complete the task.
  • Method 2: A more sophisticated approach designed for maximum efficiency. It treats the data as an array, rather than as individual cells, and does the calculation in VBA using variables that have been properly declared.
Example results
Run time result
There is an enormous difference between the two methods.

Different methods result in an enormous difference in run time

The difference in run time between the two methods is enormous: on our testing PC, Method 1 takes almost 5 minutes to complete the task, while Method 2 takes 0.023 seconds. That is, Method 2 is more than twelve thousand times faster than Method 1!

Depending on your computer environment your run times may differ from ours, but nonetheless we would expect a large difference in run times between the two methods irrespective of the computer you're using.

With just a few changes to the method, this example shows that it is possible to greatly improve the run time of your VBA code. The following sections show the code for the two methods.

Details of Method 1

The VBA code for Method 1 is shown below. It is called from another procedure, which handles the user interaction and calculation of the run time.

Code for Method 1
This code is a very inefficient approach to implementing the task. It selects each cell individually, switching back-and-forth between VBA and the worksheets, which is very slow. It also doesn't declare variables, which implicitly means that they are all of type Variant (which is slower than explicitly declared variables).
Private Sub Method1()
' Typical method for interaction between VBA and worksheets.
' This method is very inefficient, as it fails to explicitly declare variables and
' then it loops over the input data by selecting each cell individually.
' Each step triggers a recalculation of the worksheets, which greatly increases the
' run time of the procedure.

   Worksheets("Input").Select
   NumRows = Range("Data").Rows.Count        ' Adapt to the size of the input block
   NumCols = Range("Data").Columns.Count

   Worksheets("Output").Select
   For i = 1 To NumRows                      ' Loop over each cell in the output block
      For j = 1 To NumCols                   ' and set their values to zero
         Range("OutputTopLeft").Offset(i - 1, j - 1).Value = 0
      Next j
   Next i
   
   For i = 1 To NumRows                      ' Loop over each cell in the input block,
      For j = 1 To NumCols                   ' perform the required function, and write
         Worksheets("Input").Select          ' the results to the output block
         CurrCell = Range("DataTopLeft").Offset(i - 1, j - 1).Value
         NewVal = CurrCell + 1
         Worksheets("Output").Select
         Range("OutputTopLeft").Offset(i - 1, j - 1).Value = NewVal
      Next j
   Next i
   Worksheets("Input").Select

End Sub

Details of Method 2

The VBA code for Method 2 is shown below.

Code for Method 2
This code is much more efficient than Method 1. It works with the input data as an array, avoiding almost all of the worksheet interaction. In addition to being more efficient, the code is also shorter and simpler.
Private Sub Method2()
' More efficient method for large scale interaction between VBA and worksheets.
' This method treats the data as an array, which is many times faster than working
' with cells individually. The calculation is also done in VBA, which avoids
' worksheet recalculations.

   Dim DataArray As Variant                  ' Local copy of input (must be Variant)
   Dim NumRows As Integer                    ' Number of rows in the data
   Dim NumCols As Integer                    ' Number of columns in the data
   Dim CurrRow As Integer                    ' Loop counter for rows
   Dim CurrCol As Integer                    ' Loop counter for columns
   
   DataArray = Range("Data")                 ' Get the data in a single step
   NumRows = UBound(DataArray, 1)
   NumCols = UBound(DataArray, 2)
   
   Range("Output").Value = 0                 ' Set all cells in the output block to zero
   
   For CurrRow = 1 To NumRows                ' Loop over each cell, perform the required
      For CurrCol = 1 To NumCols             ' function, and write to the output block
         DataArray(CurrRow, CurrCol) = DataArray(CurrRow, CurrCol) + 1
      Next CurrCol
   Next CurrRow
   Range("Output") = DataArray
   
End Sub

Download the example to see more about how it works

Many techniques can be used to reduce the run time of a spreadsheet. The techniques that are appropriate in each case will depend on the particular characteristics of the spreadsheet. This example illustrates just a few of the techniques, so download the example and have a look at the code.

If you have any comments about this article, then please contact us.

Download the example spreadsheet: RunTimeTest.xlsm
When you open the spreadsheet you will need to enable macros in order to run the example.
Instructions for running the example are provided on the 'Notes' worksheet.

Go to top