1

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?

Community
  • 1
  • 1
rajeev
  • 137
  • 5
  • 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](https://stackoverflow.com/help/mcve). – Vegard Nov 17 '17 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 Nov 17 '17 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 Nov 17 '17 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 Nov 17 '17 at 13:51

1 Answers1

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:

enter image description here

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
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 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 Nov 17 '17 at 11:55
  • 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!. – rajeev Nov 17 '17 at 12:08