How to Pass collection between modules?

[deko]

This is a basic program flow question.

I'm trying to refractor an AC2000 app and split sections of code into
separate modules. But there are a number of collections I create in one big
module - containing file paths, Excel worksheet names, etc - I'm not sure
how to pass these around, or if I'd be better off saving things to a table
and using a recordset. My guess is a recordset would be slower.

For example, should I do something like this:

[form module]
If basA.Function1 Then
If basB.Function.2 Then
If basC.Function3
Else
'code
End if
Else
'code
End if

(but then how to pass collections between modules?)

or just daily chain the modules:

[basA]
'code
Call basB (colX)

[basB]
'code
Call basC (colY)

etc..

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A Module is not a routine (Sub/Function). We do not pass parameters
between modules, but between routines in the modules. You can pass a
collection variable as you pass any other variable: as a parameter.
E.g.:

' inside a routine
Dim col As New Collection
...
col.Add "Fred"
col.Add "Harry"
MySub col ' <- the call to the other routine w/ the collection
---
' in another module
Public Sub MySub(MyCol As Collection)
' use the collection parameter
Dim nm As Variant
For Each nm In MyCol
Debug.Print nm
Next nm
End Function

Results (in debug window):
Fred
Harry
--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm1tEoechKq OuFEgEQKE5ACg1g eSg/++CdXBKZ66HZ+AI RsEYkcAoI/g
jfJeqdCbu3XFQIk 57pN8lqva
=8TgW
-----END PGP SIGNATURE-----

deko wrote:

This is a basic program flow question.

I'm trying to refractor an AC2000 app and split sections of code into
separate modules. But there are a number of collections I create in one big
module - containing file paths, Excel worksheet names, etc - I'm not sure
how to pass these around, or if I'd be better off saving things to a table
and using a recordset. My guess is a recordset would be slower.

For example, should I do something like this:

[form module]
If basA.Function1 Then
If basB.Function.2 Then
If basC.Function3
Else
'code
End if
Else
'code
End if

(but then how to pass collections between modules?)

or just daily chain the modules:

[basA]
'code
Call basB (colX)

[basB]
'code
Call basC (colY)

etc..

[deko]

> A Module is not a routine (Sub/Function). We do not pass parameters

between modules, but between routines in the modules. You can pass a
collection variable as you pass any other variable: as a parameter.

Yes, that makes sense.

Perhaps using a public collection would work...

Option Compare Database
Option Explicit
Public MyCol as Collection

Public Function Put_Stuff_In_Co llection
' code here
End Function

Now I can access the collection from the other modules.

As I mentioned, this app has grown considerably and I need to separate out
the code into different modules to keep it manageable. Otherwise I have a
module with about 1000 lines of code.

I'm also looking for alternatives to storing everything in a
table/recordset. VBA arrays just don't cut it, and collections are tricky
because they get re-indexed if I try to pop something out in the middle of a
loop.

[rkc]

deko wrote:

A Module is not a routine (Sub/Function). We do not pass parameters
between modules, but between routines in the modules. You can pass a
collection variable as you pass any other variable: as a parameter.

Yes, that makes sense.

Perhaps using a public collection would work...

Option Compare Database
Option Explicit
Public MyCol as Collection

Public Function Put_Stuff_In_Co llection
' code here
End Function

Now I can access the collection from the other modules.

As I mentioned, this app has grown considerably and I need to separate out
the code into different modules to keep it manageable. Otherwise I have a
module with about 1000 lines of code.

I'm also looking for alternatives to storing everything in a
table/recordset. VBA arrays just don't cut it, and collections are tricky
because they get re-indexed if I try to pop something out in the middle of a
loop.

Where is the information coming from in the first place if not from
a recordset retrieved from some type of storage?

A VBA.Collection isn't much more that an array wrapped in a class with
a few methods to manipulate it. The collection itself becomes more
useful when wrapped in a class with methods to manipulate it. It's
a basic object that is most useful when you use it to build something
else. Having a collection floating around with nothing managing it is
really kind of sloppy.

[deko]

> A VBA.Collection isn't much more that an array wrapped in a class with

a few methods to manipulate it. The collection itself becomes more
useful when wrapped in a class with methods to manipulate it. It's
a basic object that is most useful when you use it to build something
else. Having a collection floating around with nothing managing it is
really kind of sloppy.

Yeah, I guess I could just throw everything into a table. When in Rome...