Microsoft Office 2007 What is the fastest way to s
 

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,Windows 7 Starter Key,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,Microsoft Office 2007, -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,Discount Office 2007, 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:


Method

Operation

Cells/Sec

Variant

Read

1,225,490

Write

714,Office Pro,286

Read/Write

263,Microsoft Office 2007 Pro,158

Range

Read

250,000

Write

1818

Read/Write

1,852

Offset

Read

206

Write

200

Read/Write

203
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.
<div