Как хранить Config в Excel27.09.2022 10:01

Изображение отсюда: 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

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

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

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

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

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

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

CodeName листаCodeName листа

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

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

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

Вызываем автокомплит с помощью Ctrl+SpaceВызываем автокомплит с помощью Ctrl+Space

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

Вызываем методы и свойстваВызываем методы и свойства

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

Создаем 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

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

© Habrahabr.ru