Passing a ParamArray from one Function to another Function

[Edlueze]

Greetings:

I have two functions and I would like to pass the ParamArray gathered
from one function to the other function. For the purposes of this post,
let's say that they are calculating averages (they're actually
processing a sequence of pairs of variants and the sequence is of
unknown length).

I want something like these two functions:

#1 Function AverageSequenceOfNumbers(ParamArray SequenceOfNumbers As
Variant) as Double
....
End Function

#2 Function AverageSequenceOfNumbersPlusOne(ParamArray
SequenceOfNumbers as Variant) as Double

AverageSequenceOfNumbersPlusOne = AverageSequenceOfNumbers(99,
SequenceOfNumbers)

End Function

The second function is brand new, but the first function is
tried-and-true code so I don't want to go back and strip out the
ParamArray in the first function and replace it with a regular Array.
In fact, I don't want to touch the first function at all (unless it is
really minor change). If there is not something obvious then is there a
work-around I can implement in the new second function?
Thanks,

Edlueze.

[Steve Jorgensen]

Alas, this is no easy task - in fact, it's pretty much intractible.

My response to this issue has been to stop using Paramarray arguments, and
instead, take a variant array. The Array function, then makes it easy to pass
a custom-built array of values in-line from calling code. That this seems to
have been the logic of the ADO API as well, since many ADO methods accept
Array values passed to variant arguments.

Example based on your example (untested air code):

Function AverageSequenceOfNumbers( _
SequenceOfNumbers As Variant _
) as Double
Dim varNumItem As Variant
Dim varSumOfNums As Variant
Dim varItemCount As Long

varSumOfNums = 0&

For Each varNumItem in SequenceOfNumbers
varSumOfNums = varSumOfNums + varNumItem
Next

varItemCount = UBound(SequenceOfNumbers) - _
LBound(SequenceOfNumbers) + _
1

AverageSequenceOfNumbers = _
varSumOfNums / varItemCount

End Function

Function AverageSequenceOfNumbersPlusOne( _
SequenceOfNumbers as Variant _
) as Double
Dim varSequencePlusOne As Variant
Dim lngLBound As Long
Dim lngUBound As Long

varSequencePlusOne = SequenceOfNumbers
lngLBound = LBound(varSequencePlusOne)
lngUBound = UBound(varSequencePlusOne)

Redim Preserve varSequencePlusOne(lngLBound To lngUBound+1)
varSequencePlusOne(lngUBound) = 99

AverageSequenceOfNumbersPlusOne = _
AverageSequenceOfNumbers(varSequencePlusOne)

End Function

On 20 Jan 2005 22:12:53 -0800, "Edlueze" <ed*****@onegen.com> wrote:

Greetings:

I have two functions and I would like to pass the ParamArray gathered
from one function to the other function. For the purposes of this post,
let's say that they are calculating averages (they're actually
processing a sequence of pairs of variants and the sequence is of
unknown length).

I want something like these two functions:

#1 Function AverageSequenceOfNumbers(ParamArray SequenceOfNumbers As
Variant) as Double
...
End Function

#2 Function AverageSequenceOfNumbersPlusOne(ParamArray
SequenceOfNumbers as Variant) as Double

AverageSequenceOfNumbersPlusOne = AverageSequenceOfNumbers(99,
SequenceOfNumbers)

End Function

The second function is brand new, but the first function is
tried-and-true code so I don't want to go back and strip out the
ParamArray in the first function and replace it with a regular Array.
In fact, I don't want to touch the first function at all (unless it is
really minor change). If there is not something obvious then is there a
work-around I can implement in the new second function?
Thanks,

Edlueze.

[Edlueze]

Thanks a lot Steve! You've taught me a lot about an area I knew little
about. I took your code for a test run and it worked perfectly first
time.

Based upon your example, I think I've come up with a way to preserve
backwards compatibility while adding the new functionality I need. It
essentially involves expanding the capability of the existing first
function to accept either a ParamArray or a regular Array, and then
have the new second function call the first with a regular Array - that
way I don't have to modify all of the other places the first function
is called from my application. This is done by building a consolidated
Array right at the top of the first function and then leaving the rest
of the code (hopefully) untouched.

I've tried to illustrate this below by modifying the original code you
provided me (changing the function names for the purpose of this post):

Function AverageSequenceOfNumbersFromParamArray( _
ParamArray SequenceOfNumbers() As Variant) As Double
Dim varNumItem As Variant
Dim varNumSubItem As Variant
Dim varSumOfNums As Variant
Dim varItemCount As Long
Dim SequenceOfNumbersArray() As Variant 'Builds an Array from the
parameters passed to the function

varItemCount = 0

For Each varNumItem In SequenceOfNumbers
If IsArray(varNumItem) Then
For Each varNumSubItem In varNumItem
ReDim Preserve SequenceOfNumbersArray(varItemCount)
SequenceOfNumbersArray(varItemCount) = varNumSubItem
varItemCount = varItemCount + 1
Next
Else
ReDim Preserve SequenceOfNumbersArray(varItemCount)
SequenceOfNumbersArray(varItemCount) = varNumItem
varItemCount = varItemCount + 1
End If
Next

varSumOfNums = 0&

For Each varNumItem In SequenceOfNumbersArray
varSumOfNums = varSumOfNums + varNumItem
Next

varItemCount = UBound(SequenceOfNumbersArray) - _
LBound(SequenceOfNumbersArray) + _
1

AverageSequenceOfNumbersFromParamArray = _
varSumOfNums / varItemCount

End Function

Function AverageSequenceOfNumbersPlusOneFromParamArray( _
SequenceOfNumbers As Variant _
) As Double
Dim varSequencePlusOne As Variant
Dim lngLBound As Long
Dim lngUBound As Long

varSequencePlusOne = SequenceOfNumbers
lngLBound = LBound(varSequencePlusOne)
lngUBound = UBound(varSequencePlusOne)

ReDim Preserve varSequencePlusOne(lngLBound To lngUBound + 1)
varSequencePlusOne(lngUBound) = 99

AverageSequenceOfNumbersPlusOneFromParamArray = _
AverageSequenceOfNumbersFromParamArray(varSequence PlusOne)

End Function

My results from the Immediate window were as follows:

? AverageSequenceOfNumbersFromParamArray(1,2,3,4,5)
3
?
AverageSequenceOfNumbersFromParamArray(Array(1,2,3 ,4,5),6,7,8,Array(9,10,11,12,13))
7
?
AverageSequenceOfNumbersPlusOneFromParamArray(Arra y(1,2,3,4,5,6,7,8,9,10,11,12,13))
12.6428571428571

Thanks for all your help! If there are any traps that I should be aware
of by taking this approach then please let me know.

Edlueze.