Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
public or global array
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" wrote in message ups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
public or global array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Public Array in a form? | Excel Programming | |||
public array | Excel Programming | |||
Public vs Global | Excel Programming | |||
declaring a public array | Excel Programming | |||
Public Array | Excel Programming |