How to use the same variable in different procedures

Hi all,

If I got a variable in a procedure named AAA, how can I use the same variable (ie NewWB) without entering again the right hand side of the equation in the second procedure named BBB in the same module?

Please Login or Register  to view this content.

[/CODE]

Originally Posted by nathansav

declare the variable at the top before your subs, so public NewWB as excel.workbook

Thanks, but when to enter

Please Login or Register  to view this content.

[/CODE]

I mean in the first procedure or before the first procedure?

Sorry that I am still unclear of this concept.

Please see below code

Please Login or Register  to view this content.

If I would like to use variable NewWB in both sub-procedure, where should I put NewWB = "Sales..........xlsm" in the module? I dont want to put the same statement in every procedure. Any idea is appreciated. Thanks

Originally Posted by MarvinP

Hi,

See if this works for you...

In the Workbook module have this code:

Please Login or Register  to view this content.

Then in the Module have this sub.

Please Login or Register  to view this content.

Of course you need to set RawWB and NewWB as global variables....

Does this make sense to you??

Thanks MarvinP, your idea is good, I am also thinking of using

Please Login or Register  to view this content.

before the first procedure.

Originally Posted by MarvinP

Public Const is a great idea also.

In some other posts, when searching for an answer for you, there was a lot of confusion and concern about using Global variables. One MVP suggested to not use them as they were unreliable. Read
http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx
to see that there is some confusion on using the same variable name and not knowing what was going on. If you use a global constant of NewWB can you change its value in code?? If you create a global variable of NewWB can you change its value in code??

I believe the confusion happens when you have a Global Variable and then use a DIM statement in a lower module using the same name. Then the confusion starts. Does the code use the most recent Dimed value or the Global value. In my understanding, a procedure is put on the stack and then erased from the program stack at the End Sub statement. End Sub throws away all the variables and code of that Sub. Read the above link to see if following the "Temp" variable makes sense. Also check to see if a Global Constant can change values. I'm not sure it can.

MarvinP. Much appreciate for your help