Excel VBA: Calling Named Function with Parameters in UDF

Question

I have a named function in my workbook named "ReadRaw" with two input parameters, doing a 2D index-match lookup using lambda, defined as below:

=LAMBDA(runPoint,header,INDEX(Sheet1!$A:$X),XMATCH(runPoint,Sheet1!$A:$A),2,XMATCH(header,Sheet1!$4:$4"),2)

It works as expected when defined in a cell like so:

=ReadRaw(C4,C2)

I'd like to use the function to calculate the average of multiple calls of "ReadRaw", avoiding having a formula that looks like this because my input for the second parameter will be a larger range.

=AVERAGE(ReadRaw(C4,C2), ReadRaw(C4,D2), ReadRaw(C4,E2))

Calling =AVERAGE(ReadRaw(C4,C2:D2) doesn't work. and I tried creating a new named function "AverageReadRaw" without success as well:

=LAMBDA(runPoint,header,AVERAGE(ReadRaw(runPoint,header)))

So my next thought was to create a UDF in VBA that loops through the input range and calls "ReadRaw" on each cell and computes the average. Is it possible to call the named function from VBA?

Function AverageReadRaw(runPoint As Long, header As Range) As Variant
    Dim RangeLength As Integer
    Dim productVal As Long

    RangeLength = header.Count()
    productVal = 0#
    
    ' Run the RearRaw formula for each value in the range
    For Each i In header
        productVal = productVal + **ReadRaw(runPoint, i)**
    
    AverageReadRaw = productVal / RangeLength
    
End Function

The ReadRaw call is what I'm not sure how to define. I've tried Evaluate, CallByName, Application.Run without success.

Answer 1

Going back a step, if you wanted to call ReadRaw from your sheet with an array you would have to allow it to process arrays in your function definition. So to allow the header part of it to be an array, the body of your lambda could be:

=MAP(
    header,
    LAMBDA(c, INDEX(Sheet1!$A:$X, XMATCH(runpoint, Sheet1!$A:$A, 2), XMATCH(c, Sheet1!$4:$4, 2)))
)

Here is a mini example:

To be more explicit, the new function ReadRaw2 appears in the name manager as

=LAMBDA(runpoint,header,MAP(header, LAMBDA(c, INDEX(Sheet1!$A:$X, XMATCH(runpoint, Sheet1!$A:$A, 2), XMATCH(c, Sheet1!$4:$4, 2)))))

Answer 2

You can call a named formula from VBA using Evaluate(). However, you will need to grab the named formula and add the parameters to form a single string. For example:

  productVal = productVal + Evaluate("ReadRaw(" & Cstr(runPoint) & "," & Cstr(i.value) &")")

This will create a string that looks like "ReadRaw(9,3)" which the Evaluate function can then calculate.