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.

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



--
Neal Z


"JMB" wrote:

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

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
--
Neal Z


"JMB" wrote:

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

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
--
Neal Z