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

Сказал "не хардкодить" и захардкодил название таблицы