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

Community
  • 1
  • 1
Ahmed
  • 285
  • 1
  • 9
  • 16
  • 4
    Yes you can define a public variable and use it for multiple workbook provided they have to be open in same instance. – Santosh Jul 22 '13 at 06:14

2 Answers2

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

Community
  • 1
  • 1
Nemo copperfield
  • 21
  • 2
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

Community
  • 1
  • 1
Raystafarian
  • 2,902
  • 2
  • 29
  • 42
  • 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? – Ahmed Jul 23 '13 at 06:56
  • declare it outside the module – Raystafarian Jul 23 '13 at 08:29
  • Thanks for reply, Even declaring outside the module that variable will only able to use in that workbook, and not any other workbooks.. – Ahmed Jul 24 '13 at 02:54