Global Array not working

Good Morning,

I've been putting together a UserForm that utilizes an array in 2 different subs. I want to build the array in 1 sub and then reference it in another. I'm getting:


Compile Error:
Sub or Function not defined


I believe I declared the array corrected as public in the "Workbook_Open" section.

Please Login or Register  to view this content.

Then in my userform I get:

Please Login or Register  to view this content.

Any advice? I'm at a dead end!

John

You can't declare a public variable in any procedure, and you can't declare a public variable in a class module (sheet module, ThisWorkbook, form, user-defined class).

Public variables must be declared at the top of a standard module, above any procedures, right below Option Explicit.

@shg,
you mean a public array variable. other types (apart from fixed length strings and udts) you can declare as public in a class-they just become public members of the class.

it means you need to declare it in a normal module, as shg said-not in ThisWorkbook or a worksheet/class module.

you mean a public array variable. other types (apart from fixed length strings and udts) you can declare as public in a class-they just become public members of the class.

No, I think I mean any public variable -- scalar, object, or array. A variable declared as public in a class module is scoped local to an instance of the class. In the case of worksheet and workbook modules, there can only be a single instance, so they can seem as public variables (prefixed with the class), but I think it's a bit of perversion.

Just to repeat,

You can't declare a public variable in any procedure, and you can't declare a public variable in a class module (sheet module, ThisWorkbook, form, user-defined class).

Public variables must be declared at the top of a standard module, above any procedures, right below Option Explicit.

Please Login or Register  to view this content.

I take your point, though I would suggest that in any practical way it is a public variable (not least because it is a variable declared as Public). the issue is of course moot here anyway since an array cannot be a public member of a class.

Fair enough.

Thanks so much!!!

Okay, so that solved the problem of it breaking-- it no longer breaks, but it still doesn't retain the information like I need it to.

I made a mock-example of what I'm trying to do, it's attached EXAMPLE.xlsm and also here is the code:

Please Login or Register  to view this content.

So I'm trying to have an e-mail go out when you hit Submit that lists everything that was input. But it only seems to take what was last input.