When it comes to work with Excel's VBA, many people face 'huge' (and i mean it) speed issues. This issues are very common when reading/writing data from/to a worksheet inside a loop.
Reading and writing from Excel to VBA (and vice versa) is a slow task, and the only solution to get through this problem is to reduce the times it is done.
An easy way out is to use a Variant. When you asign a Range to a Variant, the Variant wont become a Variant/Range, but a 2 dimension Array with the Range's values (great! ^.^). While doing this, you'll only read the data (Range) once. Also, when you asign an Array to a Range, the Range will take the Array's values. This way, you'll only have to write the data once.
I think the easiest way to understand it will be with a little example:
I'll write two columns of 20,000 random numbers in a sheet, then read those numbers and write its product in a third column. Example A will do it using FOR/NEXT to write the numbers, to read them and to write the product. Example B will do it using array's to write, read and write again (the product).
I'll take the time each example takes to acomplish (using a 990mhz, 256 ram, old computer)
[CODE=vb]Sub ExampleA()
Dim t As Single
Dim i As Long
t = Timer
With Worksheets(1)
'STEP 1: Create the list
For i = 1 To 20000
.Cells(i, 1) = Rnd
.Cells(i, 2) = Rnd
Next
'STEP 2: Read the list, write the product
For i = 1 To 20000
.Cells(i, 3) = .Cells(i, 2) * .Cells(i, 2)
Next
End With
MsgBox "Example A took " & Timer - t & " seconds."
End Sub[/CODE]
It took 14.5625 secs
[CODE=vb]Sub ExampleB()
Dim t As Single
Dim Var1 As Variant
Dim Arr1() As Double, Arr2() As Double
t = Timer
With Worksheets(2)
'STEP 1: Create the list
ReDim Arr1(1 To 2000, 1 To 2)
For i = 1 To UBound(Arr1)
Arr1(i, 1) = Rnd
Arr1(i, 2) = Rnd
Next
Range(.Cells(1, 1), .Cells(UBound(A rr1), 2)) = Arr1
'STEP 2: Read the list, write the product
Var1 = Range(.Cells(1, 1), .Cells(UBound(A rr1), 2))
ReDim Arr2(1 To UBound(Var1), 1 To 1)
For i = 1 To UBound(Var1)
Arr2(i, 1) = Var1(i, 1) * Var1(i, 2)
Next
Range(.Cells(1, 3), .Cells(UBound(A rr2), 3)) = Arr2
End With
MsgBox "Example B took " & Timer - t & " seconds."
End Sub[/CODE]
It took 8.98 x 10^-2 seconds (0.0898 secs)
after this, we can conclude:
Example B is 166 times faster than Example A, and it does exactly the same thing.
My only suggestion: Do not use Cells or Range references inside a loop.
Kad.
For editors corner: any comment or sugestion, be welcome
Reading and writing from Excel to VBA (and vice versa) is a slow task, and the only solution to get through this problem is to reduce the times it is done.
An easy way out is to use a Variant. When you asign a Range to a Variant, the Variant wont become a Variant/Range, but a 2 dimension Array with the Range's values (great! ^.^). While doing this, you'll only read the data (Range) once. Also, when you asign an Array to a Range, the Range will take the Array's values. This way, you'll only have to write the data once.
I think the easiest way to understand it will be with a little example:
I'll write two columns of 20,000 random numbers in a sheet, then read those numbers and write its product in a third column. Example A will do it using FOR/NEXT to write the numbers, to read them and to write the product. Example B will do it using array's to write, read and write again (the product).
I'll take the time each example takes to acomplish (using a 990mhz, 256 ram, old computer)
[CODE=vb]Sub ExampleA()
Dim t As Single
Dim i As Long
t = Timer
With Worksheets(1)
'STEP 1: Create the list
For i = 1 To 20000
.Cells(i, 1) = Rnd
.Cells(i, 2) = Rnd
Next
'STEP 2: Read the list, write the product
For i = 1 To 20000
.Cells(i, 3) = .Cells(i, 2) * .Cells(i, 2)
Next
End With
MsgBox "Example A took " & Timer - t & " seconds."
End Sub[/CODE]
It took 14.5625 secs
[CODE=vb]Sub ExampleB()
Dim t As Single
Dim Var1 As Variant
Dim Arr1() As Double, Arr2() As Double
t = Timer
With Worksheets(2)
'STEP 1: Create the list
ReDim Arr1(1 To 2000, 1 To 2)
For i = 1 To UBound(Arr1)
Arr1(i, 1) = Rnd
Arr1(i, 2) = Rnd
Next
Range(.Cells(1, 1), .Cells(UBound(A rr1), 2)) = Arr1
'STEP 2: Read the list, write the product
Var1 = Range(.Cells(1, 1), .Cells(UBound(A rr1), 2))
ReDim Arr2(1 To UBound(Var1), 1 To 1)
For i = 1 To UBound(Var1)
Arr2(i, 1) = Var1(i, 1) * Var1(i, 2)
Next
Range(.Cells(1, 3), .Cells(UBound(A rr2), 3)) = Arr2
End With
MsgBox "Example B took " & Timer - t & " seconds."
End Sub[/CODE]
It took 8.98 x 10^-2 seconds (0.0898 secs)
after this, we can conclude:
Example B is 166 times faster than Example A, and it does exactly the same thing.
My only suggestion: Do not use Cells or Range references inside a loop.
Kad.
For editors corner: any comment or sugestion, be welcome
Comment