ParamArray values not being passed, Feb08

[Neal Zimm]

Hi All,
Am trying to learn about this type of array. I have successfully passed
other arrays to/from Sub's and functions, but clearly I'm missing something
pretty fundamental.

1. I've extracted what I hope are the pertinent lines of code in my
testing. Why is the paramarray not passing properly ? I've read MSo help a
couple of times and other postings here, but still can't 'see' it.

2. What is the "value added" of using a ParamArray versus putting:
Optional AnyVarName as variant = vbEmpty
in a Sub or Function statement and then valuing it as needed ?

Thanks much,
Neal


Sub Learn()

Dim vDCcArgAy() As Variant 'the ParamArray to be, I use option base 1
Dim uDraw as uDraw
Dim sAC as string

ReDim vDCcArgAy(3)
vDCcArgAy(3) = "test"

' in immediate window parm ay here is OK

uDraw = uDrawTESTmakeF 'the record is being made is OK

sAC = sAC_vuDrawF(uDraw, False, vDCcArgAy)

debug.print sAC

end sub


Public Function sAC_vuDrawF(Rec As uDraw, bAddAcctDelim As Boolean,
ParamArray vDCcArgAy()) As String

'Output: One account string from one uDraw record.

' Ubound of vDCcArgAy here is zero, and not being passed to next function,
???

'NOTE: "..." below = other rec. fields not shown to simplify
sAC_vuDrawF = sAC_ViaVarsF(Rec.ACN, Rec.DlvCd, "..." , vDCcArgAy)

End Function

 

[Tim Williams]

That's not how paramarray works. See example below:

'**********************************
Sub TestParam()
Debug.Print PTest("ParamArray ", "one", "two", "three")
End Sub

Function PTest(s As String, ParamArray PA()) As String

PTest = s & "=" & Join(PA(), ", ")

End Function
'**********************************

If you just need a function which takes an array as one of its arguments
then just declare it as (eg.) a variant parameter.


Tim

 

[JMB]

Your paramarray has a ubound of 0 because you only passed one argument to it
- which happens to be an array.

Paramarray could be useful if you were passing an unknown number of arrays
to your function.

Try this simplified example:

Sub Learn()
Dim x As Variant
x = Array("One", "Two", "Three")
y = Array("Four", "Five", "Six")
z = sAC_vuDrawF(True, x, y)
End Sub


Public Function sAC_vuDrawF(bAddAcctDelim As Boolean, ParamArray
vDCcArgAy()) As String
For i = LBound(vDCcArgAy) To UBound(vDCcArgAy)
For t = LBound(vDCcArgAy(i)) To UBound(vDCcArgAy(i))
Debug.Print vDCcArgAy(i)(t)
Next t
Next i
sAC_vuDrawF = "Test"
End Function

 

[Neal Zimm]

Dear Tim and JMB,
Thanks for responding. Your examples, along with re-reading the MSo
help, are beginning to clear the fog.

Here's the 'key' phrase from MSo help: "The ParamArray keyword allows
you to provide an arbitrary number of arguments".

Are these conclusions correct ?
1. The word "Arbitrary" from the above means you don't "dim" a
paramarray in the usual way.

2. You don't assign values to the elements of a Paramarray array in the
usual way, such as arrayname(index) = "something" prior to using it.

3. VBA automatically assigns, and dimensions, all but the first argument
in the Sub or Function statement as the elements of the paramarray.

4. Item 3 appears to me to be the "Value Added" of the syntax.

5. In using the elements of a paramarray array you're forced into either:
A. for index = 1 to 3 (if you've listed 4 arguments)
B. for index = Lbound(ayname) to UBound(ayname)

Thanks again.
Neal

 

[Neal Zimm]

Dear JMB,
Thanks for responding. Your example, along with re-reading the MSo help,
are beginning to clear the fog.

Here's the 'key' phrase from MSo help: "The ParamArray keyword allows
you to provide an arbitrary number of arguments". (Bad word choice by MSo,
all arrays are "arbitrary" even if dynamically re-dim'd.

Are these conclusions correct ?
1. The word "Arbitrary" from the above means you don't "dim" a
paramarray in the usual way.

2. You don't assign values to the elements of a Paramarray array in the
usual way, such as arrayname(index) = "something" prior to using it.

3. VBA automatically assigns, and dimensions, all but the first argument
in the Sub or Function WHICH CALLS the procedure in which paramarray appears
as the elements of the paramarray.

4. Item 3 appears to me to be the "Value Added" of the syntax.

5. IF you want the same information passed to more than one Sub or
Function, the syntax appears "messy" since you'd have to repeat the arguments
going into the paramarray each time. (So, for me the syntax does not appear
to be useful.)

6. In using the elements of a paramarray array you're forced into either:
A. for index = 1 to 3 (if you've listed 4 arguments in the calling
routine)
B. for index = Lbound(ayname) to UBound(ayname) as you showed.

Thanks again.
Neal

 

[Tim Williams]

Comments below.

Tim

Dear JMB,
Thanks for responding. Your example, along with re-reading the MSo
help,
are beginning to clear the fog.

Here's the 'key' phrase from MSo help: "The ParamArray keyword allows
you to provide an arbitrary number of arguments". (Bad word choice by MSo,
all arrays are "arbitrary" even if dynamically re-dim'd.

Are these conclusions correct ?
1. The word "Arbitrary" from the above means you don't "dim" a
paramarray in the usual way.

2. You don't assign values to the elements of a Paramarray array in the
usual way, such as arrayname(index) = "something" prior to using it.

Don't focus too much on the "array" part of "paramarray" when passing
arguments: just pass as many separate parameters as you need to.

3. VBA automatically assigns, and dimensions, all but the first argument
in the Sub or Function WHICH CALLS the procedure in which paramarray
appears
as the elements of the paramarray.

Not necessarily the FIRST - depends on how the procedure is defined

Function PTest(s As String, ParamArray PA()) As String
Function PTest(s As String, s2 As String, ParamArray PA()) As String
Function PTest(s As String, s2 As String, s3 As String, ParamArray PA()) As
String

So, all arguments *beyond those individually defined* end up in the
ParamArray.

4. Item 3 appears to me to be the "Value Added" of the syntax.

5. IF you want the same information passed to more than one Sub or
Function, the syntax appears "messy" since you'd have to repeat the
arguments
going into the paramarray each time. (So, for me the syntax does not
appear
to be useful.)

Agreed, in this case you may as well use an actual array as a parameter.

6. In using the elements of a paramarray array you're forced into
either:
A. for index = 1 to 3 (if you've listed 4 arguments in the calling
routine)

0 to 2 in your example. Note that the lbound/ubound of the paramarray will
not reflect your "Option Base x",
so lbound is always 0:

'********************************
Option Explicit
Option Base 1

Sub TestParam()
Dim PA As Variant
PA = Array("one", "two", "three")
Debug.Print PTest("ParamArray ", "one", "two", "three") '0 to 2
Debug.Print LBound(PA) & " to " & UBound(PA) '1 to 3
End Sub

Function PTest(s As String, ParamArray PA()) As String
PTest = LBound(PA()) & " to " & UBound(PA())
End Function
'********************************

 

[JMB]

I've not worked extensively w/parameter arrays, but I'll try to explain as
best as I understand:

1. By arbitrary, they mean you don't have to know how many arguments you are
going to pass to your procedure. But no, you don't "dim" a parameter array.
The Dim keyword is not used when declaring parameters for a procedure. In
fact, the paramarray must be declared as an array of type variant.

2. Correct. Paramater array elements are assigned by their position in the
procedure call. In my small example, x is the first element of the
paramarray and y is the second due solely to their position in the function
call.

3. VBA handles dimensioning the parameter array and assigning all of the
arguments. Assignment of arguments is based on position. In fact,
attempting to use named arguments in a call to a procedure w/a parameter
(even for the arguments that are separate from the paramarray) generates an
error me.

4. Perhaps.

5. You could have your procedure call pass an array to a variant variable
instead of a paramarray. If you are passing the same parameters to several
other procedures/functions - you may find easier way than using a paramarray.
One project I worked on had a large data set that is filtered every which
way to generate smaller reports. So there is one sub that handles all of the
filtering and copy/paste operations (and headers/footers and other pretty
stuff in the resulting reports) and maybe 10-15 small subs (assigned to
buttons on a worksheet) that just pass filter criteria to the main sub using
a parameter array. Each small sub passes unique filter requirements. The
filter criteria of each calling sub is easy to change (which is good cause
there was a little flip-flop on what the filter criteria should be), and I
don't have to create an array w/filter criteria to pass to the main sub for
every one of the calling subs. Your approach depends on your preference and
the project layout.

6. I would say so.

 

[Neal Zimm]

Tim -
thank you such much, funny how the English language gets in the way
sometimes:

I played with your example below, my bad, on the word "first", I was
referring to the args in the calling macro, where, beyond the 1st, they end
up in the PA.

Not necessarily the FIRST - depends on how the procedure is defined

Function PTest(s As String, ParamArray PA()) As String
Function PTest(s As String, s2 As String, ParamArray PA()) As String
Function PTest(s As String, s2 As String, s3 As String, ParamArray PA()) As
String

So, all arguments *beyond those individually defined* end up in the
ParamArray.

 

[Neal Zimm]

JMB -
Thanks so much. I'll admit to being a bit crazy when it comes to certain
wording phrases, I have a bad habit of assigning to much meaning to what MSo
writes in their Help.

re: not "knowing" what's being passed, MSo should have written something
like: You don't have to explicity assign a number for the quantity of vars
being passed, (but clearly you "KNOW" what they are 'cuz your typing them
into the damn Sub or Function statement.!!!)

thanks again