"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:

Using optional arguments multiple of times.
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

ParamArray can only be used for the last parameter of a parameter list, which implies you can only have one. — Brian M Stafford
– Brian M Stafford, Commented Aug 19, 2019 at 14:01
if more than one ParamArray were somehow allowed, there would be no way to guess where the first one ends and the second one starts — Slai
– Slai, Commented Aug 19, 2019 at 14:14

Gary's Student · Accepted Answer · 2019-08-19 14:07:50Z

4

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)

answered Aug 19, 2019 at 14:07

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

1 Comment

PTQuoc Over a year ago

Thank you, I think you are right. I was overthinking and complicating the problem

Vityata · Accepted Answer · 2019-08-19 14:11:27Z

4

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

answered Aug 19, 2019 at 14:11

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

PTQuoc Over a year ago

thank you so much. That's right. I was overthinking and complicating the problem :(

Collectives™ on Stack Overflow

"Compile Error" while Creating a Function with Multiple ParamArray

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related