User-defined functions in array-formulas

Question

I have created an array formula in two different forms, using:

1. an Excel function
2. an equivalent, but not identical, user-defined function (in VBA).

The first form works perfectly well, but the second returns a #Value! error.

Is it possible to have a user-defined function in an array formula at all?

If so, what are the rules for writing it in VBA?

Answer 1

Using arrays and VBA functions requires a bit more work than that. There's not a lot of guidance out there for single cell array returns, but generally you need to return an array from your function, which means your return type needs to be a variant, and the array orientation that you return depends on the orientation of the range that you want to compare it against, so you need to check that. Bol_Cure_Event and BoI_Cur_Date can either be named ranges or comma delimited arrays - this should work in your Formula 2.

Function IsLike(strVal1 As String, strVals2 As Variant) As Variant

Dim boolLike() As Boolean

Dim i As Integer

If TypeOf strVals2 Is Excel.Range Then

ReDim boolLike(1 To strVals2.Cells.Count)

For i = 1 To strVals2.Cells.Count

boolLike(i) = strVals2.Cells(i).Value Like strVal1

Next i

If strVals2.Rows.Count = 1 Then

IsLike = boolLike

Else

IsLike = Application.Transpose(boolLike)

End If

Else

ReDim boolLike(LBound(strVals2) To UBound(strVals2))

For i = LBound(strVals2) To UBound(strVals2)

boolLike(i) = strVals2(i) Like strVal1

Next i

IsLike = boolLike

End If

End Function

Answer 2

Bernie, this looks like a very thorough and systematic reply. I shall try it out and, hopefully, mark it as the answer - not just helpful.

Many thanks.

Answer 3

Yes it is possible - but if you want help, post your formula from 1. and your code from 2.

Formula 1: {=MAX(IF(ISNUMBER(SEARCH("meteor",BoI_Cur_Event)), BoI_Cur_Date))}

• Note: The brackets do not copy; I entered them by hand here.

Formula 2: ={MAX(IF(IsLike("*meteor*",BoI_Cur_Event), BoI_Cur_Date)}

Code:

• Function IsLike(string1 As String, string2 As String) As Boolean
• 'Matches two strings regardless of where wild characters appear
• IsLike = (string1 Like string2) Or (string2 Like string1)
• End Function

Any suggestions will be gratefully received. But is there no "official" guidance on how to write VBA functions that will work equally well in array-formulas?

Answer 4

Yes it is possible - but if you want help, post your formula from 1. and your code from 2.