Declare a Public Array

[Patrick C. Simonds]

Per Jessen has provided me with a great deal of help, and one of the thinks
he has helped me with is to use an array of ranges not separate variables.
Among the lines of code he has provided one was:

Dim rngArr() As Range

It became apparent in the course of my work that I would need to refer to
that array in additional modules that were called from the userform in which
rngArr() As Range dimensioned. He told me to declare rngArr() As Range as
public and outside the module. By outside the module I assumed that to mean
that the line Public rngArr() As Range should be placed above all other
code for the userform (before the first sub routine).

I have done that but get the following error message:

Compile Error

Constants, fixed-length strings, arrays, user-defined types and Declare
Statements not allowed as Public members or object modules


So is there any way in which to declare this array as Public? Below is
everything I am declaring (what I believe to be outside the module) above
the first sub routine.


Option Explicit
Dim rMyCell As Range
Public rng As Range
Dim c As Range
Dim Counter As Long
Dim myRng As Range
Dim my1Rng As Range
Public MissData As Boolean
Public BlkProc As Boolean


Private Sub ComboBox1001_Change()

 

[Patrick C. Simonds]

Sorry I copied from a version before I added the Public rngArr() As Range
line. Below is whit I intended to send:

Option Explicit
Dim rMyCell As Range
Dim c As Range
Dim Counter As Long
Dim myRng As Range
Dim my1Rng As Range
Public rng As Range
Public rngArr() As Range
Public MissData As Boolean
Public BlkProc As Boolean


Private Sub ComboBox1001_Change()

 

[Bob Phillips]

Are you sure that he was suggesting setting the array as type Range, as that
means that it will store several ranges, and would be loaded something like
this


ReDim rngArr(1 To 2)
Set rngArr(1) = Range("A1:A10")
Set rngArr(2) = Range("C1:C10")

I would have thought he was sayin g set it up as type variant and drop a
range into it and process that array. Like this

Option Explicit
Dim rMyCell As Range
Dim c As Range
Dim Counter As Long
Dim myRng As Range
Dim my1Rng As Range
Public rng As Range
Public rngArr() As Variant
Public MissData As Boolean
Public BlkProc As Boolean


Private Sub ComboBox1001_Change()

rngArr = Range("A1:A10")

End Sub

and you can then use rngArr elsewhere

 

[Per Jessen]

Patrick,

Maybe I wasn't explicit in my explanation.

As you have realized you can not declare a public variable as a Range in a
userform module, but if you declare the public range variable(s) in a
general module everything is working fine.

Regards,
Per

 

[Chip Pearson]

The key word in the error message is

or object modules

An object module is any one of the following: a Class module, the
ThisWorkbook module, one of the Sheet modules, a Userform's code
module. In these types of modules, you cannot declare public arrays.
The array can be declared as Private, and it will work fine but will
not be accessible (read or write) to code outside of the module in
which it was declared.

If you declare the array variable in a regular code module, it will be
accessible from any procedure in any module, including object modules.

By outside the module I assumed that to mean
that the line Public rngArr() As Range should be placed above all other
code

That is correct.

The term "Scope" refers to where a variable is declared and from where
it can be accessed (written to or read from). For a detailed
discussion of Scope as it applies to both variables and procedures,
see http://www.cpearson.com/excel/Scope.aspx .

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Patrick C. Simonds]

Thank you sir. The link you provided lead me in the right direction. I
declared rngArr() As Range under ThisWorkbook and gave it a project scope
thereby solving my problem.