Declare a Workbook as a Global variable Declare a Workbook as a Global variable vba vba

Declare a Workbook as a Global variable


I think the most universal way for workbook global variable would be creating a module with a Public Property Get procedure. You can refer to it without calling any code first, and you don't have to worry if the file is open or not.

Here is the sample module code for one of the variables:

Private wLocations As WorkbookPublic Property Get Locations() As Workbook  Const sPath As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"  Dim sFile As String  If wLocations Is Nothing Then      'extract file name from full path      sFile = Dir(sPath)      On Error Resume Next      'check if the file is already open          Set wLocations = Workbooks(sFile)      If wLocations Is Nothing Then        Set wLocations = Workbooks.Open(sPath)      End If      On Error GoTo 0  End If  Set Locations = wLocationsEnd Property

You can use it anywhere in the code as a global variable:

Sub Test()  Debug.Print Locations.Worksheets.CountEnd Sub


Your question implies that you want a global workbook constant, not a variable. Because VBA doesn't allow objects to be initialised outside of a procedure, you can't have an object constant. The best you can do is have a public workbook variable that's initialised in an event.


You can declare a global variable, but you can't execute code to assign a value outside of a procedure:

Public myBook As Excel.WorkbookSub AssignWorkbook()    Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside subEnd SubSub TestItWorked()    MsgBox myBook.NameEnd Sub

So in a normal module you could have:

Public myBook As Excel.Workbook

And in your Workbook_Open() event:

Private Sub Workbook_Open()    Set myBook = Workbooks.Open("C:\SomeOtherBook.xlsx")End Sub

Then you can use myBook elsewhere in your code without having to re-assign it.

It might be worth having a look at Chip Pearson's article about variable scope in VBA here


what you want is some sort of Factory with static properties, for example in a separate module

mFactoryWkbs

Private m_WkbLocations           As WorkbookPrivate m_WkbMergeBook           As WorkbookPublic Property Get LOCATIONS() As Workbook    If m_WkbLocations Is Nothing Then        Set m_WkbLocations= Workbooks.Open("wherever")    End If    Set LOCATIONS = m_WkbLocationsEnd PropertyPublic Property Get MERGEBOOK () As Workbook    If m_WkbMergeBook Is Nothing Then        Set m_WkbMergeBook = Workbooks.Open("wherever")    End If    Set MERGEBOOK = m_WkbMergeBook End Property

To use, just call the property where & when you need it, no extra variables (or Sets for them) required.

TotalRowsMerged = MERGEBOOK.Worksheets("Sheet1").UsedRange.Rows.Count