sharing an array between worksheets

[Peter]

Hi

I have a module which runs when a sheet is opened. That
gets some information and then populates an array (defined
as public within the module). That all works fine, but if
I try to access the contents of the array from any of the
subsequent sheets, I get an error saying an array was
expected. Why can't the sheets see it even though it's
defined as public in the module?

Thanks for your replies.

Peter

 

[Jim Rech]

If I follow you, it seems that you're setting a public variable in a sheet
module and finding that it cannot be seen from outside of it.

If that's the case it's because code in other modules will not search sheet
modules for variables (unlike standard modules), but it will find them if
you point specifically to them, by, for example, using "Sheet1.MyVar"
instead of just "MyVar".

--
Jim Rech
Excel MVP
| Hi
|
| I have a module which runs when a sheet is opened. That
| gets some information and then populates an array (defined
| as public within the module). That all works fine, but if
| I try to access the contents of the array from any of the
| subsequent sheets, I get an error saying an array was
| expected. Why can't the sheets see it even though it's
| defined as public in the module?
|
| Thanks for your replies.
|
| Peter