Public Variable

[Guest]

Is it possible to set a value for a public variable in one workbook, and then
use its value in another workbook? Of course, the first workbook would
remain open.

What I am trying to do is set an "authorization" value for a user when EXCEL
starts, and then use that value to allow or disallow use of functions in my
own addin.

Thanks.

 

[Dave Peterson]

How about adding a function in the workbook with the public variable. That
function's only job would be to return the value of the public variable.

Option Explicit
Dim YourPublicVariableNameHere As Variant
Sub aa()
'initialize it someway
YourPublicVariableNameHere = "testme"
End Sub
Public Function GetVal() As Variant
GetVal = YourPublicVariableNameHere
End Function

and in the other workbook:

Option Explicit
Sub auto_open()
Dim myVar As Variant
myVar = Application.Run("'book1.xls'!getval")
MsgBox myVar
End Sub