Как хранить 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

Итоговый код

Исправил запись в Property Get Config, спасибо за наводку @qyix7z.

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

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

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

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

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

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

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