excel vba global variable and concurrency

Question

I have a few questions about how global variables can be used in Excel VBA.

So my application uses an API object that's provided by a third party. Currently in all of the functions, we initialize this object and use it:

function()
   Dim api as someApi
   api.dosth

end function

I'm just wondering, instead of initialize it every single time, can i just make it a global variable? like just declare it at the beginning and initialize it in the workbook_open function? If multiple functions are running at the same time and trying to use this shared api object, will it be a problem? Also just a general question about global variables: do all the modules share the variables? Or each module can only see the global variables declared in its own code? For example if I have module a and module b in the same workbook, and api object is declared in module a and initialized in the workbook_open event, can the functions in module b use it or not?

Thanks so much!!

See here for how variable scope works in VBA support.microsoft.com/kb/141693. Basically though, yes you can declare a Public variable to hold your object. Depending on whether or not that object has "state" and what your various routines are doing with it, it should work fine. Note there isn't really "concurrency" in VBA - only one function/sub is running at any one time. — Tim Williams
If you are feeling functional, an alternative would be to pass the object as a parameter to any function/sub that needs to use it. — A. Webb

Dexter1759 Dexter1759 · Accepted Answer · 2013-02-03T20:18:10

Depending on the usage of the API it can be initialised once and used everywhere.

I would suggest reading up on global/public/private variables.

Essentially declaring a variable as Private outside of a function means the variable can only be accessed within that module. Declaring as Public, means it can be accessed across all modules with that workbook. Declaring as Global means it can be accessed across all workbooks within that instance of Excel.

If you only want to access the API anywhere with the one workbook I'd suggest creating a Public variable and initialising it when the work book opens, ensuring to close/kill any variables properly when the workbook closes.

excel vba global variable and concurrency

1 Answers