using a global variable

[stuck]

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

[HansV]

Can you edit the code in the add-in (.xlam)?

[PJ_in_FL]

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.

[stuck]

Can you edit the code in the add-in (.xlam)?

Yes, the code in all it's (lack of) glory is mine.

Define a custom document property in the workbook that you want to pass the value to and store it there...

Ta, I'll look into that idea.

Ken

[HansV]

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):

Code: Select all

Public Function GetMyVar()
    GetMyVar = MyVar
End Function

You can then run code like this in other workbooks:

x = Application.Run("GetMyVar")

[stuck]

Hans, that works.

Ken

[HansV]

And if you need to set the variable, you can create a procedure in the add-in:

Code: Select all

Public Sub SetMyVar(v)
    MyVar = v
End Sub

Use like this from any open workbook to assign the value 37 to MyVar:

Application.Run "SetMyVar", 37