Re: Make Excel work faster

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi,

Turns out we were talking about different things. I meant lookups in a work*** range.
If the values in the range are stable, reading them into VBA once and interrogate from there is good practice; I use that in my
actuarial function system a lot. But if the values are dynamic, you'll have to pass the range to the function with each call. That
was the situation I was thinking of in my previous posts.

I tested a table of a complete column, 65536 sorted entries. I did 10,000 lookups with random keys, distributed through the entire
table.
VLOOKUPs from a work*** took 48 msec (using your timer).
I tried 3 VBA variations:

Function LookupFromWork***(a As Range, b As Double)
LookupFromWork*** = Application.VLookup(b, a, 1, True)
End Function

This took 468 msec.

Function lookupVBA(a As Range, b As Double)
Dim i As Long
For i = 1 To a.Count
If a(i, 1) = b Then
lookupVBA = a(i, 1)
Exit Function
End If
Next
End Function

just 100 lookups (not 10,000 as in previous tests) took 29703 msec.

Function LookupVBAWithArray(a As Range, b As Double)
Dim i As Long
Dim aArray
aArray = a
For i = 1 To a.Count
If aArray(i, 1) = b Then
LookupVBAWithArray = aArray(i, 1)
Exit Function
End If
Next
End Function

100 lookups took 2703 msec

So in this situation VLOOKUP from a work*** was impossible to beat, but your examples give a good demo of what can be achieved
in VBA.

I hope I will remember to be more specific next time, when stating what performs better!

Nice excercise, thanks!

--
Kind regards,

Niek Otten


"RB Smissaert" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message news:OULflFpWGHA.3492@xxxxxxxxxxxxxxxxxxxxxxx
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?






.

Quantcast