Why is this lookup function slow?

Question

I have written the following UDF function to return True if a cell value is found in a specified range and return False otherwise:

Function find_in_range(value_to_find As Variant, lookup_range As Range) As Boolean

For Each cell In lookup_range.Cells.SpecialCells(xlConstants)
    If cell.Value = value_to_find Then
        find_in_range = True
        Exit For
    Else
        find_in_range = False
    End If
Next cell

End Function

However, it is considerably slower than let's say VLOOKUP.

Why is that so? Is there a way to make it faster? What magic is used to make VLOOKUP search faster?

score 2 · Accepted Answer · answered Aug 02 '18 at 12:02

This is a way to make it a bit faster:

Public Function findInRange(valueToFind As Variant, lookupRange As Range) As Boolean
    findInRange = Not IsError(Application.Match(valueToFind, lookupRange, 0))
End Function

What magic is used to make VLOOKUP search faster?

Programming in C

Pᴇʜ · Answer 2 · 2018-08-02T12:34:29.123

1

Built in functions are in most cases (I would even say all) faster than VBA can ever be. They are already compiled and native code.

They can also use multi-threading where VBA can not. All such effects make them acting a lot faster. Also you won't want to re-invent the wheel. So I suggest to use built in functions where ever possible.

edited Aug 02 '18 at 12:34

answered Aug 02 '18 at 12:02

Pᴇʜ

56,719
10
49
73

Code written in VBA is compiled[4] to Microsoft P-Code (packed code), a proprietary intermediate language, ... from https://en.wikipedia.org/wiki/Visual_Basic_for_Applications – Vityata Aug 02 '18 at 12:09
@Vityata What I meant to say there, is that VBA needs to be compiled before you can run it, which takes time. Builtin functions don't need that extra compiling time. – Pᴇʜ Aug 02 '18 at 12:11
2

@PEH - VBA is actually compiled on the moment you write it on the VBA Editor. Still, the built-in functions are uncomparable in speed. See the last comments below this answer - https://stackoverflow.com/a/17253557/5448626 – Vityata Aug 02 '18 at 12:17
1

@Vityata Nice, good to know. Now that explains why you actually get a *compile error* on every single line immediate after typing and not when trying to execute the code. Makes totally sense now. Thanks for clarifying, always a pleasure. – Pᴇʜ Aug 02 '18 at 12:35
@PEH - you are welcome. Actually, this is one of the reasons why I personally consider that VBA to be hugely underestimated. – Vityata Aug 02 '18 at 12:38
1

@Vityata: I completely agree!. I think VBA (or Excel for that matter) is one of the most underrated `"tools"` out there. Also completely agree.. VBA is NOT a scripting language by any stretch of the imagination.. some interesting takes on languages in that thread though :) – Zac Aug 02 '18 at 13:54
1

@Zac - The underrating of VBA is something that everyone from the VBA world si used to. However, that thread was once used against me to show that VBA is a scripting language. I was speechless for a minute and I have tried to edit the thread later. The edit was not accepted anyway. – Vityata Aug 02 '18 at 13:57
2

@Vityata: I don't want to start a conversation on this question because we could go on for hours.. but good on you for standing your ground! loved it! – Zac Aug 02 '18 at 14:03

Samuel Hulla · Answer 3 · 2018-08-02T12:33:44.147

Unless you absolutely insist on poly-filling (in a non-literal sense), excel already has inbuilt function Find that returns the Range if found, or Nothing if not

You could further modify this, into a function that evaluates into a Boolean

Option Explicit
Function isFound(ByVal value_to_find As String, ByVal in_range as Range) As Boolean

 If in_range.Find(value_to_find, lookin:= xlValues) Is Nothing Then
    isFound = False
 Else
    isFound = True
 End If

End Function

Returns true if value can be found in range, otherwise returns false

In general, if you can do something without a loop it usually means it's faster

Why is this lookup function slow?

3 Answers3