function that takes range as an argument -vba

[lezawang]

Hi I created vba function that takes 3 numbers and add them and then multiple by 1.13. Everything fine if I say

= foo(a1,a2,a3)

but I got #Value if I type

= foo(A1:A3)

Why is that? Thank you.
+++

Function foo(x As Integer, y As Integer, z As Integer) As Double
foo = (x + y + z) * 1.13
End Function

 

[lrobbo314]

If you want to add a range instead of individual number arguments, you could write it like this.

Function PointOneThree(r As Range)
PointOneThree = Application.WorksheetFunction.Sum(r) * 1.13
End Function

 

[JoeMo]

A1:A3 is a range object not a series of three integers. You could do it like this:

Function foo(R as range) as double
foo = Application.Sum(R)*1.13
End Function

Then use it as =foo(A1:A3)

 

[mikerickson]

=foo(A1:A3) is passing one argument (the range A1:A3) rather than the required 3.

Try using a paramarray.

Function Foo2(ParamArray aterm() As Variant) As Double
    Dim i As Long
    Dim oneSubTerm As Variant
    
    For i = 0 To UBound(aterm)
        Select Case TypeName(aterm(i))
            Case "Range"
                For Each oneSubTerm In aterm(i).Cells
                    Foo2 = Foo2 + Val(CStr(oneSubTerm.Value))
                Next oneSubTerm
            Case "Double", "Single", "Integer", "Byte", "Long"
                Foo2 = Foo2 + aterm(i)
            Case Else
            
        End Select
        
    Next i
End Function