How to pass Excel Array Formula to VBA UDF?

Question

I have already looked at a similar solution here but somehow I still could not make my code work.

I want to pass an output of an Array Formula as Input to UDF, process it and return a single value from it. Say just add all items in an array and return the value as an example. My main question is how to pass an output of an Array Formula to UDF and process it inside the UDF.

'Public Function Test1(ParamArray Parm1() As Variant) As Integer
'Dim i, j
'i = 0
'For j = LBound(Parm1) To UBound(Parm1)
'
'    i = i + Parm1(j)
'
'Next j
'Test1 = i
'End Function

Public Function Test1(Parm1 As Variant) As Integer
Dim i, j
Dim tmparray() As Variant
tmparray = Parm1
For j = LBound(tmparray, 1) To UBound(tmparray, 2)
   i = i + tmparray(j)
Next j
Test1 = i   
End Function

Above commented code did not work. I tried to modify it by referring the mentioned solution as pasted just below it, but still I could not make it work.

In Excel Spreadsheet I am passing {=Test1(ROW(C1:C4))} as an Array Formula to this but it returns #VALUE!

In above code (The commented one) if I test and debug thru below sub it works fine but when called from an Excel Array Formula like {=Test1(ROW(C1:C4))}it returns #VALUE!

Sub check()

j = Test1(1, 2)

End Sub

Could someone help me further please?

There is a myriad of things that might be wrong. Have you tried stepping through the code? Do you get any errors - if so, what is the error and where is it? What does the array you're passing to the function look like? Read more here. — Vegard
– Vegard, Commented Nov 17, 2017 at 11:47
Thanks for the input. The array looks as returned by ROW(C1:C4) Excel Function in an array formula as mentioned above. So when I evaluate the function it looks like Test1({1;2;3;4}) — rajeev
– rajeev, Commented Nov 17, 2017 at 11:54
I'm not sure how you are accomplishing this? ROW(C1:C4) returns 1, not the array (1, 2, 3, 4). — Vegard
– Vegard, Commented Nov 17, 2017 at 12:30
Change this: For j = LBound(tmparray, 1) To UBound(tmparray, 2) to this: For j = LBound(tmparray, 1) To UBound(tmparray, 1). Note that it only works because ROW is returning a 2D array. If you used COLUMN similarly it would fail as you'd have a 1D array. — Rory
– Rory, Commented Nov 17, 2017 at 13:51

Vityata · Accepted Answer · 2017-11-17 12:00:07Z

1

This is how to make a UDF, summing all your numbers in a range:

Public Function AlternativeSum(Parm1 As Range) As Long

    Dim myCell  As Range
    Dim result  As Long

    For Each myCell In Parm1
        result = myCell.Value + result
    Next myCell

    AlternativeSum = result

End Function

You may consider changing the output to double or returning some specific info, if the input range does not consist of numbers. But in general it works:

In order to make it "your way", you should learn how to transfer range to array Array from Range in Excel VBA. It is a bit tricky. With a single line range, try like this:

Public Function Test1(Parm1 As Variant) As Integer

    Dim i           As Long
    Dim j           As Long
    Dim tmparray    As Variant

    tmparray = Parm1

    For j = 1 To 3
        i = i + Parm1(1, j)
    Next

    Test1 = i

End Function

edited Nov 17, 2017 at 12:00

answered Nov 17, 2017 at 11:49

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rajeev Over a year ago

Thanks for the answer. Summing is just an example. I am stuck at processing Array input in UDF which is an output of the Array Formula in Excel being passed as Input to this UDF.

rajeev Over a year ago

Did you mean 'i = i + tmparray(1, j)' here because Parm1 is already assigned to tmparray? Still when called as an Array Formula from Excel it returns #VALUE!.

Collectives™ on Stack Overflow

How to pass Excel Array Formula to VBA UDF?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related