VBA Excel. Программное создание модуля

Динамическое создание стандартного программного модуля из кода VBA Excel. Запись в стандартный модуль текста процедур. Программное удаление модуля.

Содержание

Создание стандартного модуля

Стандартный программный модуль создается как новый элемент коллекции VBComponents проекта VBA:

1 2	Dim myModule As Object Set myModule = ThisWorkbook.VBProject.VBComponents.Add(1)

Число (1) означает, что создается стандартный модуль, если заменить его на (3), будет создана форма.

Запись текста процедуры в модуль

Текст процедуры

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

Private Sub MyNewSub()

Cells(1, 1) = 15

Cells(1, 1).Interior.Color = vbYellow

Cells(1, 2) = 25

Cells(1, 2).Interior.Color = vbGreen

Cells(1, 3) = Cells(1, 1) * Cells(1, 2)

Cells(1, 3).Interior.Color = vbCyan

End Sub

Программная запись текста процедуры в модуль может быть осуществлена с помощью метода CodeModule.AddFromString или метода CodeModule.InsertLines.

Метод CodeModule.AddFromString

Метод CodeModule.AddFromString всегда записывает текст процедуры сразу после раздела Declarations и других строк, не относящихся к процедурам (например, комментарии). То есть, если в модуле уже есть процедуры, новая будет записана перед самой первой из них.

Пример кода VBA Excel, записывающего текст процедуры в программно созданный стандартный модуль «myModule» методом CodeModule.AddFromString:

myModule.CodeModule.AddFromString ("Private Sub MyNewSub()" & vbNewLine & _

" Cells(1, 1) = 15" & vbNewLine & _

" Cells(1, 1).Interior.Color = vbYellow" & vbNewLine & _

" Cells(1, 2) = 25" & vbNewLine & _

" Cells(1, 2).Interior.Color = vbGreen" & vbNewLine & _

" Cells(1, 3) = Cells(1, 1) * Cells(1, 2)" & vbNewLine & _

" Cells(1, 3).Interior.Color = vbCyan" & vbNewLine & _

"End Sub")

Метод CodeModule.InsertLines

Метод CodeModule.InsertLines предназначен для построчной записи текста процедуры. Обычно этот метод используется для добавления строк в конце модуля. Перед вставкой новых строк можно определить с помощью свойства CodeModule.CountOfLines количество уже имеющихся строк и начать вставку следующих.

Пример кода VBA Excel, записывающего текст процедуры в программно созданный стандартный модуль «myModule» методом CodeModule.InsertLines:

Dim n As Integer

With myModule.CodeModule

n = .CountOfLines

.InsertLines n + 1, "Private Sub MyNewSub()"

.InsertLines n + 2, " Cells(1, 1) = 15"

.InsertLines n + 3, " Cells(1, 1).Interior.Color = vbYellow"

.InsertLines n + 4, " Cells(1, 2) = 25"

.InsertLines n + 5, " Cells(1, 2).Interior.Color = vbGreen"

.InsertLines n + 6, " Cells(1, 3) = Cells(1, 1) * Cells(1, 2)"

.InsertLines n + 7, " Cells(1, 3).Interior.Color = vbCyan"

.InsertLines n + 12, "End Sub"

End With

Код с методом InsertLines выглядит несколько аккуратней из-за отсутствия амперсандов и ключевых слов переноса строки (vbNewLine), чем код с методом AddFromString.

Вставка кнопки на рабочий лист

Чтобы продемонстрировать работоспособность процедуры «MyNewSub», записанной в динамически созданный стандартный модуль, нам понадобится кнопка на рабочем листе, которую мы свяжем с этой процедурой.

Программная вставка кнопки из коллекции «Элементы управления формы» на активный рабочий лист Excel:

Dim myButton As Shape

Set myButton = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 100, 100, 100, 20)

'Числа 100, 100, 100, 20 – это значения параметров кнопки Left, Top, Width, Height

'Выбираем кнопку методом Select, чтобы она перешла в режим редактирования

myButton.Select

With Selection

'Меняем надпись на кнопке

.Characters.Text = "Новая кнопка"

'Назначаем кнопке процедуру MyNewSub

.OnAction = myModule.Name & ".MyNewSub"

End With

Пример кода в сборе

Динамическое создание стандартного модуля, программная запись в него процедуры «MyNewSub», вставка на рабочий лист кнопки из коллекции «Элементы управления формы» и назначение ей процедуры «MyNewSub»:

Sub Primer()

'Создаем стандартный модуль в текущей книге, как новый экземпляр коллекции "VBComponents"

Dim myModule As Object

Set myModule = ThisWorkbook.VBProject.VBComponents.Add(1)

'Записываем текст процедуры "MyNewSub" в программно созданный модуль "myModule"

Dim n As Integer

With myModule.CodeModule

n = .CountOfLines

.InsertLines n + 1, "Private Sub MyNewSub()"

.InsertLines n + 2, " Cells(1, 1) = 15"

.InsertLines n + 3, " Cells(1, 1).Interior.Color = vbYellow"

.InsertLines n + 4, " Cells(1, 2) = 25"

.InsertLines n + 5, " Cells(1, 2).Interior.Color = vbGreen"

.InsertLines n + 6, " Cells(1, 3) = Cells(1, 1) * Cells(1, 2)"

.InsertLines n + 7, " Cells(1, 3).Interior.Color = vbCyan"

.InsertLines n + 12, "End Sub"

End With

'Вставляем на активный рабочий лист кнопку из коллекции "Элементы управления формы"

Dim myButton As Shape

Set myButton = ActiveSheet.Shapes.AddFormControl(xlButtonControl, 100, 100, 100, 20)

'Меняем надпись на кнопке и назначаем ей процедуру "MyNewSub"

myButton.Select

With Selection

.Characters.Text = "Новая кнопка"

.OnAction = myModule.Name & ".MyNewSub"

End With

End Sub

Вы можете скопировать процедуру «Primer» в стандартный модуль вашего проекта VBA, запустить ее и ознакомиться с результатами ее работы. Код записан и протестирован в Excel 2016.

Удаление стандартного модуля

Программное удаление стандартного модуля с именем «Module4» из проекта VBA Excel:

Sub RemoveModule()

With ThisWorkbook.VBProject

.VBComponents.Remove .VBComponents("Module4")

End With

End Sub

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

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

5 комментариев для “VBA Excel. Программное создание модуля”

1 29.12.2020 в 09:43

Здравствуйте!
Известно, что запись вида
Cells(1, 1) = 15
эквивалентна записи
Cells(1, "A") = 15
или записи
dim x as Byte, y as Byte x=1 y=1 Cells(x, y) = 15
А можно ли использовать в переменной букву?
Что-нибудь такого плана:
dim x as Byte, y as String x=1 y="A" Cells(x, y) = 15
Спасибо!
1. Евгений 29.12.2020 в 11:51
  
  Добрый день!
  В переменной можно использовать букву и ваш пример работает правильно:
  
  1
  2
  3
  4
  5
  6
  
  Sub Test()
      Dim x As Byte, y As String
      x = 1
      y = "A"
      Cells(x, y) = 15
  End Sub
  
  Только я бы рекомендовал вместо Dim x As Byte использовать Dim x As Long, чтобы выделенной памяти хватило переменной на количество строк всего листа.
2. 1. 1 29.12.2020 в 12:07
    
    Действительно, работает…
    Спасибо!
aui 02.07.2021 в 11:55

Подскажите, пожалуйста, в чем проблема.
Имеется Модуль1, Из него программно создаю пустой Модуль2 и хочу, чтобы после его создания продолжилось выполнение кода из Модуля1.
Модуль 2 создается через

1

Set myModule = ThisWorkbook.VBProject.VBComponents.Add(1)

Модуль создается, но после этого открывается в редакторе кода и выполнение останавливается.
на панели дебага кнопка ран становится неактивной, доступны только пауза и стоп.
Не могу понять что происходит, ошибок никаких не вылетает…
1. Евгений 02.07.2021 в 12:50
  
  Попробуйте запустить у себя процедуру Primer из параграфа «Пример кода в сборе». Там, после создания модуля и записи в него текста процедуры, создается еще кнопка. Если сработает, сравните Primer с вашим кодом.

Обсуждение закрыто.