public or global array

Hi,

I am trying to generate a public or global array. The situation is
like this...I have couple of subroutines..Each of these subroutines
needs data stored in an array. I wish to generate this array and make
it public or global in one subroutine and make it available to all
other subroutines..How can I accomplish that..The code snippet as
below..

Sub GeneratePublicArray()
Dim MyArray As Variant
Dim mCell As Range

Set mCell =
ThisWorkbook.Sheets("SPECS").Range("NO_OF_MODULES").Offset(3, 1)

MyArray = Application.Transpose(Range(mCell, mCell.End(xlDown)))

End Sub

In a general module at the top if the module, declare it as public. don't
declare it anywhere else.

Public MyArray as Variant

Sub GeneratePublicArray()
Dim mCell As Range

Set mCell =ThisWorkbook.Sheets( _
"SPECS").Range("NO_OF_MODULES").Offset(3, 1)

MyArray = Application.Transpose(Range(mCell, mCell.End(xlDown)))

End Sub

--
Regards,
Tom Ogilvy



"shishi" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I am trying to generate a public or global array. The situation is
> like this...I have couple of subroutines..Each of these subroutines
> needs data stored in an array. I wish to generate this array and make
> it public or global in one subroutine and make it available to all
> other subroutines..How can I accomplish that..The code snippet as
> below..
>
> Sub GeneratePublicArray()
> Dim MyArray As Variant
> Dim mCell As Range
>
> Set mCell =
> ThisWorkbook.Sheets("SPECS").Range("NO_OF_MODULES").Offset(3, 1)
>
> MyArray = Application.Transpose(Range(mCell, mCell.End(xlDown)))
>
> End Sub
>

Do not declare the array within a sub. Instead declare it at the top of a
standard code module.

public myArray as Variant

sub Test
myArray(0) = "Test"
exit sub

Be careful with overusing globals as they can be tough to debug. If you have
a bunch of different procedures all modifying the varaible then if the
variable contains the wrong value at any point in the execution, it can be
hard to track down which procedure added the incorrect value.
--
HTH...

Jim Thomlinson


"shishi" wrote:

> Hi,
>
> I am trying to generate a public or global array. The situation is
> like this...I have couple of subroutines..Each of these subroutines
> needs data stored in an array. I wish to generate this array and make
> it public or global in one subroutine and make it available to all
> other subroutines..How can I accomplish that..The code snippet as
> below..
>
> Sub GeneratePublicArray()
> Dim MyArray As Variant
> Dim mCell As Range
>
> Set mCell =
> ThisWorkbook.Sheets("SPECS").Range("NO_OF_MODULES").Offset(3, 1)
>
> MyArray = Application.Transpose(Range(mCell, mCell.End(xlDown)))
>
> End Sub
>
>