Orienting UDF array output

[N1GHTEYES]

I recently figured out this technique, partially from some comments on this forum, and have found it handy, so I thought I'd share it.

It concerns returning arrays from user-defined-functions (UDFs). In particular, how they are oriented.

If you create a function which returns an array, this can be entered on a worksheet across a range of cells as an array formula. For example, you could create a function which, when supplied with an input range of cells, and a string, returned an array consisting of the contents of all of those cells in the input range which are "Like" the string, using the VB Like function.

As an example, lets say you have text data in the cell range A1 to B7. Three of the cells match "*abc*". You enter your function (as an array formula), with the arguments (A1:B7,"*abc*") in cells D1 to Q1.

You would expect to get the matching cells' values replicated in cells D1 to F1. The rest of the output range would have "N/A".

That is great, but suppose it is more convenient for you lay out your spreadsheet so that the output range is not D1 to Q1, but D1 to D14?

If you enter the same function in that range, the first cell, D1, will be correct, but D2 to D14 will all contain the same value as D1. In other words, the orientation of the output array and the range where it is called do not match, so only the first value from the array appears in the calling range. You can solve this problem by using the TRANSPOSE function on the worksheet when calling the array formula in the first place, but it would be neater to put some code in your UDF to detect which orientation it ought to be, and alter the orientation of the output array accordingly.

That, however is a bit tricky, because the code which runs the function has no obvious reference to the range of cells which called it. There is a reference to the range containing the source data (A1:B7), but this is not where the output goes. So how do we do it?

The answer is to use the Application.Caller method. This supplies information about how Visual Basic was called. For details see Caller in the help file.

Below is some code which hopefully clarifies the foregoing.

Here is a sample function which returns an array:

Public Function AL_LikeList(inlist As Range, checkstring As String) As Variant
'***********************************************************
'  1. FUNCTIONAL DESCRIPTION of AL_LikeList
'
'       This function generates an array output consisting 
'       of the values in those cells in "inlist" which 
'       are "like" the "checkstring".  It can therefore be  
'       used as part of an array formula to select and 
'       display multiple cells from a list which are "like" 
'       a given string.
'       The "like" comparison uses the same rules as the VB
'       (and VBA) code, i.e. ? equates to a
'       single character, * to multiple, contiguous 
'       characters etc.
'
'  2. REFERENCES - None
'
'  3. INPUTS
'
'     inlist - Variant
'     checkstring - string
'
'  4. RETURN VALUE
'     Variant (array) - The content of all the cells in 
'     inlist matching checkstring
'***********************************************************Dim X As Range
Dim matches As Long
Dim insize As Long
insize = inlist.count
ReDim oplist(1 To insize) As String

matches = 0
For Each X In inlist
    If X.Text Like checkstring Then
        matches = matches + 1
        oplist(matches) = X.Text
    End If
Next X

If matches >= 1 Then
    ReDim Preserve oplist(1 To matches) As String
    AL_LikeList = oplist()
    Orientate AL_LikeList
Else
    AL_LikeList = Null
End If
End Function

In case you're wondering, AL are my initials. All my UDFs start that way, partly so they come high in the function selection list box, and partly so my colleagues know who blame.

Anyway, note the line "Orientate AL_LikeList". This is the line which checks the orientation of the calling array and causes the orientation of the output to be matched to it.

The code for "Orientate" is here:

Public Sub Orientate(rng As Variant)
'This sub checks whether the code currently running was initially called by an array of cells.
'If it was, the orientation of the range "rng" is changed (if necessary) to match that of the calling range.
If CalledByArray Then
    If CallingArrayRows > 1 Then
        rng = WorksheetFunction.Transpose(rng)
    End If
End If
End Sub

This in turn uses "CalledByArray" and "CallingArrayRows". These are listed below:

Public Function CalledByArray() As Boolean
'This function indicates whether the cell from which it was called was a single cell or
'part of an array (as in the use of array formulae).

If TypeName(Application.Caller) = "Range" Then
    CalledByArray = Application.Caller.Cells.count > 1
Else
    CalledByArray = False
End If
End Function

Public Function CallingArrayRows()
'This function returns the number of rows in the array from which it was called.
'If it was not called by an array formula, it returns zero.
If TypeName(Application.Caller) = "Range" Then
    Dim rng As Range
    Set rng = Application.Caller
    If rng.Cells.count > 1 Then
        CallingArrayRows = rng.Rows.count
    Else
        CallingArrayRows = 0
    End If
Else
    CallingArrayRows = 0
End If
End Function

The upshot is that, if you simply add the line "Orientate <funcname>" to the end of any UDF <funcname>, it will automatically orientate your output correctly.

By the way, I know I write ugly, badly commented, error-vulnerable code. And there are lots of other things wrong with it too! So if you feel like telling me the above, don't bother - I already know. If you want to show how to improve it, feel free - all suggestions gratefully received.

Tony

 

[xlbo]

Interesting post Tony - don't have a use for it at the mo but I can certainly see some possibilities. One for my archive I think!

Thanks for taking the time to post this useful info

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[N1GHTEYES]

Glad you liked it.

If you do decide to use it, you might want to change the line:
'***********************************************************Dim X As Range

to

'***********************************************************
Dim X As Range

They look the same in the preview window. Or should that be doh!

There are lots of similar potential uses for this idea. For example, I have implicitly assumed that the output array is 1D (basically a simple list), but similar code could just as easily check the actual dimensions of the calling range and shape the output array to fit.

It seems to me there must be lots more useful things which could be done with this technique. Anybody else got any ideas?

Tony