Declare a Workbook as a Global variable

Question

I am starting to write a code that will become applicable to multiple workbooks, but always uses the same reference workbook. The code will have many subs, and as I am trying to avoid to dim a variable to the reference workbook in every sub I would like to declare them Global.

First I had:

Global Locations As Excel.Workbook
Set Locations = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")

Which gave me:

"Compile error: Invalid outside procedure"

After some googling I found the following bit of code somewhere:

Public Const Locations As Excel.Workbook = "Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")"

Which gave me:

"Compile error: Expected: type name"

---

Edit:

Using:

Public Const Locations As Excel.Workbook = "Workbooks.Open('M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx')"

(Single quotation marks within the Workbooks.Open statement) results as the same error as when using double quotation marks.

Who knows what I am doing wrong?

Edit2:

I also tried to declare the variables in the "ThisWorkbook", following this answer using:

Private Sub Workbook_Open()
Dim Locations As Excel.Workbook
Dim MergeBook As Excel.Workbook
Dim TotalRowsMerged As String


Locations = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx")
MergeBook = Workbooks.Open("M:\My Documents\MSC Thesis\Italy\Merged\DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
End Sub

But then it returns an

"Object Required"

within my module.

Edit3:

I now have this which works, but has the downside of having to copy the SET lines into every Sub, there has to be a better way to do this?

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks("DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

Answer 1

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 Workbook

Public 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 = wLocations
End Property

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

Sub Test()
  Debug.Print Locations.Worksheets.Count
End Sub

Answer 2

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

Sub AssignWorkbook()
    Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside sub
End Sub

Sub TestItWorked()
    MsgBox myBook.Name
End 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

Answer 3

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

mFactoryWkbs

Private m_WkbLocations           As Workbook
Private m_WkbMergeBook           As Workbook

Public Property Get LOCATIONS() As Workbook
    If m_WkbLocations Is Nothing Then
        Set m_WkbLocations= Workbooks.Open("wherever")
    End If
    Set LOCATIONS = m_WkbLocations
End Property

Public 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

Answer 4

This is the best I can come up with until now. The result is that there is now only one place to change the name of the file, however I still need to copy the SET function within every subroutine. Not completely ideal yet, but better then nothing.

Public Const DESTBOOK = "DURUM IT yields merged.xlsm"

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks(DESTBOOK)
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

Answer 5

Whenever I run into this, I declare wb as a public constant string:

public wb as string = "c:\location"

Then, throughout the code in the project, you can refer to

workbooks(wb).anything

Answer 6

This solution will work only if you know the numbers and names of all the worksheets that you will use from referenced workbook.

In your module, declare worksheet public variable for all your worksheets as follows:

Public sht1 As Worksheet
Public sht2 As Worksheet
Public sht3 As Worksheet
...

Instantiate these public variables in the application load event.

Sub Workbook_Open()

    Workbooks.Open ("your referenced workbook")

    'Instantiate the public variables
    Set sht1 = Workbooks("Test.xlsm").Sheets("Sheet1")
    Set sht2 = Workbooks("Test.xlsm").Sheets("Sheet2")
    Set sht3 = Workbooks("Test.xlsm").Sheets("Sheet3")

End Sub

Now you can refer these global worksheets in your sub.

For example:

Sub test()
    MsgBox sht1.Range("A1").Value
    MsgBox sht2.Range("A1").Value
    MsgBox sht3.Range("A1").Value
End Sub

Answer 7

If you create a Module say ExcelMod and within that Module you have a public function or subroutine Initialize() and another one called Terminate() you can initialize and terminate Module level variables using those routines. For example I have used this before: (Note that module variables are the first thing declared at the top of the module.)

Dim excelApp As Object, wb As Workbook, ws As Worksheet

Sub Initialize()
    Set excelApp = CreateObject("Excel.Application")
    Set wb = Workbooks.Open("C:\SomeOtherBook.xlsx")
End Sub

Sub Terminate()
    Set excelApp = Nothing
    Set wb = Nothing
End Sub

The variables are part of the entire module and only get initialized and terminated with these subroutines. You can pass the variables in and out of the module as you wish and use them in ALL of this modules subroutines without having to set again. If you need to use in another module you will need to pass it to that module as you normally would.

Also as others have mentioned you can use the workbook_Open event to call the initialization sub to create the objects and set them only once if needed.

Is this what you are after?

Answer 8

You could also do it with a class module and rely on the class initialiser to do the work for you when it gets used in the module:

Class module called cLocations:

Public Workbook As Workbook

Private Sub Class_Initialize()
    Set Workbook = Workbooks.Open("C:\Temp\temp.xlsx")
End Sub

And where you like in your module, or anywhere for that matter:

Dim Locations As New cLocations

Sub dosomething()
    Locations.Workbook.Sheets(1).Cells(1, 1).Value = "Hello World"
End Sub

And then, you can just use Locations.Workbook to refer to the locations workbook, and ThisWorkbook to refer to the workbook the code is running in and ActiveWorkbook to refer to the workbook that has focus. This way you could run your code from one workbook (ThisWorkbook), using the locations workbook (Locations.Workbook) as a reference and iterate over other workbooks (ActiveWorkbook) to add another level of automation.

If you step through the code, you will see that the class is only initialised when you hit a line of code that requires it, not when the workbook is loaded.

I must add though, in this case I think if you give us a slightly bigger picture of what you are trying to achieve we might be able to give you a solution to a better problem than the one you have hit while coding.

You could also take this a step further, and abstract to the application level, keep the locations workbook hidden, and even provide intellisense for named sheets if you know their position or their name explicitly:

Class module:

Private App As Application
Public Workbook As Workbook
Public NamedSheet As Worksheet

Private Sub Class_Initialize()
    Set App = New Application
    App.Visible = False
    App.DisplayAlerts = False
    Set Workbook = App.Workbooks.Open("C:\Temp\temp.xlsx") 'maybe open read only too?
    Set NamedSheet = Workbook.Sheets("SomethingIKnowTheNameOfExplicitly")
End Sub

Public Sub DoSomeWork()
    'ThisWorkbook refers to the one the code is running in, not the one we opened in the initialise
    ThisWorkbook.Sheets(1).Cells(1, 1).Value = Wb.Sheets(1).Cells(1, 1).Value
End Sub

Public Function GetSomeInfo() As String
    GetSomeInfo = NamedSheet.Range("RangeIKnowTheNameOfExplicitly")
End Function

And then in your module, the first time you use the variable it will be initialised in one line of code:

Dim Locations As New cLocations
Dim SomeInfo

Sub DoSomething()
    SomeInfo = Locations.GetSomeInfo 'Initialised here, other subs wont re-initialise

    Locations.Workbook.Sheets(1).Cells(1, 1).Value = _ 
        ThisWorkbook.Sheets(1).Cells(1, 1).Value

    Locations.NamedSheet.Cells(1,1).Value = "Hello World!"

    Locations.Workbook.Save
End Sub

Answer 9

This is the sort of thing I usually do when I have global variables that need to be properly initialized:

In a general code module put the following code:

Public Initialized As Boolean
Public Locations As Workbook

Sub Initialize()
    If Initialized Then Exit Sub
    Const fname As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"
    On Error Resume Next
        Set Locations = Workbooks(Dir(fname))
    On Error GoTo 0
    If Locations Is Nothing Then
        Set Locations = Workbooks.Open(fname)
    End If
    Initialized = True
End Sub

Then in the workbook's code module put:

Private Sub Workbook_Open()
    Initialize
End Sub

Furthermore, in any "gateway" sub or function (e.g. event-handlers, UDFs, etc.) which might launch your code, put Initialize (or maybe: If Not Initialized Then Initialize) as the first line. Typically most subs won't be directly launched and can rely on Locations being properly set by the caller. If you need to test something which won't run properly if the variable isn't set then you can just type initialize directly in the Immediate Window.

Answer 10

If I understand your question correctly, you are creating a code that should work on the application level and not on workbook level. In this case why don't you create an add-in.

All the code inside the add-in will have access to all the open workbooks at application level.

Answer 11

You might want to create an Add-In, or use a Class module to work with properties, ...

But I'm not sure it'll be that cleaner than a simple declaration in a regular module and a call to that procedure at workbook's open will do the trick just fine too .

(I have been using this method for quite some times and haven't been bothered)

So you can use this in a (dedicated or not) regular module :

'Set the path to your files
Public Const DESTBOOK = "M:\My Documents\MSC Thesis\Italy\Merged\DURUM IT yields merged.xlsm"
Public Const LOCBOOK = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"

'Declare all global and public variables
Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

'Set all variable (Procedure call from Workbook_Open)
Sub Set_All_Global_Variables()
    Set Locations = Set_Wbk(LOCBOOK)
    Set MergeBook = Set_Wbk(DESTBOOK)
    TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
    '...
End Sub

'Function to check if the workbook is already open or not
Function Set_Wbk(ByVal Wbk_Path As String) As Workbook
    On Error Resume Next
        Set Set_Wbk = Workbooks(Dir(Wbk_Path))
    On Error GoTo 0
    If Set_Wbk Is Nothing Then
        Set Set_Wbk = Workbooks.Open(Wbk_Path)
    End If
End Function

And call the procedure setting all the variables in the ThisWorkbook module :

Private Sub Workbook_Open()
    Set_All_Global_Variables
End Sub