3

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.

Improve this question
0

5 Answers 5

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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

Improve this answer

2 Comments

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

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

Improve this answer

2 Comments

Unfortuantley this needs to be a workbook independent addin. Will have to look for another option.
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.
1

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.

Improve this answer

1 Comment

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

If you declare your variables Public, they will be available to all code in the Workgroup where you defined it, regardless of which instance is upfront. Declare and set your variables at the start of your app (and check their status regularly).

Public gwbkMarket As Workbook Public gstrThisWorkbookName As String gstrThisWorkbookName = ThisWorkbook.Name Set wbkMarket = Application.Workbooks(gstrThisWorkbookName)

You can refer to your sheet values and code thereafter in the following way: Workbooks(gstrThisWorkbookName).Worksheets(cstrSettingsTab).Range(cstrAColDBPriceDif & clngFirstRow)

Or

gwbkMarket.Worksheets(cstrSettingsTab).Range(cstrAColDBPriceDif & clngFirstRow)

Or

Call DummyProc(22) ' in the same Workboook

Some Excel functions require different approach, however. If you attempt to ".Select" a range, you'll get an error 1004. Therefore:

If gstrThisWorkbookName = ActiveWorkbook.Name Then gwbkMarket.Worksheets(cstrDataTab).Select Workbooks(gstrThisWorkbookName).Worksheets(cstrDataTab).Range(cstrColBuyFactor & clngFirstRow).Select End If

"gstrThisWorkbookName" usually, but not always be equal to "ThisWorkbook"

Good Luck.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.