Passing Variables between Workbooks

  • Thread starter Thread starter Patrick Kirk
  • Start date Start date
P

Patrick Kirk

Does anyone know how to pass/call a global variable between two workbooks?
Both workbooks have a Global variable declared in module1 called wkName. Any
thoughts?

Set wbResults2 = ActiveWorkbook
Set wbResults1 = Workbooks.Open(StrName)
???
wbResults2.Close savechanges:=False
 
Hello,

You should set the workbook name to a variable, then you can use it as
you want.

Dim AWS as String
AWS = ActiveWorkbook.Name

Then you can do

Workbooks(AWS).Close
Windows(AWS).Activate


And so on.


HTH,
JP
 
To link to the code of another book, you would need to make it a reference
(like for a library reference). This is not at all convenient.

You could use a Named Range or just a book Name to stored that variable.
Workbook Name
-------------------
' say Book1.xls
Workbooks("book1.xls").Names.Add "myvar","=""Hello"""
creates a name called 'myvar' and set it to the value 'Hello'
or for a number:
Workbooks("book1.xls").Names.Add "myvar","=3"
creates a name called 'myvar' and set it to the value 'Hello'

Now from another book, you can retrieve this value from a worksheet:
=book1!myvar
or through code
application.evaluate(aworkbooks("book1.xls").Names("myvar").Value)

Named Range
----------------
Similar to above.

Book name vs. Named Range: not sure, but I would tend to say that the later
is accessible when the book is closed, whcih could be very convenient. But i
haven't tested.
 
JP, I tried and get the run-time error 438, object doesn't suipport this
property or method. Any thoughts?
 
Back
Top