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.

Solution 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.

Solution 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.

Solution 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).

Solution 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.

View more solutions

Comments

[John F]

These variables are declared in a standard module, rather than in a workbook, or sheet object, which I think may be where the problem lies. However this is where they need to be for the rest of the code to access them. This is a VBA addin, rather than a worksheet with code

[Dick Kusleika]

Right, I meant scoped to the project. I just tested it with two identical workbooks and global variable dimmed in a standard module and changing one didn't change the other. I assume you haven't set a reference between the workbooks.

[John F]

Unfortuantley this needs to be a workbook independent addin. Will have to look for another option.

[Charles Williams]

I understand that its a workbook independent addin. You make this work by trapping application-level events so that code in your addin gets called each time a non addin workbook is activated.<br/> Your code then checks the workbook that has been activated for your hidden names and if they are present copies their settings to your global variables. You would also need to handle storing the global variables in the hiddeen names on workbook deactivation.

[John F]

Thanks, I had a feeling that is what was happening. Unfortunately I can't create a class instance in the workbook_open event, as this addin needs to work for any workbook. Will have to find another way to do it.

[Marc Thibault]

I suggested using the Workbook_Open event because it's automatic. You could as easily use a keyboard macro or whatever else you're using now to launch the application. Just add the code to instantiate the class.

[Oneide]

By the way, I noticed a flaw in the code above. You must check if the value is already in the collection before setting it (you cannot add something that is already there). Maybe there are other flaws, but it should be easy to make it work.