custom function in Excel VBA which takes an array as an argument

[monkey424]

Hello. I want to make a custom function in Excel VBA which takes an array as an argument. The calculation I want performed, for example, is:


=SUMIFS(C16:C22,A16:A22,"<"&H3+4,E16:E22,"=1")/4


In the above example, I'd like to just pass the array A16:E22 rather than three separate columns (i.e. A16:A22, C16:C22, and E16:E22). I need to perform the calculation multiple times and pass different size arrays (always 5 columns wide from A to E, but varying in height with a different start and end row).

Calling the function from the Excel formula bar might look something like this:

=CUSTOM_FUNCTION(A16:E22, H3, 1)/4

Thanks

[Paul_Hossler]

not tested but something like this

Option Explicit
' =SUMIFS(C16:C22,A16:A22,"<"&H3+4,E16:E22,"=1")/4
'Calling the function from the Excel formula bar might look something like this:
 '=CUSTOM_FUNCTION(A16:E22, H3, 1)

Function ph_1(r As Range, x As Range, n As Long) As Variant
    Dim r1 As String, r2 As String, r3 As String
    
    Set r1 = r.Columns(3)
    Set r2 = r.Columns(1)
    Set r3 = r.Columns(5)
    ph_1 = Application.WorksheetFunction.SumIfs(r1, r2, "<" & (x.Value + 4), r3, "=" & n) / 4
End Function

[monkey424]

Thanks Paul.

It works! ...except r1, r2 and r3 needed to be declared as Ranges, not Strings.

Would it be possible to have some reminder of the inputs pop up when typing the function in the formula bar?

[Paul_Hossler]

Yes, my mistake leaving them as strings. I started as Ranges, decided (wrongly) that I needed addresses to build the function string, byt then realized that the parameters needed to be ranges after all

Yes -- sample attached using Application.MacroOptions and Shift-F3

Capture.JPG

Although it really helps to have user meaningful names for the input variables

Option Explicit
Function Hello(a As String, b As String, c As String) As String
Hello = "Hello" & vbCrLf & a & vbCrLf & b & vbCrLf & c
End Function

Sub AddOptions()

Application.MacroOptions "Hello", "My Test Function", , , , , , , , , Array("A Parm", "B Parm", "C Parm")
End Sub

I usually put things like 'AddOptions' in a Workbook_Open of ThisWorkbook