PDA

View Full Version : [SOLVED] Pass Variables



jwilder1
09-18-2005, 02:59 AM
I use a Workbook(sheets hidden) to assign a string variable with a sub procedure. How can I pass this variable to any other workbook that I open?

MWE
09-18-2005, 06:48 AM
I use a Workbook(sheets hidden) to assign a string variable with a sub procedure. How can I pass this variable to any other workbook that I open?
There are several possibilities but the simpliest is if both workbooks are open and you simply "pass" to the 2nd workbook what you want it to know. For example, you wish to fetch a string variable from Book1, Sheet1, cell A1 and put it in cell A2, Sheet2, Book2:

Workbooks("Book2").Worksheets("Sheet2").Cells(2,1) = Workbooks("Book1").Worksheets("Sheet1").Cells(1,1)
Similarly, assume a global in Book2 named gblString into which the string stuff from Book1 is to be placed. Then the code might be:

gblstring = Workbooks("Book1").Worksheets("Sheet1").Cells(1,1)
Code like this could be placed in the Workbook_Open procedure of Book2 preceeded by a statement to open Book1 and succeeeded by a statement to close Book1. That way Book2 always knows the value it needs and there is nothing that the user needs to do (except to click on the box indivating that Macros are to be Enabled when he/she statrs up Book2.

Assuming that the overhead of opening and closing Book1 is not a problem, the above should do what you want. You can fetch data from a closed workbook as well.

mdmackillop
09-18-2005, 11:25 AM
Hi,
If your workbooks are not open, you can save values in the registry. See the atttached KB.
Regards
MD
http://vbaexpress.com/kb/getarticle.php?kb_id=208

jwilder1
09-18-2005, 12:44 PM
Thank you for clearing that up for me. Jim