Optimize VBA Excel 2003 NextFor loop

[eric_rutt]

I'm using Excel 2003 VBA to copy and paste values from one cell to
another which my spreadsheet then uses to runs these values through
many formulas and generates one result. VBA copies this result to a
result column and proceeds to the next row and so on for about 1000
rows. VBA is simply being used to feed many combinations of values (in
my case, pairs of values) into a 20MB spreadsheet which does the heavy
calcs. I tested my code on 5 rows of input values and it took ~30
seconds to generate a result.
Can anyone give me tips for optimizing my code? I have already done the
following: (1) declared my variables as Range rather than Object and as
Integer rather than Variant, (2) turned off screen updating, (3) used
Range instead of Cells, (4) used Range Object instead of Selection
Object, (5) set my range info as a variable rather than specifying . I
read on Chip Pearson's website that For Each loops are usually faster
than For...Next loops, but (as is obvious) I am new to VBA and do not
know how to use For Each with my project. I also cannot turn off
calculate because I need the spreadsheet to recalculate each time a new
row is evaluated. Below is my code as it currently stands:


Sub OpponentsOdds()

Worksheets("Pairs").Activate
Application.ScreenUpdating = False

Dim I1 As Range
Dim I2 As Range
Dim I3 As Range
Dim I4 As Range
Dim Index As Range
Dim Counter As Range
Dim TotPairs As Range
Dim Suit1 As Range
Dim Suit2 As Range
Dim Value1 As Range
Dim Value2 As Range
Dim IndexTot As Range
Dim i As Integer

Set I1 = Range("Input1") 'The following variables each refer to 1 cell
Set I2 = Range("Input2")
Set I3 = Range("Input3")
Set I4 = Range("Input4")
Set Index = Range("Index")
Set Counter = Range("Counter")
Set TotPairs = Range("TotPairs")
Set Suit1 = Range("Suit1") 'The following variables refer to 2704
cells
Set Suit2 = Range("Suit2")
Set Value1 = Range("Value1")
Set Value2 = Range("Value2")
Set IndexTot = Range("IndexTot")

'Define loop by number of pairs (counter); use 5 for test purposes
For i = 1 To 5

If Counter(i) <> "" Then
'Copy and paste pair info to inputs
Suit1(i).Copy
I1.PasteSpecial Paste:=xlPasteValues
Value1(i).Copy
I2.PasteSpecial Paste:=xlPasteValues
Suit2(i).Copy
I3.PasteSpecial Paste:=xlPasteValues
Value2(i).Copy
I4.PasteSpecial Paste:=xlPasteValues

'Copy and paste Output (or index) to output column (IndexTot)
Index.Copy
IndexTot(i).PasteSpecial Paste:=xlPasteValues

Else
Exit For
End If
Next i

Application.ScreenUpdating = True

End Sub

 

[Oliver Ferns via OfficeKB.com]

Hi,
you can cut out the copy/paste process between two ranges using the
following syntax

MyRange1.Value = MyRange2.Value


This will speed up the code considerbly.

Hth,
Oli

 

[Tushar Mehta]

For the most part, the kind of changes you made are what I call micro-
improvements. While those kinds of recommendations may be common, the
improvements are of the magnitude of hardware operating speeds --
microseconds and milliseconds.

In your case, it would appear the workbook takes a long time to
recalculate. If you do it by hand (i.e., with the F9 key) how long
does it take? OK, once you know that, you know the best that the VBA
code you shared can do. There's no way for it to do better than the
minimum time needed to calculate the workbook.

The fact that you need to do so for each *set* is no reason to leave
calculation set to automatic. The latter means that XL will
recalculate as you do each of the copy/paste operations! If I were you
I'd set calculation to manual, then in the loop do the 4 copy+paste
operations, force a recalculation, and copy+paste the result. This way
XL recalculates the workbook once for each data set, not five times,
once after each copy+paste operation.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions