Объявить книгу как глобальную переменную

Ваш вопрос подразумевает, что вам нужна глобальная рабочая книга константа, а не переменная. Поскольку VBA не позволяет инициализировать объекты вне процедуры, у вас не может быть константы объекта. Лучшее, что вы можете сделать, - это иметь общедоступную переменную книги, которая инициализируется в событии.

---

Можно объявить глобальную переменную, но нельзя выполнить код для присвоения значения вне процедуры:

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

---

Так что в обычном модуле вы могли бы имейте:

Public myBook As Excel.Workbook

И в вашем Workbook_Open() событии:

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

Тогда вы можете использовать myBook в другом месте кода, не переназначая его.

Возможно, стоит взглянуть на статью чипа Пирсона о переменной области видимости в VBA здесь

Вам нужна какая-то фабрика со статическими свойствами, например, в отдельном модуле

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

Для использования просто вызовите свойство where & when вам это нужно, никаких дополнительных переменных (или наборов для них) не требуется.

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

Это лучшее, что я могу придумать до сих пор. В результате теперь есть только одно место для изменения имени файла, однако мне все еще нужно скопировать функцию SET в каждой подпрограмме. Еще не совсем идеально, но лучше, чем ничего.

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

Всякий раз, когда я сталкиваюсь с этим, я объявляю wb как открытую постоянную строку:

public wb as string = "c:\location"

Затем, по всему коду в проекте, вы можете ссылаться на

workbooks(wb).anything

Это то, что я обычно делаю, когда у меня есть глобальные переменные, которые нужно правильно инициализировать:

В общий код модуля помещаем следующий код:

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

Затем в модуле кода рабочей книги поместите:

Private Sub Workbook_Open()
    Initialize
End Sub

Кроме того, в любой "шлюзовой" подсистеме или функции (например, обработчики событий, UDFs и т. д.) который может запустить ваш код, поместите Initialize (или, возможно: If Not Initialized Then Initialize) в первую строку. Как правило, большинство субмарин не будут запущены напрямую и могут полагаться на то, что Locations будет правильно настроен абонент. Если вам нужно протестировать что-то, что не будет работать должным образом, если переменная не установлена, то вы можете просто ввести initialize непосредственно в окне непосредственного выполнения.

Вы также можете сделать это с модулем класса и полагаться на инициализатор класса, чтобы сделать работу за вас, когда он используется в модуле:

Модуль класса, называемый cLocations:

Public Workbook As Workbook

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

И где вам нравится в вашем модуле, или где угодно, если на то пошло:

Dim Locations As New cLocations

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

И затем, вы можете просто использовать Locations.Workbook для ссылки на книгу locations, и ThisWorkbook для ссылки на книгу, в которой выполняется код, и ActiveWorkbook для ссылки на книгу, которая имеет фокус. Таким образом, вы можете запустить свой код из одной книги (ThisWorkbook), используя книгу locations (Locations.Workbook) в качестве ссылки и повторите другие книги (ActiveWorkbook), чтобы добавить другой уровень автоматизации.

Если вы пройдете через код, вы увидите, что класс инициализируется только тогда, когда вы нажимаете строку кода, которая требует этого, а не когда загружается книга.

Однако я должен добавить, что в этом случае я думаю, что если вы дадите нам немного большую картину того, чего вы пытаетесь достичь, мы могли бы дать вам решение проблемы. лучше проблема, чем та, в которую вы попали во время кодирования.

Вы также можете сделать этот шаг дальше и абстрагироваться до уровня приложения, сохранить книгу locations скрытой и даже предоставить intellisense для именованных листов, если вы знаете их положение или их имя явно:

Модуль класса:

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

А затем в вашем модуле при первом использовании переменной она будет инициализирована в одной строке кода:

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

Это решение будет работать только в том случае, если вы знаете номера и имена всех листов, которые вы будете использовать из указанной книги.

В модуле объявите общую переменную листа для всех ваших листов следующим образом:

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

Создайте экземпляр этих открытых переменных в событии загрузки приложения.

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

Теперь вы можете ссылаться на эти глобальные листы в своем суб.

Например:

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

Если вы создаете модуль, скажем ExcelMod, и в этом модуле у вас есть публичная функция или подпрограмма Initialize() и другая, называемая Terminate (), вы можете инициализировать и завершать переменные уровня модуля, используя эти подпрограммы. Например, я использовал это раньше: (обратите внимание, что переменные модуля-это первое, что объявляется в верхней части модуля.)

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

Переменные являются частью всего модуля и только инициализируются и завершаются этими подпрограммами. Вы можете пройти мимо переменные в модуле и из модуля, как вы хотите, и использовать их во всех подпрограммах этого модуля без необходимости устанавливать снова. Если вам нужно использовать в другом модуле, вам нужно будет передать его в этот модуль, как обычно.

Также, как уже упоминалось, вы можете использовать событие workbook_Open для вызова суб-инициализации для создания объектов и установки их только один раз, если это необходимо.

Это то, что вам нужно?

Если я правильно понял ваш вопрос, вы создаете код, который должен работать на уровне приложения, а не на уровне рабочей книги. В этом случае почему бы вам не создать надстройку?

Весь код внутри надстройки будет иметь доступ ко всем открытым книгам на уровне приложения.

Вы можете создать надстройку или использовать модуль класса для работы со свойствами...

Но я не уверен, что это будет чище, чем простое объявление в обычном модуле и вызов этой процедуры при открытии книги тоже отлично справится .

(я использую этот метод уже довольно давно, и меня это не беспокоит)

Таким образом, вы можете использовать это в (выделенном или нет) регулярном модуле :

'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

И вызов процедура установки всех переменных в модуле ThisWorkbook :

Private Sub Workbook_Open()
    Set_All_Global_Variables
End Sub