How to create module wide 'constant'?

Excel Helper

I am writing some code which consists of circa 10 different procedures
within a module.

I want to be able to refer to an array in each without having to declare it
in each module.

My array is:

ddRWS = Array(4,16,28,40,52,64,76,88,100)

I want to be able to refer to this in each procedure with statements like:

ddRWS(2)
For i = UBound(ddRWS) to UBound(ddRWS)

Can I declare this array once at the top of the module and then refer to it
during proecedures? I have already tried:

Public ddRWS as Variant
ddRWS = Array(4,16,28,40,52,64,76,88,100)

....this didn't work so i tried...

Public ddRWS as Variant
Const = Array(4,16,28,40,52,64,76,88,100)

How can I declare my array and the assocaited values once only at the module
level so that it can be referred to in subsequent procedures?

Regards


Alex

You declare an array like any other variable - if you leave out the data type
it assumes it's variant, ie:

Dim ddRWS(8) As Integer

would declare an array that would work for you. At the top of the module,
this becomes an array that is shared throughout the module (you can use
Public instead of Dim if you want other modules in your project to share it
too).

You would then need to populate it from a procedure, eg:
Sub myPopulate()
ddRWS(0) = 4
ddRWS(1) = 16
etc (or more normally a loop of some kind)

Once populated, because it was declared at the module level it can be used
by any procedure in the module. However, like all variables, it will be
reset once a macro stops running.

Hope that helps

"Alex" wrote:

> Excel Helper
>
> I am writing some code which consists of circa 10 different procedures
> within a module.
>
> I want to be able to refer to an array in each without having to declare it
> in each module.
>
> My array is:
>
> ddRWS = Array(4,16,28,40,52,64,76,88,100)
>
> I want to be able to refer to this in each procedure with statements like:
>
> ddRWS(2)
> For i = UBound(ddRWS) to UBound(ddRWS)
>
> Can I declare this array once at the top of the module and then refer to it
> during proecedures? I have already tried:
>
> Public ddRWS as Variant
> ddRWS = Array(4,16,28,40,52,64,76,88,100)
>
> ...this didn't work so i tried...
>
> Public ddRWS as Variant
> Const = Array(4,16,28,40,52,64,76,88,100)
>
> How can I declare my array and the assocaited values once only at the module
> level so that it can be referred to in subsequent procedures?
>
> Regards
>
>
> Alex

Hi Alex,

1)you can declare your array public at top of the module and then
populate it with the values in any procedure and read the values in any
procedure

2)you can use class module and property let, property get statements.

Regards,
Ivan

Alex,
An alternative, if you truly want a constant...
....
Public Const ddRWS As String = "4,16,28,40,52,64,76,88,100"

Sub GetSome()
Dim x As Variant
x = Split(ddRWS, ",")
MsgBox x(7)
End Sub
------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Alex" <Alex@discussions.microsoft.com>
wrote in message
Excel Helper
I am writing some code which consists of circa 10 different procedures
within a module.
I want to be able to refer to an array in each without having to declare it
in each module.
My array is:

ddRWS = Array(4,16,28,40,52,64,76,88,100)

I want to be able to refer to this in each procedure with statements like:

ddRWS(2)
For i = UBound(ddRWS) to UBound(ddRWS)

Can I declare this array once at the top of the module and then refer to it
during proecedures? I have already tried:

Public ddRWS as Variant
ddRWS = Array(4,16,28,40,52,64,76,88,100)

....this didn't work so i tried...

Public ddRWS as Variant
Const = Array(4,16,28,40,52,64,76,88,100)

How can I declare my array and the assocaited values once only at the module
level so that it can be referred to in subsequent procedures?
Regards
Alex