![Изображение отсюда: https://www.freepik.com/free-vector/settings-concept-illustration_9793179.htm#query=settings&position=2&from_view=search Изображение отсюда: https://www.freepik.com/free-vector/settings-concept-illustration_9793179.htm#query=settings&position=2&from_view=search](https://habrastorage.org/getpro/habr/upload_files/728/241/015/728241015e50d899afcea1436fa7fcf1.jpeg)
Я часто пользуюсь конфигурацией при написании VBA макросов в Excel. Иногда она нужна в формах для сохранения настроек, иногда для сохранения каких-то получаемых в процессе выполнения макроса данных.
Долгое время я искал информацию о том как работать с конфигом в VBA.
Есть множество различных вариантов, от хранения конфигурации в коде, до выноса ее в отдельный файл.
Сегодня я хотел бы пошагово рассказать о том как я храню и обрабатываю конфигурацию макроса в книге Excel.
Создаем лист ConfigSheet
У меня под рукой был Excel 2010, но в данном случае версия не имеет значения.
Для начала создаем отдельный лист. Я назвал его config, но это не принципиально. Что же действительно принципиально, так это CodeName
листа:
![CodeName листа CodeName листа](https://habrastorage.org/getpro/habr/upload_files/de6/034/661/de6034661496d3241d8c96157abf6801.jpg)
Если вы вдруг не знали, листы документа Excel в VBA – это, ни что иное, как объект класса Worksheet
. Обращаемся к справке и видим у объекта Worksheet
необходимое свойство (перезаписать его программно, несмотря на Read-only, можно, но об этом в другой раз):
![](https://habrastorage.org/getpro/habr/upload_files/66d/dd1/0de/66ddd10de518cbec7246bca48abc178c.jpg)
Чтобы было проще обращаться к нашему Config листу, меняем ему значение поля (Name) в свойствах (если у вас их нет, нажмите F4 или View -> Properties Window, а если у вас нет структуры с проектом, нажмите Ctrl+R или View -> Project Explorer).
Таким образом, мы дополнительно защищаемся от ошибки в случае изменения обычного имени листа, а так же получаем автокомплит.
![Вызываем автокомплит с помощью Ctrl+Space Вызываем автокомплит с помощью Ctrl+Space](https://habrastorage.org/getpro/habr/upload_files/fdc/266/dd5/fdc266dd5f8449b386c36f4ab608e752.jpg)
Кстати, так как лист – это объект, мы можем обращаться так же к его методам, полям и свойствам через точку, как обычно:
![Вызываем методы и свойства Вызываем методы и свойства](https://habrastorage.org/getpro/habr/upload_files/5c7/003/e72/5c7003e7221a626f8296455d12229b08.jpg)
Этим мы и воспользуемся, но об этом чуть позже.
Создаем ListObject
Итак, как вы уже догадались, всю информацию мы будем сохранять в таблицу, а именно в объект ListObject
.
Для этого на нашем листе создаем пустую таблицу с двумя столбцами Key
и Value
:
![создаем таблицу создаем таблицу](https://habrastorage.org/getpro/habr/upload_files/75a/b66/e04/75ab66e04489db84582f6585b48e7e5e.jpg)
Теперь осталось в Конструкторе задать нашей таблице имя, и основа для хранения готова:
![](https://habrastorage.org/getpro/habr/upload_files/9f3/cf8/957/9f3cf8957fe343bdc9b3c087ca7af52f.jpg)
Получаем объект таблицы
Переходим к самому интересному. Писать код будем в модуле листа ConfigSheet
.
Для начала создадим необходимые гетеры:
![](https://habrastorage.org/getpro/habr/upload_files/534/6ce/b8c/5346ceb8c28fa980aa070e47e9469f6b.jpeg)
Public Property Get Table() As ListObject
' Свойство Read-Only для объекта таблицы.
Set Table = Me.ListObjects("configTable")
End Property
Public Property Get Keys() As Range
' Свойство Read-Only для столбца ключей.
Set Keys = Me.Table.ListColumns(KeyColumn).DataBodyRange
End Property
Public Property Get Values() As Range
' Свойство Read-Only для столбца значений.
Set Values = Me.Table.ListColumns(ValueColumn).DataBodyRange
End Property
В свойство Table
помещаем нашу таблицу, в Keys
– столбец ключей, в Values
– столбец значений.
Для удобства обращения к столбцам (и чтобы не хардкодить), прописываем Enum
на уровне модуля:
![](https://habrastorage.org/getpro/habr/upload_files/cd7/9e0/013/cd79e00137276ac5243e7bf435b239ff.jpeg)
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Сказал "не хардкодить" и захардкодил название таблицы ?. Исправляюсь:
![](https://habrastorage.org/getpro/habr/upload_files/aac/34c/a27/aac34ca27d3e40f58ccb8ace4b632d7d.jpeg)
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
Прописываем свойство Get Config
Осталось создать свойство Config
, через которое будем записывать, получать и обновлять значения в таблице.
Для начала прописываем получение значения по ключу:
![](https://habrastorage.org/getpro/habr/upload_files/bd6/f6c/746/bd6f6c74616477ad77ca29fee07b1128.jpeg)
Public Property Get Config(ByVal Key As Variant) As Variant
Dim i As Long
For i = 1 To Me.Keys.Rows.Count
If Key <> Me.Keys(i).Value Then GoTo NextKey
Config = Me.Values(i).Value: Exit Property
NextKey:
Next
End Property
Здесь все довольно просто – пробегаем циклом по ключам и сравниваем их с параметром Key
, передаваемым пользователем. Как только находим нужный ключ, возвращаем соответствующее значение.
Так как мы работаем напрямую с объектом Range
, это уже несколько замедляет работу макроса. Плюс ко всему, во время цикла макрос будет постоянно вызывать свойства Keys
и Values
чтобы получить их значения, что еще сильнее замедлит скорость выполнения.
Чтобы немного ускорить процесс и избежать постоянное обращение, можно создать переменные и передавать в них свойства:
![](https://habrastorage.org/getpro/habr/upload_files/2ef/185/4bb/2ef1854bba7192ec09a991027a331676.jpeg)
Public Property Get Config(ByVal Key As Variant) As Variant
' Переменные, для хранения свойств.
Dim Keys As Range: Set Keys = Me.Keys
Dim Values As Range: Set Values = Me.Values
Dim i As Long
For i = 1 To Me.Keys.Rows.Count
If Key <> Me.Keys(i).Value Then GoTo NextKey
Config = Me.Values(i).Value: Exit Property
NextKey:
Next
End Property
Но это несколько загромождает код (а ведь у нас еще будет свойство Let
), поэтому воспользуемся UDT
(user defined type) и процедурой, которая будет его инициализировать.
Создаем тип TConfig
в который помещаем все наши ранее созданные свойства (кроме, собственно, Config
), а так же создаем приватную переменную This
на уровне модуля:
![](https://habrastorage.org/getpro/habr/upload_files/748/620/169/7486201692d1f649c6f9f77998a05b92.jpeg)
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
Private Type TConfig
Table As ListObject
Keys As Range
Values As Range
End Type
Private This As TConfig
Очень важно чтобы и Type TConfig
и переменная This
были приватными, иначе на этапе компиляции возникнет ошибка.
Далее, прописываем небольшую процедуру InitThis
, для присваивания значений нашему типу:
![](https://habrastorage.org/getpro/habr/upload_files/599/151/031/59915103106f09d35e1b1faeb57663ff.jpeg)
Public Sub InitThis()
Set This.Table = Me.Table
Set This.Keys = Me.Keys
Set This.Values = Me.Values
End Sub
Теперь поправим свойство Config
:
![](https://habrastorage.org/getpro/habr/upload_files/7f1/1f7/7bb/7f11f77bb88bafa7536ec7f60f7a129a.jpeg)
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key <> This.Keys(i).Value Then GoTo NextKey
Config = This.Values(i).Value: Exit Property
NextKey:
Next
End Property
Лаконично, не так ли?
Прописываем свойство Let Config
С установлением значений чуть иначе:
![](https://habrastorage.org/getpro/habr/upload_files/e0c/c23/c55/e0cc23c55303a313b7f4aebe8dd9fa68.png)
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
End Property
В параметры принимаем Key
и RHS
(Right Hand Side – по правую руку), для того чтобы можно было прописывать такую конструкцию:
ConfigSheet.Config("Key") = "Value"
В самом начале проверяем This.Keys
на Nothing
, т.к. если в таблице еще совсем нет значений, при попытке пробежаться циклом по столбцам выскочит ошибка.
Чтобы этого избежать, после проверки добавляем в таблицу пустую строку и заново инициализировать This
. Только после этого можно будет свободно проходить по столбцам циклом.
Подобную проверку добавляем и в Get
, но вместо добавления строки просто возвращаем сообщение "Нет данных в таблице конфигурации":
![](https://habrastorage.org/getpro/habr/upload_files/8e2/8ce/9ed/8e28ce9ed054c4312ff8e812a72410b7.png)
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
If This.Keys Is Nothing Then Config = "Нет данных в таблице конфигурации": Exit Property
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key <> This.Keys(i).Value Then GoTo NextKey
Config = This.Values(i).Value: Exit Property
NextKey:
Next
End Property
Далее, так же как и в Get
части, циклом, только теперь Do Until
, пробегаем по ключам конфига. При достижении максимального индекса – добавляем в конце новую строку и выходим из цикла. В конце присваиваем ключ и значение в соответствующие ячейки.
Удаляем пустые строки
При первом обращении к таблице у нас появится пустая строка. Чтобы ее убрать (и вообще, на всякий случай, каждый раз проверять, нет ли пустых строк в таблице), пропишем небольшую процедуру:
![](https://habrastorage.org/getpro/habr/upload_files/ad7/b23/51d/ad7b2351dc7e7512485aa3cfcb41efad.jpeg)
Public Sub DeleteEmptyRows()
Me.InitThis
Dim i As Long
For i = This.Keys.Count To 1 Step -1
If (IsEmpty(This.Keys(i).Value) And IsEmpty(This.Values(i).Value)) _
Or (This.Keys(i).Value = vbNullString And This.Values(i).Value = vbNullString) Then This.Table.ListRows(i).Delete
Next
End Sub
и добавим ее в уже написанную Let
часть:
![](https://habrastorage.org/getpro/habr/upload_files/574/8e2/8b8/5748e28b86baa05c04f4827a4dd6e2b3.png)
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
Me.DeleteEmptyRows ' Проверяем на пустые строки.
End Property
Итоговый код
Исправил запись в Property Get Config
, спасибо за наводку @qyix7z.
![](https://habrastorage.org/getpro/habr/upload_files/1f1/cfc/011/1f1cfc011e8f2609682ac0e356a9c980.jpg)
Option Explicit
Private Enum EConfigColumns
KeyColumn = 1
ValueColumn
End Enum
Private Const ConfigTable As String = "configTable"
Private Type TConfig
Table As ListObject
Keys As Range
Values As Range
End Type
Private This As TConfig
Public Sub InitThis()
Set This.Table = Me.Table
Set This.Keys = Me.Keys
Set This.Values = Me.Values
End Sub
Public Property Get Table() As ListObject
' Свойство Read-Only для объекта таблицы.
Set Table = Me.ListObjects(ConfigTable)
End Property
Public Property Get Keys() As Range
' Свойство Read-Only для столбца ключей.
Set Keys = Me.Table.ListColumns(KeyColumn).DataBodyRange
End Property
Public Property Get Values() As Range
' Свойство Read-Only для столбца значений.
Set Values = Me.Table.ListColumns(ValueColumn).DataBodyRange
End Property
Public Property Get Config(ByVal Key As Variant) As Variant
Me.InitThis
If This.Keys Is Nothing Then Config = "Нет данных в таблице конфигурации": Exit Property
Dim i As Long
For i = 1 To This.Keys.Rows.Count
If Key = This.Keys(i).Value Then Config = This.Values(i).Value: Exit Property
Next
End Property
Public Property Let Config(ByVal Key As Variant, ByVal RHS As Variant)
Me.InitThis
If This.Keys Is Nothing Then This.Table.ListRows.Add: Me.InitThis
Dim i As Long
Do Until Key = This.Keys(i).Value
i = i + 1
If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do
Loop
This.Keys(i).Value = Key
This.Values(i).Value = RHS
Me.DeleteEmptyRows ' Проверяем на пустые строки.
End Property
Public Sub DeleteEmptyRows()
Me.InitThis
Dim i As Long
For i = This.Keys.Count To 1 Step -1
If (IsEmpty(This.Keys(i).Value) And IsEmpty(This.Values(i).Value)) _
Or (This.Keys(i).Value = vbNullString And This.Values(i).Value = vbNullString) Then This.Table.ListRows(i).Delete
Next
End Sub
Проверяем результат
Ну и наконец проверяем получившийся результат.
Записываем значение в конфиг:
![](https://habrastorage.org/getpro/habr/upload_files/7d5/a84/f78/7d5a84f7841a0188c33bad21983d8b85.jpeg)
Sub Test()
' Значение "Дневник VBAшника" записано в таблицу с ключом "ChanelName"
ConfigSheet.Config("ChanelName") = "Дневник VBAшника"
End Sub
Считываем значение:
![](https://habrastorage.org/getpro/habr/upload_files/881/45f/d0a/88145fd0a1dfe94042f589a40a26f2fc.jpeg)
Sub Test()
' Распечатает: "Дневник VBAшника"
Debug.Print ConfigSheet.Config("ChanelName")
End Sub
Меняем и считываем еще раз:
![](https://habrastorage.org/getpro/habr/upload_files/959/9a8/b28/9599a8b2858ed0029d83ce5494f8eeb1.jpeg)
Sub Test()
ConfigSheet.Config("ChanelName") = "https://t.me/VBAn_Diary"
' Распечатает: "https://t.me/VBAn_Diary"
Debug.Print ConfigSheet.Config("ChanelName")
End Sub
Ну а дальше этот лист можно спрятать от глаз пользователей, защитить книгу и структуру, и спокойно работать с конфигом.
Уверен, что можно изменить подход и написать код иначе. Возможно у вас есть предложения или дополнения по статье. Буду рад любому отзыву. ?