CODEWITHSUNDEEP
excelvbafunctionarguments
spacedOut
spacedOut

Reputation: 47

Function with multiple parameters

I have a VBA function with multiple arguments, which I need to get from the excel sheet and not from a sub, how do I divide the inputs into the number of parameters i need for my function?

Ex:

Function func(a as double,b as double) as double
    'calculations
    'some return value
End Function

This is how I have been trying to get the values:

This is how I have been trying to get the values

Upvotes: 1

Views: 23854

Answers (2)

Steven Block
Steven Block

Reputation: 1

Is this what you want?

Code:

Function TestFunction(a As Double, b As Double) As Double

   TestFunction = a + b

End Function

Excel Sheet:

=TestFunction(A1,B1)

Upvotes: 0

user3598756
user3598756

Reputation: 29421

if you want to handle multiple arguments of which you don't know the number of, then use ParamArray argument

for instance, assuming func() should simply sum the arguments you pass it:

Function func(ParamArray args() As Variant) As Double
    Dim i As Long
    Dim cell As Range

    For i = LBound(args) To UBound(args) '<--| loop through each passed argument
        If TypeName(args(i)) = "Range" Then '<--| if the current element is a Range
            For Each cell In args(i) '<--| loop through range cells
                func = func + cell.Value
            Next cell
        Else '<--| otherwise
            func = func + args(i) '<--| simply process the current argument value
        End If
    Next i
End Function

Upvotes: 4

Related Questions