How to use Paramarray with arguments in pairs?

[Kelvin Stott]

Hello,

I want to create a VBA function to get the weighted average value of an unspecified number of data points, where each data point is given a certain weight, for example

Function WtdAve(Data1, Weight1, Data2, Weght2, ..., etc.)

I want to use Paramarray where each data point and its weight is given as a pair, but I am not sure how to do this. Could anyone please help point me in the right direction with an example.

Many thanks,
Kelvin

 

[b.downey]

You may want to consider storing the parameters Data1, Weight1.... as arrays and then just passing the array as the parameter. For example:

Function WtdAve(Data(), Weight())

This would allow the "Pairs" to be of almost any length.

In the function, you can determine how may pairs were transfer by using the ubound(Data) funtion.

 

[Rick Rothstein]

First thing, is you have to give your parameter array a name and place that into your function's argument list (I'll use DataWeights for the name, but you can change that if you want)...

Function WtdAve(ParamArray DataWeights()) As Double

Next, your code would need to do is make sure an even number of arguments were passed into the parameter array...

   If (UBound(DataWeights) - LBound(DataWeights)) Mod 2 = 0 Then
    '
    '  An odd number of arguments for the parameter array were
    '  passed in, so put the code, if any, to handle the error here
    '
    Exit Function
  End If

And then finally, you have to process the arguments in the parameter array two at a time (here I am assuming X is the loop counter variable)...

  For X = LBound(DataWeights) To UBound(DataWeights) Step 2
    '
    '  Here is where you process the arguments from the parameter array. On each
    '  loop, DataWeights(X) will contain the Data item and DataWeights(X + 1) will
    '  contain the Weight for that Data iteme
    '
  Next

 

[Kelvin Stott]

Thank you Rick,

Not only did you provide the solution to my original problem, you also solved problems I had not yet envisaged.

Cheers,
Kelvin