Sharing Arrays Between Modules

[NZ VBA Developer]

In an earlier post, Jean-Guy Marcil provided me with some excellent
instruction on structuring projects to separate UserForm-related processes
from document-related processes. (Thanks again, Jean-Guy!) I'm now trying to
apply this structure to a somewhat more complex project and have run into a
problem straight away.

In this new project, I use arrays to collect sets of similar information
(e.g. the names and addresses of multiple parties to a contract). These
arrays all reside in the UserForm class module, since they are obviously
integral to the processes related to the UserForm; i.e. UserForm operations
are used to create and maintain the arrays, and the arrays are used to
display information in the UserForm.

However, I cannot find a way to take the information in these arrays and
transfer it back into the 'operational' class module that is used to perform
the actual document-creation processes. UserForm controls (and the values
contained therein) are simple enough to access and variables can be declared
as Public and then just become a 'pseudo-attribute' of the UserForm object,
but arrays cannot be Public.

For obvious reasons, I don't want to dynamically create a heap of Public
variables to dump the contents of the arrays into and then rebuild the arrays
on the 'operational' side, and doing something like grabbing the contents of
the ListBoxes that are used to display and maintain the arrays on the
UserForm and then attempting to parse this information scares the daylights
out of me. Does anybody have any suggestions on how to share contents of an
array between two modules?
--
Cheers!
The Kiwi Koder

Please note: Uninvited email contact will be marked as SPAM and ignored -
unless you want to hire me. ;-)

 

[Jonathan West]

In an earlier post, Jean-Guy Marcil provided me with some excellent
instruction on structuring projects to separate UserForm-related processes
from document-related processes. (Thanks again, Jean-Guy!) I'm now trying
to
apply this structure to a somewhat more complex project and have run into
a
problem straight away.

In this new project, I use arrays to collect sets of similar information
(e.g. the names and addresses of multiple parties to a contract). These
arrays all reside in the UserForm class module, since they are obviously
integral to the processes related to the UserForm; i.e. UserForm
operations
are used to create and maintain the arrays, and the arrays are used to
display information in the UserForm.

However, I cannot find a way to take the information in these arrays and
transfer it back into the 'operational' class module that is used to
perform
the actual document-creation processes. UserForm controls (and the values
contained therein) are simple enough to access and variables can be
declared
as Public and then just become a 'pseudo-attribute' of the UserForm
object,
but arrays cannot be Public.

For obvious reasons, I don't want to dynamically create a heap of Public
variables to dump the contents of the arrays into and then rebuild the
arrays
on the 'operational' side, and doing something like grabbing the contents
of
the ListBoxes that are used to display and maintain the arrays on the
UserForm and then attempting to parse this information scares the
daylights
out of me. Does anybody have any suggestions on how to share contents of
an
array between two modules?

There are two ways round this.

Arrays and variables in a UserForm can be public. Declare them using the
Public keyword before the first Sub or Function in the code module of the
UserForm. To the outside world, Public variables in a UserForm (or in a
Class module) appear to be public properties of the form, and can be
accessed in just the same way as any of the other properties of the form.

Alternatively, you can declare the array as Public in any ordinary module.
It is then a simple public array accessible from any routine in any module.

 

[NZ VBA Developer]

Jonathan,

I did initially try declaring the array as Public in the UserForm module. I
did this because I knew that, as you stated, Public variables functioned like
properties of the UserForm and thus became easily accessible in the rest of
the project. However, I got a compile error stating that arrays (amongst
others - constants, fixed-length strings, etc.) are not allowed as Public
members of object modules, which instigated this post.

The good news is I've run a quick test just now and have discovered that an
array *can* be declared as Public in an ordinary module; apparently the
limitation on the declaration of Public arrays is only around object modules,
such as UserForms. This is a great help. Now I just need to see if I can
manipulate the array in the UserForm module as it needs to be dynamic and
thus support the use of a ReDim.

So in summary, while your first suggestion doesn't work, your second one
does and has got me pointed in the right direction. Thanks for your
assistance!

 

[fumei via OfficeKB.com]

You can use Redim (in a userform module) on an array declared Public in a
standard module.

Declaring it Public in a standard module makes it Public anywhere.

I have never figured out the logic why you can not declare Public arrays in
object modules. Other than the (very real) possibility that the userform is
unloaded. In which case, it can hardly be Public.

It is that darn Scope thing again.

Jonathan,

I did initially try declaring the array as Public in the UserForm module. I
did this because I knew that, as you stated, Public variables functioned like
properties of the UserForm and thus became easily accessible in the rest of
the project. However, I got a compile error stating that arrays (amongst
others - constants, fixed-length strings, etc.) are not allowed as Public
members of object modules, which instigated this post.

The good news is I've run a quick test just now and have discovered that an
array *can* be declared as Public in an ordinary module; apparently the
limitation on the declaration of Public arrays is only around object modules,
such as UserForms. This is a great help. Now I just need to see if I can
manipulate the array in the UserForm module as it needs to be dynamic and
thus support the use of a ReDim.

So in summary, while your first suggestion doesn't work, your second one
does and has got me pointed in the right direction. Thanks for your
assistance!

There are two ways round this.

[quoted text clipped - 6 lines]

Alternatively, you can declare the array as Public in any ordinary module.
It is then a simple public array accessible from any routine in any module.

 

[NZ VBA Developer]

I thought as much - just hadn't had the time to confirm. I'll give it a go
this morning as the first step in transforming all of my projects to the new
JGM (Jean-Guy Marcil ;-D) standard.

Thanks for sharing!

--
Cheers!
The Kiwi Koder

You can use Redim (in a userform module) on an array declared Public in a
standard module.

Declaring it Public in a standard module makes it Public anywhere.

I have never figured out the logic why you can not declare Public arrays in
object modules. Other than the (very real) possibility that the userform is
unloaded. In which case, it can hardly be Public.

It is that darn Scope thing again.

Jonathan,

I did initially try declaring the array as Public in the UserForm module. I
did this because I knew that, as you stated, Public variables functioned like
properties of the UserForm and thus became easily accessible in the rest of
the project. However, I got a compile error stating that arrays (amongst
others - constants, fixed-length strings, etc.) are not allowed as Public
members of object modules, which instigated this post.

The good news is I've run a quick test just now and have discovered that an
array *can* be declared as Public in an ordinary module; apparently the
limitation on the declaration of Public arrays is only around object modules,
such as UserForms. This is a great help. Now I just need to see if I can
manipulate the array in the UserForm module as it needs to be dynamic and
thus support the use of a ReDim.

So in summary, while your first suggestion doesn't work, your second one
does and has got me pointed in the right direction. Thanks for your
assistance!

There are two ways round this.

[quoted text clipped - 6 lines]

Alternatively, you can declare the array as Public in any ordinary module.
It is then a simple public array accessible from any routine in any module.

 

[Jonathan West]

I thought as much - just hadn't had the time to confirm. I'll give it a go
this morning as the first step in transforming all of my projects to the
new
JGM (Jean-Guy Marcil ;-D) standard.

I tend to be very sparing in my use of Public variables, it is all-too-easy
to get them mixed up and to introduce unexpected interactions between
routines. Instead, wherever practical, I prefer explicitly to pass
parameters between routines. You can pass an array as a parameter.

The art of defensive programming
http://www.word.mvps.org/FAQs/MacrosVBA/MaintainableCode.htm

How to cut out repetition and write much less code, by using subroutines and
functions that take arguments
http://www.word.mvps.org/FAQs/MacrosVBA/ProcArguments.htm

 

[Jean-Guy Marcil]

I thought as much - just hadn't had the time to confirm. I'll give it a go
this morning as the first step in transforming all of my projects to the new
JGM (Jean-Guy Marcil ;-D) standard.

Thanks, but...
just in case... (For those who missed the other thread...) I did not
advocate the use of Public variables... I simply suggested that code having
to do with the handling of the document should be kept outside the userform
class module... I did suggest using a userform varibale, which, in my
opinion, is slightly different than a public variable because it is a
userform property...

I do agree with Jonathan, I usually pass parameters between routines instead
of using Public variables. I use public variables only when I need a Flag set
early in a complex routine that will have an impact on many Subs down the
line. Then, I give a very clear descriptive name to the flag, something like:

Dim boolDocProtected As Boolean

which clearly states that this is a boolean variable that has to do with the
state of protection in the document. If it is True, then the document is
protected.

This is better than

Dim a

No variable typing and no clues as to what it does...

But I guess this is a case of personal belief and practice.. like
religion... which it is to some...

 

[NZ VBA Developer]

My apologies, Jean-Guy; I have seen the light...

I didn't mean to imply that you were advocating the use of Public variables.
I only mentioned you because I saw Public arrays as a means to achieving the
desired result of separating document-related and UserForm-related
operations. Your (and Jonathan's) comments have got me thinking about what I
actually need the UserForm to do, and I suspect that much of the manipulation
of the data that's stored in the various arrays can be done outside of the
UserForm. I may be able to share just one array between the UserForm and the
"document" and simply pass this array between the two as an argument.

By way of explanation: For one particular template I have one primary array
that is used to collect repeating sets of information (the names and
addresses of parties to a contract). In addition, I have seven other arrays
that simply store sorted and collated subsets of the information in the
primary array. These subsets are _only_ used when building the document, so
there is no need to muck about with them on the UserForm side; all of this
work can be done on the "document" side after the gross data is collected. I
suspect this would make my code faster, simpler and much more reliable.

Note that for other templates there may be two or even three primary arrays
and a corresponding number (14 or 21) of subset arrays, and to be perfectly
honest, I was a bit concerned about declaring them all as Public.

I will re-evaluate my methods and try to work out how to pass the primary
array between the modules. Perhaps as a first step I'll just declare the
primary array as Public and shift the subset arrays and the operations for
manipulating them back to the "document" module. Even this would be an
improvement over seven (or 14 or 21) Public arrays.

Thanks again for all your help. I've learned heaps in the last week since we
started this correspondence. I really do appreciate your assistance. If
you're ever in New Zealand, I'll buy you a Speights, mate! ;-D

 

[Jean-Guy Marcil]

My apologies, Jean-Guy; I have seen the light...

I didn't mean to imply that you were advocating the use of Public variables.

I know, this is why I wrote:
"just in case... (For those who missed the other thread...) "

I only mentioned you because I saw Public arrays as a means to achieving the
desired result of separating document-related and UserForm-related
operations. Your (and Jonathan's) comments have got me thinking about what I
actually need the UserForm to do, and I suspect that much of the manipulation
of the data that's stored in the various arrays can be done outside of the
UserForm. I may be able to share just one array between the UserForm and the
"document" and simply pass this array between the two as an argument.

By way of explanation: For one particular template I have one primary array
that is used to collect repeating sets of information (the names and
addresses of parties to a contract). In addition, I have seven other arrays
that simply store sorted and collated subsets of the information in the
primary array. These subsets are _only_ used when building the document, so
there is no need to muck about with them on the UserForm side; all of this
work can be done on the "document" side after the gross data is collected. I
suspect this would make my code faster, simpler and much more reliable.

Note that for other templates there may be two or even three primary arrays
and a corresponding number (14 or 21) of subset arrays, and to be perfectly
honest, I was a bit concerned about declaring them all as Public.

I will re-evaluate my methods and try to work out how to pass the primary
array between the modules. Perhaps as a first step I'll just declare the
primary array as Public and shift the subset arrays and the operations for
manipulating them back to the "document" module. Even this would be an
improvement over seven (or 14 or 21) Public arrays.

Another option is to use a class module to create Collection objects...

Thanks again for all your help. I've learned heaps in the last week since we
started this correspondence. I really do appreciate your assistance. If
you're ever in New Zealand, I'll buy you a Speights, mate! ;-D

No problems... Glad I could help...
This better be some sort of beer!

Cheers!