0

Is it possible to use one variable in Excel VBA across multiple workbooks? Without using Add-in approach? as mentioned in here.

For e.g I am saving Windows User name in var sysUser and want to use this variable in multiple workbooks, how can I do that?

Thanks and best regards

Ahmed

Improve this question
1
  • 4
    Yes you can define a public variable and use it for multiple workbook provided they have to be open in same instance. Commented Jul 22, 2013 at 6:14

2 Answers 2

Reset to default
2

http://www.cpearson.com/excel/trulyglobalvariables.htm

This link contains two great solutions to this problem. Be sure to drill down to the "hidden name" technique's link which is short and sweet.

Here's the crux of chip's hidden name solution

Application.executexcel4mAcro("SET.NAME(""test"",1)")

See the link for full details

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Typo: Left an e out after execute
that's really helpful
0

source

Public variables can be used in any procedures in the project. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared.

You would need public name as type to be outside of any function as well as noted here

Improve this answer

3 Comments

Thanks for reply, as per your post we can use Public variables in any project if we add reference of the project another file, but what if we don't want to add reference? are there any other ways?
declare it outside the module
Thanks for reply, Even declaring outside the module that variable will only able to use in that workbook, and not any other workbooks..

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.