Как хранить Config в Excel

Я часто пользуюсь конфигурацией при написании VBA макросов в Excel. Иногда она нужна в формах для сохранения настроек, иногда для сохранения каких-то получаемых в процессе выполнения макроса данных.

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

Сегодня я хотел бы пошагово рассказать о том как я храню и обрабатываю конфигурацию макроса в книге Excel.

Создаем лист ConfigSheet

У меня под рукой был Excel 2010, но в данном случае версия не имеет значения.

Для начала создаем отдельный лист. Я назвал его config, но это не принципиально. Что же действительно принципиально, так это CodeName листа:

Если вы вдруг не знали, листы документа Excel в VBA – это, ни что иное, как объект класса Worksheet. Обращаемся к справке и видим у объекта Worksheet необходимое свойство (перезаписать его программно, несмотря на Read-only, можно, но об этом в другой раз):

Чтобы было проще обращаться к нашему Config листу, меняем ему значение поля (Name) в свойствах (если у вас их нет, нажмите F4 или View -> Properties Window, а если у вас нет структуры с проектом, нажмите Ctrl+R или View -> Project Explorer).

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

Кстати, так как лист – это объект, мы можем обращаться так же к его методам, полям и свойствам через точку, как обычно:

Этим мы и воспользуемся, но об этом чуть позже.

Создаем ListObject

Итак, как вы уже догадались, всю информацию мы будем сохранять в таблицу, а именно в объект ListObject.
Для этого на нашем листе создаем пустую таблицу с двумя столбцами Key и Value:

Теперь осталось в Конструкторе задать нашей таблице имя, и основа для хранения готова:

Получаем объект таблицы

Переходим к самому интересному. Писать код будем в модуле листа ConfigSheet.
Для начала создадим необходимые гетеры:

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 на уровне модуля:

Private Enum EConfigColumns     KeyColumn = 1     ValueColumn End Enum

Сказал «не хардкодить» и захардкодил название таблицы ?. Исправляюсь:

Option Explicit  Private Enum EConfigColumns     KeyColumn = 1     ValueColumn End Enum  Private Const ConfigTable As String = "configTable"

Прописываем свойство Get Config

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

Для начала прописываем получение значения по ключу:

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

Чтобы немного ускорить процесс и избежать постоянное обращение, можно создать переменные и передавать в них свойства:

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 на уровне модуля:

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, для присваивания значений нашему типу:

Public Sub InitThis()     Set This.Table = Me.Table     Set This.Keys = Me.Keys     Set This.Values = Me.Values End Sub

Теперь поправим свойство Config:

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

С установлением значений чуть иначе:

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, но вместо добавления строки просто возвращаем сообщение «Нет данных в таблице конфигурации»:

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

Удаляем пустые строки

При первом обращении к таблице у нас появится пустая строка. Чтобы ее убрать (и вообще, на всякий случай, каждый раз проверять, нет ли пустых строк в таблице), пропишем небольшую процедуру:

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 часть:

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

Итоговый код

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 GoTo NextKey         Config = This.Values(i).Value: Exit Property NextKey:     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 

Проверяем результат

Ну и наконец проверяем получившийся результат.

Записываем значение в конфиг:

Sub Test()     ' Значение "Дневник VBAшника" записано в таблицу с ключом "ChanelName"     ConfigSheet.Config("ChanelName") = "Дневник VBAшника" End Sub

Считываем значение:

Sub Test()     ' Распечатает: "Дневник VBAшника"     Debug.Print ConfigSheet.Config("ChanelName") End Sub

Меняем и считываем еще раз:

Sub Test()     ConfigSheet.Config("ChanelName") = "https://t.me/VBAn_Diary"     ' Распечатает: "https://t.me/VBAn_Diary"     Debug.Print ConfigSheet.Config("ChanelName") End Sub

Ну а дальше этот лист можно спрятать от глаз пользователей, защитить книгу и структуру, и спокойно работать с конфигом.

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