Use spreadsheet cells for global variable space?

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

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???
 
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
 
Back
Top