[AccessD] OT: Passing an Array in VBA

As I said before, pass it as a Variant.  It can be strongly typed within the calling procedure and assigned to the variant before passed  and the receiving subroutine can quickly check IsArray which returns a boolean.  If you absolutely need to, you can pass information about the data types and validate in the called routine, but that can get real messy with more than 2 dimensional arrays with multi data types.  I gave the example of passing an array to a sort routine that can sort on a chosen column, whatever the data type.  You'd have to be awful picky if you wanted to validate the datatype with the sort routine.  Better to create a module with a receiving variable, validate the data and then call a sort within the module akin to how you might implement it as a class module.  If you validated on each sort element, it would slow sorting down significantly.  Another example, if you use getRows with a recordset, you can't be strongly typing each column if you've got mixed numeric, date, text or boolean data types.   VBA transposes the row and column from what you get in Excel so what you can do in that case is a single dimension array for each column.  Name them 'A()', 'B()', 'C()'  and it makes sense to Excel users plus you can strongly type each one if need be.
 
Just pass it as a variant.  sheesh.
 
You could also look up paramarray in the help, those too must be variants.

Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com


 
> From: rockysmolin at bchacc.com
> To: accessd at databaseadvisors.com
> Date: Wed, 22 Dec 2010 09:32:10 -0800
> Subject: Re: [AccessD] OT: Passing an Array in VBA
> 
> The pass the local array through a global declaration. In the receiving
> routine, copy the global to a local array declaration. The global then
> becomes just a temporary 'messenger' array.
> 
> 
> R
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kenneth Ismert
> Sent: Wednesday, December 22, 2010 9:20 AM
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] OT: Passing an Array in VBA
> 
> My take:
> 
> There is no need for global object references. Public subroutines and
> functions in plain old modules provide all the global visibility that is
> required.
> 
> Lambert is right -- global variables got their bad name because can be
> changed or destroyed by any code at any time.
> 
> Globals make using code among projects harder. For sharing, you want your
> modules as self-contained as possible.
> 
> And philosophically, I think globals promote a passive, 'reference-based'
> style of programming ... "Change this global, and the app should behave
> differently", "Do this [with an implied global in some indeterminate
> state]". State is lazily maintained. This often leads to "programming that
> is so complex, nothing is obviously wrong".
> 
> What I am striving for is an active style: "Do this with this". Code is
> functional -- it acts only on what is given, and returns only a result. This
> style has no side-effects: no inputs are modified, and global state is not
> changed. Classes rely on external function libraries -- even module-level
> variables are too public! Classes written this way become 'light-weight',
> carrying only the 'what', not the 'how', and become much easier to
> understand. When most of your code is in function libraries, writing tests
> becomes feasible.
> 
> You can't achieve purely functional code in VBA (for that, you'd need
> Haskell). But still, the goal is to write "code that is so simple, nothing
> can possibly be wrong".
> 
> -Ken