CODEWITHSUNDEEP
arraysexcelvba
rajeev
rajeev

Reputation: 137

How to pass Excel Array Formula to VBA UDF?

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?

Upvotes: 1

Views: 906

Answers (1)

Vityata
Vityata

Reputation: 43575

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

Upvotes: 1

Related Questions