loading a variant array

[offthelip]

I have been writing code for a lifetime and using Excel VBA for 20 years, however it is only recently after joining this forum that I came across the idea that using select was bad idea.
I can certainly agree that using select in a poor way can make macros very slow indeed. However I have just been trying to see what the difference in timing was between the way I tend to write my code and a common method which avoids using select.
My method of getting my macros to run as fast as possible is to load all of the data from any number of worksheets into memory, then do the calculations in memory and then paste them back .
Note: I have still had macros that take hours to run usually because of multiple loops which can end up with millions of iterations
I had this feeling that operating on variant arrays in memory was going to be a lot faster than operating on range variables. This proved to be correct in my test. Selecting the worksheet and loading a variant array took 0.34 seconds.
Using methods of setting a variable to worksheet and range took 18.12 seconds that is over 50 times as long.
So my question is how would you alter my code to get it to run faster or is the best way of doing what I am doing which is selecting the worksheet to load the variant array? Is there another way of loading a variant array without selecting or activating the worksheet?

The slow routine:

Dim SecondsElapsed As Double
Worksheets("Sheet1").Select


StartTime = Timer
Dim ws As Worksheet
Dim rng2 As Range


  Set ws = Worksheets("Sheet2")
        lastcol2 = 100
        lastrow2 = 5000
    Set rng2 = ws.Range(ws.Cells(1, 1), ws.Cells(lastrow2, lastcol2))
For i = 1 To 5000
 For j = 1 To 10
  rng2(i, j) = i + j
 Next j
Next i
For i = 1 To 5000
 For j = 1 To 10
  rng2(i, j) = rng2(i, j) + 1
 Next j
Next i


SecondsElapsed = Round(Timer - StartTime, 2)


'MsgBox SecondsElapsed
Cells(1, 1) = "Setting worksheet variable"
Cells(1, 2) = SecondsElapsed
End Sub

the fast routine:

Sub speedtest()
Dim StartTime As Double
Dim SecondsElapsed As Double
Worksheets("Sheet1").Select


StartTime = Timer
Dim var2 As Variant


   Worksheets("Sheet2").Select
        lastcol2 = 100
        lastrow2 = 5000
    var2 = Range(Cells(1, 1), Cells(lastrow2, lastcol2))
For i = 1 To 5000
 For j = 1 To 10
  var2(i, j) = i + j
 Next j
Next i
For i = 1 To 5000
 For j = 1 To 10
  var2(i, j) = var2(i, j) + 1
 Next j
Next i
Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2
Worksheets("Sheet1").Select


SecondsElapsed = Round(Timer - StartTime, 2)


'MsgBox SecondsElapsed


Cells(2, 1) = "Selecting worksheet"
Cells(2, 2) = SecondsElapsed
End Sub

[mdmackillop]

I'll be checking this out, meantime I don't know if you were aware of this code for accurate timing which you might find useful

[mdmackillop]

Using the Microtimer 80-90% of the time (Tim4 - Tim 3) is incurred in writing the result

 '
 ' COPYRIGHT © DECISION MODELS LIMITED 2006. All rights reserved
 ' May be redistributed for free but
 ' may not be sold without the author's explicit permission.
 '
Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
 
Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
 
Function MicroTimer() As Double
     '
     ' returns seconds
     '
    Dim cyTicks1 As Currency
    Static cyFrequency As Currency
     '
    MicroTimer = 0
    If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
    getTickCount cyTicks1 ' get ticks
    If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds
     
End Function


Sub speedtest()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Worksheets("Sheet1").Select
      
    Tim = MicroTimer
    Dim var2 As Variant
          
    Worksheets("Sheet2").Select
    lastcol2 = 100
    lastrow2 = 5000
    var2 = Range(Cells(1, 1), Cells(lastrow2, lastcol2))
    
    Tim1 = MicroTimer
    For i = 1 To 5000
        For j = 1 To 10
            var2(i, j) = i + j
        Next j
    Next i
    Tim2 = MicroTimer
    For i = 1 To 5000
        For j = 1 To 10
            var2(i, j) = var2(i, j) + 1
        Next j
    Next i
    Tim3 = MicroTimer
    Range(Cells(1, 1), Cells(lastrow2, lastcol2)) = var2
    Tim4 = MicroTimer
    Worksheets("Sheet1").Select
     
    Cells(2, 1) = "Tim"
    Cells(2, 2) = Tim
    Cells(3, 1) = "Tim1"
    Cells(3, 2) = Tim1
    Cells(4, 1) = "Tim2"
    Cells(4, 2) = Tim2
    Cells(5, 1) = "Tim3"
    Cells(5, 2) = Tim3
    Cells(6, 1) = "Tim4"
    Cells(6, 2) = Tim4
    
End Sub

[mdmackillop]

With 3 runs of each, this was 15% faster

With Worksheets("Sheet2")
    lastcol2 = 100
    lastrow2 = 5000
    var2 = Range(.Cells(1, 1), .Cells(lastrow2, lastcol2))
    
    Tim1 = MicroTimer
    For i = 1 To 5000
        For j = 1 To 10
            var2(i, j) = i + j
        Next j
    Next i
    Tim2 = MicroTimer
    For i = 1 To 5000
        For j = 1 To 10
            var2(i, j) = var2(i, j) + 1
        Next j
    Next i
    Tim3 = MicroTimer
    Range(.Cells(1, 1), .Cells(lastrow2, lastcol2)) = var2
    Tim4 = MicroTimer
    Worksheets("Sheet1").Select
    End With

[SamT]

Sub speedtest() 
    Dim StartTime As Double 
    Dim SecondsElapsed As Double 
    Worksheets("Sheet1").Select 
    
With Sheets("Sheet2")
'create UsedRange
   .Cells(1,1) = "X"
   .Cells(5000,100) = "X"
     
    StartTime = Timer 
    Dim var2 As Variant 
    
    var2 = .UsedRange
     For i = 1 to .UsedRange.Rows.Count
        For j = 1 To .UsedRange.Columns.Count
            var2(i, j) = i + j 
        Next j 
    Next i 
.Cells(1,1).Resize(UBound(var2, 1), Ubound(Var2, 2)) = Var2
End With

'Continue as before

[offthelip]

Thanks very much for your suggestions, MDMackillop's is definitely the winner and I do like the micro timer for getting accurate breakdowns when trying to speed up macros ( which I often seemed to be doing).
Using the "usedrange" solution took 13 seconds on my computer so nearly as bad as setting a worksheet and range variables, and certainly no where near as good a selecting the worksheets.
The microtimer runs on my machine showed me that writing the array took about 70% of the time.

I also used the microtimer to find out how long it takes to select "worksheet 2" and the answer is 0.012 seconds

so my conclusions are:
1: That setting variables to ranges is a very slow way to do a lot of calculations and should be avoided if timing is an issue
2: Using worksheet.select is very fast when used with loading a variant array.
3: Using "With worksheet" and .cells construct is very slightly faster and avoids any problems in using select worksheet and so is the best way

thanks for your help.

[mdmackillop]

I found Sam's code to be about 25-30% quicker if the target sheet is empty but much slower running on a populated sheet
Try this; To Tim1 I'm measuring only 0.0107, so clearing is very quick.

tim = MicroTimer
    Dim var2 As Variant
    With Sheets("Sheet2")
        'create UsedRange
        .Cells.Clear
        .Cells(1, 1) = "X"
        .Cells(5000, 10) = "X"
        Tim1 = MicroTimer

Note: I added in the loop missing from Sam's code

[offthelip]

If the used range is zero I would expect it to be fast, since I tried Sam's code after having run my routine, sheet2 was already populated which would be typical of my applications. Which explains why it was slow.
I tried the clear cells and it was 0.03 seconds on my computer with the full range 10 to 5000 populated, so that is a very fast way of clearing a destination. A useful tool.

In almost all the applications I have written the destination has lots of data usually hundreds of rows and columns and sometimes thousands. This is why timing is critical. My test routine was just an example to execute a arithmetical calculation on every cell in a range and update each cell.