VBA Variables

[DCSwearingen]

I am creating a template to be used at ten different manufacturing plant locations throughout the US.

I have a userform that comes up when the workbook opens with several options in the form of text boxes and check boxes that will allow the user to enter the plant location, fuel sources, product lines manufactured, etc. It also has a check box to show the form on startup, so that once they have their options they can un-check the box an not have the form show.

Since I don't know too much about the volatility of variables, I am using a hidden sheet to place the values of the choices made - E.g.: The Plant location text box value gets stored in cell A1, The true or false state of the check box for each energy source gets stored in cells A2:A6, and so forth.

Would this be more efficient to use arrays? If so, global? Will the values be volatile in an array and subject to loss?

What about collections, can a TRUE/FALSE be assigned to an item in a collection?

I guess, I am comfortable with poking values into the hidden sheet, but look at this as a learning process and if there's a better mousetrap out there I would like to learn to use (build with) it.

[dominicb]

Good evening DCSwearingen

Variables are entirely volatile and need saving somewhere at the end of a session so they can be retrieved next time. Why not put them in a worksheet? It's a good as place as any - the only other alternative would be the registry, but as that only refers to the users PC it is more suited to user preferences.

I personally would probably read the values into a public variable at the start of the session, allow them to be manipulated as variables and write them back to the sheet on closing.

You shouldn't have any trouble using TRUE / FALSE (remember to declare them as boolean) values in a collection - they are the same as any other variables, save they can only be TRUE or FALSE (or 1 or 0).

HTH

DominicB

[DCSwearingen]

I am using variables and placing them into the hidden worksheet when the form is closed.

I guess I am basically doing as you suggest, other than I am not using collections - yet...

Learning by trial and error and the advice from all of the helpful MVPs.

I hope I speak for many of the people posting questions when I say you people are absolutely tremendous to help us all!