Как объявить глобальные переменные в Excel VBA видимыми в рабочей книге

У меня есть вопрос о глобальной области действия и абстрагирован проблема на простой пример:

В книге Excel: В Лист1 у меня есть две (2) кнопки.
Первый помечен как SetMe и связан с подпрограммой в Лист1 модуль:
Код Sheet1:

Option Explicit
Sub setMe()
    Global1 = "Hello"
End Sub

Вторая помечена как ShowMe и связана с подпрограммой в модуле ThisWorkbook:
Код этой книги:

Option Explicit
Public Global1 As String
Debug.Print("Hello")
Sub showMe()
    Debug.Print (Global1)
End Sub

Нажав SetMe, вы получите компилятор error: variable not defined.
Когда я создаю отдельный модуль и перемещаю объявление Global1, все работает.

Итак, мой вопрос: Все, что я прочитал, гласит, что глобальные переменные, объявленные в верхней части модуля, вне любого кода должны быть видны всем модулям проекта. Ясно, что это не так. Если мое понимание модуля неверно.
Объекты Sheet1, Sheet2, ThisWorkbook,..., которые поставляются с рабочей книгой: это не модули, способные объявлять переменные в глобальной области?

Или это единственное место, где можно объявить глобальное, в отдельном модуле типа Модули.

Ответ 1

Ваш вопрос: это не модули, способные объявлять переменные в глобальной области?

Ответ: ДА, они "способны"

Единственным моментом является то, что ссылки на глобальные переменные в этой книге или на листе должны быть полностью квалифицированными (т.е. называемыми ThisWorkbook.Global1, например) Ссылки на глобальные переменные в стандартном модуле должны быть полностью квалифицированы только в случае двусмысленности (например, если имеется более одного стандартного модуля, определяющего переменную с именем Global1, и вы хотите использовать ее в третьем модуле).

Например, место в коде Sheet1

Public glob_sh1 As String

Sub test_sh1()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

место в коде этой книги

Public glob_this As String

Sub test_this()
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

и в стандартном модуле

Public glob_mod As String

Sub test_mod()
    glob_mod = "glob_mod"
    ThisWorkbook.glob_this = "glob_this"
    Sheet1.glob_sh1 = "glob_sh1"
    Debug.Print (glob_mod)
    Debug.Print (ThisWorkbook.glob_this)
    Debug.Print (Sheet1.glob_sh1)
End Sub

Все три подпрограммы работают нормально.

PS1: Этот ответ основан, в основном, на информации от здесь. Очень стоит прочитать (от великого Чипа Пирсона).

PS2: Ваша строка Debug.Print ("Hello") даст вам ошибку компиляции Invalid outside procedure.

PS3: Вы можете (частично) проверить свой код с помощью Debug → Скомпилировать VBAProject в редакторе VB. Будут появляться все ошибки компиляции.

PS4: проверьте также Поместите код Excel-VBA в модуль или лист?.

PS5: Возможно, вы не сможете объявить глобальную переменную в, скажем, Sheet1, и использовать ее в коде из другой книги (чтение http://msdn.microsoft.com/en-us/library/office/gg264241%28v=office.15%29.aspx#sectionSection0, я не проверял этот момент, поэтому этот вопрос еще не подтвержден как таковой). Но вы все равно не хотите делать это в своем примере.

PS6: Есть несколько случаев, которые приводят к двусмысленности в случае не полностью квалифицирующих глобальных переменных. Вы можете немного помахать, чтобы найти их. Они являются ошибками компиляции.

Ответ 2

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

Откройте новую книгу Excel и в редакторе Excel VBA щелкните правой кнопкой мыши по Модулям- > Вставить- > Модуль
Во вновь добавленном модуле 1 добавьте объявление; Public Global1 As String
в листе Лист модуля VBA1 (лист1) помещает фрагмент кода:

Sub setMe()
      Global1 = "Hello"
End Sub

в листе листа VBA Module Sheet2 (Sheet2) введите фрагмент кода:

Sub showMe()
    Debug.Print (Global1)
End Sub

Выполнить последовательность Sub setMe(), а затем Sub showMe(), чтобы проверить глобальную видимость/доступность var Global1

Надеюсь, это поможет.