Re: Make Excel work faster

Niek,


OK, here is a tester for this:

Option Explicit
Public Declare Function timeGetTime _
Lib "winmm.dll" () As Long
Private lStartTime As Long

Sub StartSW()
lStartTime = timeGetTime()
End Sub

Sub StopSW(Optional strMessage As Variant = "")
MsgBox "Done in " & _
timeGetTime() - lStartTime & _
" msecs", , strMessage
End Sub

Sub LookupTester()

Dim i As Long
Dim c As Byte
Dim vResult As Variant

Dim arr(1 To 10000, 1 To 2) As Long

'populate the array
For i = 1 To 10000
For c = 1 To 2
arr(i, c) = i + c
Next
Next

Select Case MsgBox("Use VLookup?", _
vbQuestion + vbYesNoCancel + _
vbDefaultButton1, _
"array lookup tester")
Case vbYes
'with VLookup
StartSW
vResult = WorksheetFunction.VLookup(5000, _
arr, _
2, _
True)
StopSW "with VLookup"

MsgBox vResult

Case vbNo
'with array loop
StartSW
vResult = LookupArray(arr, 1, 2, 5000)
StopSW "with array loop"

MsgBox vResult
End Select

End Sub

Function LookupArray(arr As Variant, _
lSearchColumn As Long, _
lResultColumn As Long, _
vLookupValue As Variant) As Variant

Dim i As Long

For i = LBound(arr) To UBound(arr)
If arr(i, lSearchColumn) = vLookupValue Then
LookupArray = arr(i, lResultColumn)
Exit Function
End If
Next

End Function

Looks to me looping through the array is faster at least in this scenario, even although I have taken True for the
fourth argument. Looping seems at least twice as fast.
Let me know if I have not tested this properly.


RBS

"Niek Otten" <nicolaus@xxxxxxxxx> wrote in message news:%23CTw5jiWGHA.1564@xxxxxxxxxxxxxxxxxxxxxxx
<lookups by looping through these arrays in VBA. I find that this is often much faster.>

My experience is quite the contrary. It is practically impossible to get even near the speed of Excel's built-in (lookup) functions.
But using FALSE as 4th argument in VLOOKUP is disastrous for performance with large tables. There are several ways to avoid that.
Even double lookups (to check the values found) for sorted tables with the 4th argument TRUE or omitted can be several hundreds of times faster than using FALSE. Doing the double lookup or an INDEX/MATCH combination in a VBA function is hardly any faster than a VLOOKUP with 4th argument FALSE.
Of course Excel uses fast algorithms for searching in sorted tables, that is, 4th argument TRUE.

--
Kind regards,

Niek Otten



"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:ezuwW8hWGHA.1348@xxxxxxxxxxxxxxxxxxxxxxx
Other than re-designing the whole project (use a database?) you could try
not to work with lookup work*** functions, but get the *** ranges in
arrays and do the lookups by looping through these arrays in VBA.
I find that this is often much faster.
If those ranges can be sorted you could even make it faster by using
binary search algorithm's on the arrays, rather than simply looping from
lbound to ubound.

RBS

"Siva" <Siva Govender@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3306E169-01A5-4269-9165-3292C2AA9149@xxxxxxxxxxxxxxxx
I have a file, that is +- 200MB in size, and has +- 150 worksheets with 700
lines of formulaes in each work***.

Problem is that when I update the one *** where the lookup is, it takes
about 10min to do the calculation.

I am also running a PIV 3Ghz HT with 1gig RAM.

How can I get Excel to perform faster?




.