I have an Excel (2010) Add-In file that adds various buttons to the home tab of my Ribbon. One of the modules in that .xlam file declares a public variable. When the code that populates that variable is finished the variable still holds the relevant value.
Is it possible to make that variable available to code in another module that's not within the .xlam file but resides in the active workbook?
If not, what's the simplest way of passing the value over to the active workbook? All I can think of is writing the variable's value in to a spare cell on the active workbook and picking it up from there but there's bound to be a more elegant solution...
Thanks,
Ken
using a global variable
-
- Panoramic Lounger
- Posts: 8225
- Joined: 25 Jan 2010, 09:09
- Location: retirement
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1113
- Joined: 21 Jan 2011, 16:51
- Location: Florida
Re: using a global variable
Define a custom document property in the workbook that you want to pass the value to and store it there.
See Charles Pearson's page on creating, reading and writing built-in and custom document properties.
See Charles Pearson's page on creating, reading and writing built-in and custom document properties.
PJ in (usually sunny) FL
-
- Panoramic Lounger
- Posts: 8225
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: using a global variable
Yes, the code in all it's (lack of) glory is mine.HansV wrote:Can you edit the code in the add-in (.xlam)?
Ta, I'll look into that idea.PJ_in_FL wrote:Define a custom document property in the workbook that you want to pass the value to and store it there...
Ken
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: using a global variable
Let's say you have a variable
Public MyVar As ...
Create a function in the add-in (in a standard module, for example the same module that contains the above declaration):
You can then run code like this in other workbooks:
x = Application.Run("GetMyVar")
Public MyVar As ...
Create a function in the add-in (in a standard module, for example the same module that contains the above declaration):
Code: Select all
Public Function GetMyVar()
GetMyVar = MyVar
End Function
x = Application.Run("GetMyVar")
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8225
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: using a global variable
Hans, that works.
Ken
Ken
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: using a global variable
And if you need to set the variable, you can create a procedure in the add-in:
Use like this from any open workbook to assign the value 37 to MyVar:
Application.Run "SetMyVar", 37
Code: Select all
Public Sub SetMyVar(v)
MyVar = v
End Sub
Application.Run "SetMyVar", 37
Best wishes,
Hans
Hans