PDA

View Full Version : [SOLVED] loading a variant array



offthelip
08-04-2017, 07:19 AM
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
08-04-2017, 08:11 AM
I'll be checking this out, meantime I don't know if you were aware of this code for accurate timing (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1068) which you might find useful

mdmackillop
08-04-2017, 08:32 AM
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
08-04-2017, 08:40 AM
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
08-04-2017, 11:45 AM
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
08-04-2017, 03:06 PM
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
08-05-2017, 03:24 AM
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
08-05-2017, 04:14 AM
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.