What is the fastest way to scan a large range in Excel?

Today's author, Dany Hoter, a Product Planner on the Excel team, talks about some performance characteristics he recently discovered using various methods to manipulate large ranges using VBA.

Problem description

You have a large range in Excel with data. Let's say it contains 100,000 rows and 50 columns for each row (Yes you are using Excel 2007 of course). So altogether you have 5,000,000 cells. Columns A to F have some alphanumeric data that you need analyze and based on the combination of values for each row you need to use the numeric values in G to H to do some calculations and store the results in columns I and J. You could place 200,000 formulas in I and J but you see that a spreadsheet with such a volume of formulas gets very sow and consumes huge amounts of memory.

You decide to try and solve it in a piece of VBA code. The question is how to implement such a task in the most efficient way?

What are your options

How can you scan a range in Excel, read the values in some cells, and change some others?

Use a range object

Let's assume that the range you want to read starts at A1

The code looks something like this:

Dim DataRange as Range ' Could also be Dim DataRange as Object
Dim Irow as Long
Dim MaxRows as Long
Dim Icol as Integer
Dim MaxCols as Long
Dim MyVar as Double
Set DataRange=Range("A1").CurrentRegion
MaxRows= Range("A1").CurrentRegion.Rows.Count
MaxCols= Range("A1").CurrentRegion.Columns.Count
For Irow=1 to MaxRows 
  For icol=1 to MaxCols 
    MyVar=DataRange(Irow,Icol) 
    If MyVar > 0 then 
      MyVar=MyVar*Myvar ' Change the value 
      DataRange(Irow,Icol)=MyVar 
    End If 
  Next Icol
Next Irow

Use the selection and move it using offset

Many VBA developers learned VBA techniques from macro recording.

When using relative reference the generated VBA code creates statements like:

ActiveCell.Offset(0, -1).Range("A1").Select

As a consequence many developers adopt this technique and use the ActiveCell or selection ranges to move from cell to cell in code and read or write the cell values. The code will look like this:

Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
Range("A1").Select
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows 
  For Icol = 1 To MaxCols 
    MyVar = ActiveCell.Value 
    If MyVar > 0 Then 
      MyVar=MyVar*Myvar ' Change the value 
      ActiveCell.Value = MyVar 
    End If 
    ActiveCell.Offset(0, 1).Select ' Move one column to the right 
  Next Icol 
  ActiveCell.Offset(1, -MaxCols).Select ' Move one rows down and back to first column
Next Irow

Use a variant type variable

This technique copies the values from all cells in the range into a variable in memory, manipulates the values inside this variable and if needed moves the values back to the range after manipulation.

Here is the code this time:

Dim DataRange As Variant
Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
DataRange = Range("A1").CurrentRegion.Value ' Not using set
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows 
  For Icol = 1 To MaxCols 
  MyVar = DataRange(Irow, Icol) 
  If MyVar > 0 Then 
    MyVar=MyVar*Myvar ' Change the value 
    DataRange(Irow, Icol) = MyVar 
  End If
Next Icol
Next Irow
Range("A1").CurrentRegion = DataRange ' writes back the result to the range

Another difference is that this method is blazing fast compared to the two others.

Performance Summary

I compared the three methods on relatively large ranges and here are the results:
 

As you can see using a variant variable is much faster especially when changing cells. Even if the calculation can be done with Excel formulas, in some cases this method is the only one acceptable because using a very large number of formulas can become very slow.

Obviously the one method to avoid is moving the ActiveCell using Offset.