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

Ответ 1

Я думаю, что самым универсальным способом для глобальной переменной workbook будет создание модуля с процедурой Public Property Get. Вы можете ссылаться на него, не вызывая код в первую очередь, и вам не нужно беспокоиться о том, открыт ли файл или нет.

Вот пример кода модуля для одной из переменных:

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

Вы можете использовать его в любом месте кода как глобальную переменную:

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

Ответ 2

Ваш вопрос подразумевает, что вы хотите, чтобы глобальная рабочая книга была постоянной, а не переменной. Поскольку 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 здесь

Ответ 3

то, что вы хотите, это своего рода Factory со статическими свойствами, например, в отдельном модуле

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

Чтобы использовать, просто вызовите свойство где и когда оно вам нужно, никаких дополнительных переменных (или наборов для них) не требуется.

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

Ответ 4

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

public wb as string = "c:\location"

Затем во всем коде проекта вы можете обратиться к

workbooks(wb).anything

Ответ 5

Это лучшее, что я могу придумать до сих пор. В результате есть только одно место для изменения имени файла, однако мне все еще нужно скопировать функцию 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

Ответ 6

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

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

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

Ответ 7

Если вы создадите модуль, скажем 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 для вызова элемента инициализации для создания объектов и при необходимости установить их только один раз.

Это то, что вы после?

Ответ 8

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

Модуль класса, называемый 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 для ссылки на книгу местоположений и ThisWorkbook для обозначения книги, в которой работает код, и ActiveWorkbook для ссылки на рабочую книгу с фокусом. Таким образом, вы можете запускать свой код из одной книги (ThisWorkbook), используя справочник мест (Locations.Workbook) в качестве ссылки и перебирать другие книги (ActiveWorkbook), чтобы добавить еще один уровень автоматизации.

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

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

Вы также можете сделать этот шаг дальше и отвлечься до уровня приложения, скрыть книгу рабочих мест и даже обеспечить 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

Ответ 9

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

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

Ответ 10

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

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

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

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

Ответ 11

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

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

(Я использовал этот метод довольно часто и не беспокоился)

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

'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