Function with multiple parameters

Question

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:

user3598756 · Accepted Answer · 2016-11-05 18:21:18Z

4

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

answered Nov 5, 2016 at 18:21

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

Comments

omegastripes · Accepted Answer · 2016-11-05 18:39:37Z

0

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)

edited Nov 5, 2016 at 18:39

omegastripes

12.6k4 gold badges52 silver badges105 bronze badges

answered Nov 5, 2016 at 18:11

Steven Block

11 bronze badge

2 Comments

spacedOut Over a year ago

Yes, however when I choose the cells in excel, and divide the parameters with a comma, I still get an error. The cell where I want the function to be in says that the value is wrong. What I want to know is how can I manually enter three excel cell values as parameters to a vba function.

Steven Block Over a year ago

My understanding of the comma and simicolon difference is that some language settings use one, while others use the other. For the problem you are encountering, I would need to know which equations you are using, however, cells are sometimes formatted as text or some alternative format. The other thing that sometimes goes wrong is a divide by zero situation. Have you stepped through the calculations with the debugger?

Collectives™ on Stack Overflow

Function with multiple parameters

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related