VBA global variables, multiple workbooks

Question

I have a VB application, that uses some global variables to store data that is required by multiple forms and modules, this works fine. However if a user opens up another workbook, running the same VBA application, then they end up accessing (and changing) the same public variables.

How can I have workbook level global variables, or if this is not possible, what is the best way to store data that is accessible by all forms and modules, but only inside the specific workbook?

This is a VBA addin, and the Global variables are declared in a standard module.

Answer 1

I was able to replicate the problem with a simple xla called George:

Public harry As Variant

Public Sub setHarry(x)
    harry = x
End Sub

Public Function getHarry()
    getHarry = harry
End Function

I installed the xla. Then I created Alice.xls with a text box that called setHarry when it changed and a cell with =getHarry() in it. I kept it really simple:

Private Sub TextBox1_Change()
    Run "george.xla!setHarry", TextBox1
End Sub

I then made a copy of Alice.xls as Bob.xls and ran them both. As expected, if either workbook changes Harry, both workbooks see the result.

I say expected because xla's are like dll's in that there's only one copy in memory that everybody shares; that apparently includes global variables, which makes sense. I just wanted to test the theory.

To my mind, the best way to fix this is to use a class module instead of an ordinary module. This way you can give each workbook its own instance of the class and its variables in the Workbook_Open event. If the variables are declared public, you don't need property gets and sets, though you can use them if there's value in doing it.

Answer 2

Public myvar as Variant

Variables declared as Public are scoped to the workbook they're in. If you have another workbook with myvar, changing it's value in one won't change it in the other. Perhaps you have an event that's setting that variable from the activesheet that's firing in both projects and setting the variables to the same thing.

Answer 3

Your global variables are globally scoped to your Addin. I would store them in hidden names in each workbook, and then reset your global variables each time a workbook is activated (and also re-store the values in the deactivated workbook).

Answer 4

I think you could get the workbook name in runtime.

So, a Collection probably will do the job.

'//-- module --
Public var As Collection

Public Function SetVar(Value)
   If var Is Nothing Then
      Set var = New Collection
   End If
   var.Add Value, ThisWorkbook.Name
End Function

Public Function GetVar() As Variant
   If var Is Nothing Then
      GetVar = Null
   Else
      GetVar = var.Item(ThisWorkbook.Name)
   End If
End Function

Not sure if it'll work for your Addin though.