Issue Accessing Public Multidimensional Array in Another Module

Hi all,

As the title says, I am having an issue accessing a Publicly declared multidimensional array in another module. Sorry if this seems obvious or badly written, I'm new to VBA. My project is set out as below:

Module 1 - Contains all my publicly declared variables, including the multidimensional array. The code for that is:

Please Login or Register  to view this content.

Module 2 - Here I ReDim the array as a 2 dimensional array and add the values to it.

Please Login or Register  to view this content.

At this point, when I Watch the tableArray variable, it displays correctly, with data populated in each of the collections (although some are left blank intentionally)

After this I have a final module - where the problem is. The Sub in this module is run after the user double clicks on certain cells within a worksheet (but this will always happen after the Sub in the second module has run):

Please Login or Register  to view this content.

I get the error on double clicking: "Subscript out of range", and the watch now reports the tableArray as type Collection(0 to -1), with no data. Why, if this is a public variable, is the data not being retained properly?

Thanks in advance!

Sprucie

Hi Leith, thanks for the quick response.

Unfortunately I am still getting the same error message when running the Sub in module 3. Now on the Watch it says the type is Variant/Variant(), but that is the only change.

I am adding the code to call the final sub to the worksheet programatically if that makes any difference?

I'm pretty sure that:

Please Login or Register  to view this content.

Will reset the project and all your variables.

Adding code at runtime is not good practice. Why can't you use a template?

Hi Kyle,

As I mentioned I'm pretty new to VBA, that section you're talking about seems like it could be the proble. I'll look into templates.

Thanks!

I've looked into templates and it doesn't seem like it will work in this case - I need the macros to run on multiple machines without issues.

Can anyone advise a way to adapt this code so it doesn't reset the project/variables:

Please Login or Register  to view this content.

How is that a restriction of a template workbook?

Hi Kyle, I think I've understood what you're suggesting now, and I don't see why it wouldn't work, so I'll mark it as solved. Many thanks to everybody for their quick responses.

Sprucie.