Do I need "Global" or is there an alternative?

I'm trying to use 3 input values and 1 derived (vlookup) value in 4 cells on a worksheet To open , close, save as and input data on several other workbooks. I can do this, but have to Dim and Set values for every Sub which uses the data. I've confused myself researching Global, Public & Constant and wonder if I'm overthinking things. Sub "Wanted" shows what I want to achieve in (hopefully) plain language. Then subs in other modules need to access the "Public" line. Hope this is clear. Sub "Got" shows what I'm using.

Cell data examples - Fle; 14/1/17; 2017; Flem. Normally change each time code runs

Any help appreciated.

Please Login or Register  to view this content.

Can't believe I've done this! The following code works!!!!!

It seems I have to call "SetAll" each time I want to access the Public Variables. Is this right?

Also I need some help with changing the format of the date taken from F2. It is dd/mm/yyyy in the cell, but I need to use it as ddmmyyy for cell2 and yyyymmdd for cell5. Any help on that please?

Would appreciate any thoughts on the original question and my solution, but will thank you for your assistance tomorrow. I've been working on this all day, I'm brain dead and need a drink.

Cheers

Steve

Please Login or Register  to view this content.

Originally Posted by swallis

It seems I have to call "SetAll" each time I want to access the Public Variables. Is this right?

You wouldn't necessarily have to call SetAll each time. Call SetAll once to initialize the variable values and then anytime the cell values change so as to update the variables. That may or may not mean every time for you depending on how often the cell values change. The values in the public variables persist in memory, so you only need to update them when the cell values change.

Also I need some help with changing the format of the date taken from F2. It is dd/mm/yyyy in the cell, but I need to use it as ddmmyyy for cell2 and yyyymmdd for cell5. Any help on that please?

Please Login or Register  to view this content.

Alternatively, you could just read the date into one Date-type variable. Then in the code, format the date any way you like when you use it e.g.; Format(MyDate, "ddmmyyy")

Please Login or Register  to view this content.

One tip: in your original code, you declare a variable called Year. That is also a keyword used in VBA. While it is legal to have variable names the same as VBA keywords, it is not good coding practice. It can be confusing to read the code and, once in a while, it will cause vba to puke. I know you said you've read a lot, but here's one more you should read.
Constant and Variable Naming Conventions

Thanks for the replies.

Alphafrog:

You wouldn't necessarily have to call SetAll each time.

I must have been really brain dead last night. Kept getting error messages when I tried to run the code again without "set". Today it works fine. Process will be that each time I change any of the cells they will be used for the entire Project, so I just have to make sure that the first Sub calls Setall?

I like your first solution to the format problem. I nearly had it, but had the .value at the end of the line.

One tip: in your original code, you declare a variable called Year.

Yes. All my names are temporary while I get things to work. I was intending to Rename them and put a P in front. So thanks for the link, I see it advise against that and suggests G instead.

Thanks for your help. You've set me up for a lot of fun next week.

regards

steve