Making use of array formulas in UDFs

[ghr]

I love the power of array formulas in spreadsheets, however my question is:

Can I utilise this same power in a pure VBA User-defined Function?

I have had a good look but can't seem to find how to do it?

In my current use case, I want to do an INDEX/MATCH multi-value lookup. This screen shot shows a very simplified version of my actual spreadsheet:

The magic I am trying to emulate happens in the shaded part of the formula in the above image: "A2:A22&B2:B22" which takes the two arrays and returns a single array with each item equal to the concatenation of the corresponding item in the original two arrays.


If I have two arrays of same length in VBA, I can't find how to make this same magic happen.


Note that I do NOT want to construct a string formula and place it in a spreadsheet. My goal is to do everything in VBA and just return the found value.


I know I can easily do this with For loops, but the data table can become very large (many tens of thousands of rows) and I want to make use of the lovely optimised code built into the guts of Excel rather than slow VBA loops.


I need this function to work as fast as possible as I will be using it to create other large tables with potentially thousands of uses of the function and that's why I am exploring all the options.


Basically I was hoping this simple looking code would work (but it fails):

Function LookupDepartmentName(department As String, firstName As String)
    'Trying to convert {=INDEX(C2:C22,MATCH(F3&F4,A2:A22&B2:B22,0))} to VBA...
    LookupDepartmentName = WorksheetFunction.Index(ActiveSheet.Range("C2:C22"), WorksheetFunction.Match(department & firstName, ActiveSheet.Range("A2:A22") & ActiveSheet.Range("B2:B22"), 0))
End Function

I have a VLookup version working but it requires an extra column to be added to pre-calculate the lookup keys and I'm trying to avoid having to do that:

Function LookupDepartmentName(department As String, firstName As String)
    'NOTE: This assumes a different spreadsheet from that shown above
    '  It would need a pre-calculated column of lookup keys inserted before column A
    LookupDepartmentName = WorksheetFunction.VLookup(department & firstName, ActiveSheet.Range("A2:D22"), 4, False)
End Function

So, is there a way to perform these magic array formulas in pure VBA?

Note: Cross-posted here

 

[Domenic]

Try...

Function LookupDepartmentName(department As String, firstName As String)
    LookupDepartmentName = Application.Evaluate("INDEX('Sheet1'!C2:C22,MATCH(""" & department & firstName & """,'Sheet1'!A2:A22&'Sheet1'!B2:B22,0))")
End Function

Hope this helps!

 

[ghr]

Hi Domenic, thanks for your assistance. This does indeed work.

However, because I'm trying to wring every last bit of performance out of this UDF (as it needs to be duplicated one to several thousand times per sheet) the complex string concatenation required each time through (which will be even more involved when I need to supply four or more keys) will no doubt bog it down.

Am off to bed now but will try it in a larger example to see how it goes tomorrow.

I am in search of all the UDF performance tricks where I can hand off expensive/repetitive calls to the optimised Excel internals.

 

[mikerickson]

In this case, the best way to up performance would be to add a helper column concatenating A and B, that way you could use native Excel, without array formulas, which is the fastest way to approach any problem.

Array formulas are wonderful, but they are often a replacement for helper columns. Array formulas are often a trade off between performance and screen space (although hiding helper columns helps the screen space)

 

[Fazza]

In my experience, for best performance array formulas would be the last thing I'd expect to use in any situation. Consider measuring execution times to check different approaches.

 

[ghr]

mikerickson, yeah, currently the most performant method is a VLOOKUP with a helper column but I'm trying to see if there is an even faster method.

Do you know if Excel allows VBA code to do the equivalent of "A2:A22&B2:B22" (as explained in my original post) which I would bet is very fast?

It would certainly be a lot faster than looping through tens of thousands of items in 2, 3, 4 or more arrays.

 

[ghr]

Fazza, I will certainly be timing the different methods.

My belief is that this magic ("A2:A22&B2:B22") would be very speedy for my purposes, but I have not been able to find out how to do it using VBA. Given it's available for Excel functions, I would be surprised that it would not be possible with VBA.

Let me ask you as well: Do you know if VBA can do this? If so, what is the syntax?

 

[Fazza]

I have no idea if VBA can do that: though like you I would be surprised that it would not. And again, I would be surprised if it was the most efficient approach.

With many tens of thousands of data rows, suggest you start a thread explaining the real situation & inviting fast solutions: the inputs & what needs to be done. Anticipate approaches without formulas being fastest.

PS. IIRC there are old forum posts where array formulas on large datasets took an hour, and even many (6 IIRC) hours, when other approaches took less than a second.