Return to Revisions

Volatile

Application.Volatile

I'm walking down the code as I'm writing this, so I haven't really seen the code below that line. The Application.Volatile method call tells Excel to recalculate your function every time any cell anywhere on the worksheet is modified. The default behavior is to recalculate only when the input variables change.

That method call on the Application object tells me that you've perhaps tested it without at first, and concluded that it was required in order for the function to properly recalculate.

A comment explaining why Application.Volatile is needed here, might be judicious.

Naming notes...

I like the consistency you have with your naming style, it's nice to see. The style matches that of native Excel worksheet functions, like SUM:

SUMIF(range, criteria, [sum_range])

I think it's fine to expose that style to outside code (and/or Excel), but I don't see a reason for the internals to use any snake_case, convention is to use camelCase for locals (and usually, parameters too), and PascalCase for member names (procedures, functions, properties, ..).

Dim i(0 To 1) As Long, j As Long, lookup_vector_values As Variant

Hmm. 3 variables declared in the same instruction, of 3 different types and meanings. For readability's and maintainability's sake, a few points:

• Declare arrays on their own line. As Long, j As Long is pretty much hiding the identifier.
• If you're going to declare more than one variable in one instruction, do so but with variables of the same type.
• Use meaningful identifier names: i is usually used in a For..Next loop, to count iterations. An array of Int32's isn't exactly trivially that. We don't know anything about that variable until we stumble on a usage of it, which hopefully isn't too far. The first usage is actually 2 lines below, which isn't too bad, but... let's face it, we all hate it when it's down to i and l and 1.
• j is also typically used for iterating Foo..Next loops, usually when i is already taken. I'll have to scroll further down to see where the loop is. An interesting loop, I'll get back to it.
• Declare variables as close as possible to their first usage.

I'd get rid of i, and rename lookup_vector to lookup_range or, as VLOOKUP has it, table_array (although, ...)

I said I was going to get back to the loop:

For i(j) = 1 To UBound(lookup_vector_values, j + 1)

Why make that operation a side-effect of iterating the loop? It's non-standard; people usually use a variable in a For loop, not an expression - one must read it carefully. If it can't be refactored, then it should be commented.