"Compile Error" while Creating a Function with Multiple ParamArray

Question

I want to include a ParamArray in my function, but my efforts have failed so far.

Let's say I want to build a function to calculate mean for discrete distribution.

The function has the following form:

Function Dist_Discrete(X1, P1, X2, P2, X3, P3, etc.)

There are two solutions currently come to my mind:

1. Using optional arguments multiple of times.

2. Using ParamArray

The second one is preferred. But here is some problem when I try to define

Function Dist_Discrete(ParamArray XVal() As Variant, ParamArray Prob() As Variant)

And an error message comes up - "Compile error"

I can find a way to go around this by setting even as probability and odd as value. But I think this might be a good temporary solution

Answer 1

Your posted example shows the inputs as cells in a contiguous column like X1, X2. etc. If this is the case, the just use Range objects as the inputs:

Function Dist_Discrete(X as Range, P as Range) as Double

then in a worksheet cell:

=DistDiscrete(X1:X30, P1:P30)

Answer 2

ParamArray specifies that a procedure parameter takes an optional array of elements of the specified type. ParamArray can be used only on the last parameter of a parameter list. Thus 2 param arrays are not possible. (learn.microsoft.com)

However, you may think about a little trick in Excel. E.g. lets consider the following Function:

Function SumAndMultiply(valuesToSum(), valuesToMultiply()) as Double which has the idea to sum the values of the valuesToSum() array and to multiply the result with each of the valuesToMultiply().

In VBA, if the first one is presented as a range, then it would work quite nicely:

Public Function SumAndMultiply(valuesToSum As Range, _
        ParamArray valuesToMultiply() As Variant) As Double

    Dim myCell As Range
    Dim sum As Double
    For Each myCell In valuesToSum
        sum = sum + myCell
    Next myCell

    Dim param As Variant
    SumAndMultiply = sum
    For Each param In valuesToMultiply
        SumAndMultiply = SumAndMultiply * param
    Next

End Function