Use spreadsheet cells for global variable space?

[Robert Crandal]

I have two large arrays of integers that I would like to initialize
when my workbook is opened. Since it seems too difficult
(and maybe impossible) to make these arrays global to ALL
of my VBA code modules, I thought of the following solution:

How about I create one hidden sheet in my workbook and
use the cells to simulate an array of data? If my data is stored
in a hidden sheet, that sheet could be easily accessed by ANY
of my VBA modules.

Any comments or suggestions???

 

[OssieMac]

Hi Robert,

I have always found it to be a very successful method. You can also set up
your system so that Administrators with the correct password can make
modofications. However, consider very hidden code so that the worksheet
cannot be unhidden in the user interface mode.

Example hiding.
Sheets("Sheet2").Visible = xlVeryHidden

Can only unhide with code as follows.
Sheets("Sheet2").Visible = True

 

[John Bundy]

I've never had a problem making my arrays global, just put it in its won
module and make it public.
Here is a reference
http://www.dreamincode.net/forums/showtopic40077.htm