Я часто пользуюсь конфигурацией при написании 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Ну а дальше этот лист можно спрятать от глаз пользователей, защитить книгу и структуру, и спокойно работать с конфигом.
Уверен, что можно изменить подход и написать код иначе. Возможно у вас есть предложения или дополнения по статье. Буду рад любому отзыву. ????
Комментарии (25)
qyix7z
27.09.2022 09:47+1Не ради холивара, но понимания для. Зачем здесь GoTo?
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
Вы рядом не пользуетесь этой конструкцией:Do Until Key = This.Keys(i).Value i = i + 1 If i > This.Keys.Rows.Count Then This.Table.ListRows.Add: Exit Do Loop
Так почему не написать так:For i = 1 To This.Keys.Rows.Count If Key = This.Keys(i).Value Then Config = This.Values(i).Value: Exit Property Next
Но может в этом есть некий смысл, которого я не вижу?
По сабжу. Я обычно использую имена (именованные диапазоны) в качестве конфигов. Но у меня количество таких данных на пальцах одной руки можно посчитать. Наверное, при большом количестве буду использовать Ваш подход. Спасибо.
ArtCapCorn Автор
27.09.2022 09:51Спасибо за отзыв!
Пихаю Goto где надо и где нет..надо по рукам себя бить начинать) Конечно, лучше так как Вы написали. Будет возможность, поправлю.
s_f1
27.09.2022 10:15+1Если это конфиг макроса, то, наверное, при запуске нужно сначала программно создавать всё то, что описано в Создаем лист ConfigSheet, при отсутствии оного в книге. Ну и сверять список ключей с текущей версией макроса, если ConfigSheet уже существует.
ArtCapCorn Автор
27.09.2022 10:25Спасибо за отзыв.
Не совсем так. Формирование этой таблицы действительно можно сделать программным. Но проверять/формировать ее при каждом запуске нет необходимости. Она изначально потребуется при разработке.
Другой вопрос, что можно автоматизировать процесс ее первого появления. И такое есть у меня, но подход костыльный и требует доработки. Как только, так сразу опишу в отдельной статье :)
sergio_deschino
27.09.2022 11:19У нас были дефолтные параметры, если не создано конфига и первый раз при запуске спрашивалось — использовать дефолтные или предлагать вводить их ручками/указать путь к конфигу.
ArtCapCorn Автор
27.09.2022 14:26+1В одном своём проекте я заложил в процедуре установление настроек по дефолту, если меняется пользователь.
То есть - открывается книга, макрос проверяет текущего пользователя, и если он не соответствует тому, который записан в том же конфиге, конфиг полностью сбрасывает в дефолт.
А уже далее пользователь под себя все настраивает.
Bagir123
29.09.2022 06:50-1Я считаю что VBA слишком примитивен для написания сложных приложений, поэтому написание там конфига - заведомо неверный подход. Когда мне надо сделать на excel что то серьезное, то использую DNA Excel через C#. А на нем создаю на листе объект, конфиг сеариализирую через json и записываю его внутри объекта.
Соответственно работа с конфигом занимает 2 строки кода.
qyix7z
29.09.2022 08:32VBA слишком примитивен для написания сложных приложений
Каким-то снобизмом повеяло, извините. А если приложение не сложное, то ему конфиг не нужен?
Можете поделиться, что за объект на листе создаете и как туда json записываете?
GreedyHamster
29.09.2022 06:55А зачем так громоздко? Да еще и циклы... (для справки: можно использовать Find) Как понял, все сводится к "прочитать/записать/добавить/удалить". Решается тремя функциями, работает на порядок быстрее.
qyix7z
29.09.2022 08:25можно использовать Find
Find подразумевает работу с объектом Range, что может быть медленнее, чем считать Range в массив и курочить его в памяти. Зависит от данных, их объема и т.п. Универсальных рецептов нет, но подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.GreedyHamster
29.09.2022 18:36подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.
А качество кода в расчет уже не берется? По сути, опубликованное выше можно охарактеризовать, как "bloatware", т.к. налицо явная избыточность. К тому же Find работает значительно быстрее цикла, на списке в 1000 строк при чтении последнего ключа разница в порядок, т.е. в 10 раз.
Ну и напоследок вишенка на торт: если в вышеприведенном творчестве задать ключ "10", например (подойдет любое число в строковом формате), то при перезаписи этого ключа он будет дописываться в конец таблицы, а прочитать его значение не получится.
Для наглядности:
ConfigSheet.Config("10") = "тест"данная конструкция при каждом вызове будет добавлять новую запись. В то же время записанное значение не читается:
aa = ConfigSheet.Config("10")переменная получает значение Empty. (причина, надеюсь, ясна)
P.S. Поскольку конфиг - это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.
qyix7z
29.09.2022 19:44А качество кода в расчет уже не берется?
Полагаю, 90% пишущих на VBA — это продвинутые пользователи, которым стало мало собственно листов и формул, и они открыли для себя «запись макроса». Кто им укажет на качество кода? Вокруг обычные пользователи, для которых =ВПР() уже из области фантастики. Сужу по себе. Набалатыкался на форуме планетаэксель, и теперь мне нет равных на работе в части экселя :)причина, надеюсь, ясна
Не, не очень. Но думаю, что при записи"10"на лист, эксель преобразует его в число. Если заранее отформатировать столбец Key как текст, то всё должно работать. Или при записи в конфиг добавить.NumberFormat = "@". Надо набить себе шишек с экселевским неявным преобразованием форматов, чтобы такое учитывать. Или пользоваться.
А еще думаю, что с ключом «1/2» будет так же весело.Поскольку конфиг — это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.
Вы правы, но тут опять возвращаемся к пользователям VBA. У программиста есть утвержденное ТЗ, а VBAшник имеет что-то примерное в голове. Поэтому ситуация «ой, мне надо еще пару строк в конфиг» — типичная.GreedyHamster
29.09.2022 21:47По сути верно, происходит конфликт форматов, что и нужно учитывать при использовании Value. Или использовать другой метод.
Поэтому ситуация «ой, мне надо еще пару строк в конфиг» — типичная.
Пару строк можно добавить и вручную, это прерогатива программиста. Пользователь же работает именно с фиксированным списком, давать ему возможность добавлять/удалять строки - неразумно.
Ну и, чтобы не быть голословным, вот пример функции чтения/записи по заданному ключу:
Option Explicit Private Function fnRC2A1B2(vRow1 As Long, vCol1 As Integer, vRow2 As Long, vCol2 As Integer) As String fnRC2A1B2 = Split(Cells(1, vCol1).Address(True, False, xlA1), "$")(0) + CStr(vRow1) + ":" + _ Split(Cells(1, vCol2).Address(True, False, xlA1), "$")(0) + CStr(vRow2) End Function Function fnDataConfig(aKey As Variant, Optional aItem As Variant = Empty) As Variant Dim c As Object, nn As Long nn = CFG.Cells(Rows.Count, 1).End(xlUp).Row With CFG.Range(fnRC2A1B2(1, 1, nn, 1)) Set c = .Find(What:=aKey, After:=CFG.Cells(nn, 1), LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then If aItem = Empty Then fnDataConfig = c.Offset(0, 1).Value Else c.Offset(0, 1).Value = aItem fnDataConfig = True End If Else If aItem = Empty Then fnDataConfig = "Not Found" Else ' ===== необходимость добавления весьма сомнительна ' CFG.Cells(nn + 1, 1).Value = aKey ' CFG.Cells(nn + 1, 2).Value = aItem ' fnDataConfig = "Not Exists, Added" ' ===== fnDataConfig = "Not Exists" End If End If End With Set c = Nothing End Function
baldr
В целом, я бы одобрил подход в целом. Держите плюс к статье.
В Excel довольно просто написать ужасный код, где макросы перемежаются с трехэтажными формулами.. Так что любую попытку структурировать код нужно поощрять.
Однако, я сразу вижу минусы. Если вы создаете новый документ - вы заново через копипаст переносите все? А потом еще раз в новый?
А если вы решаете в свой конфиг добавить новую фичу-функцию - как вы это добавите во все документы сразу? Скорее всего у вас будут старые документы, где еще нет этой функции и новые. А потом еще одну добавите или баг почините... А потом открываете старый документ - а там нет привычной функции..
Также не получится версионировать этот код. Например, выложить на github и получать пулл-реквесты.
В VBA, кажется, была возможность вызывать код из внешнего .vba файла - может быть оформить его в файл в какой-то общей папке и всем вызывать его? И для нового документа не накликивать каждый раз изменения, а создать общий макрос или шаблон?
ArtCapCorn Автор
Спасибо за отзыв и за плюс)
На самом деле я пытаюсь придумать этот «велосипед» с возможностью контроля версий. Даже что-то очень отдаленное удалось сделать (в виде надстройки). Но довольно костыльно. Увы, пока прям точных рецептов нет (в интернете особо ничего не удалось найти, не удивлюсь если плохо ищу).
Что касаемо GitHub’а, конкретно у меня проблема в том, что работодатель блокирует для сотрудников)) ну вот так, не суть важно. (Может стоит другие варианты контроля версий рассмотреть..) Короче говоря, возможно, этот вариант можно раскушать, но так как лично мне он не подходит, я за него пока не брался.
На счёт вызова из .vba не слышал ни разу. Можно подробнее? Освечу для себя этот момент.
В остальном, конечно, мой подход далеко не идеален и требует доработки. Но меня на данном этапе пока, в общем и целом, устраивает)
Ещё раз спасибо)
baldr
Я сам не очень пользовался, и давно это было, пришлось гуглить. Ищите по слову "XLSTART":
ArtCapCorn Автор
Благодарю.
Похоже, что это personal book. Да и надстройки, если мне память не изменяет, тоже запускаются из этой папки. Примерно таким образом у меня сейчас работает формирование листа и таблицы конфига в новых проектах. Ну и если код вынести в отдельный модуль, можно и его подключать так же.
Правда Вы меня натолкнули на другую идею. Пойду думать)
qyix7z
А XLSTART — это уже автозапуск.
ArietZ
По версионированию программ VBA есть небольшой опыт, могу поделиться парочкой подходов - может наведет на новыи идеи для Вашей задачи.
Для версионирования отдельных файлов можно "хардкодить" ваш конфиг в VBA модуль и автоматически экспортировать в отдельный файл. После чего файл размещать во внутреннем git репозитории (также можно сделать автоматически с помощью скриптов и запуска из VBA через Shell).
Версионирование общего конфига для нескольких продуктов / пользовательских файлов можно делать в рамках отдельного config-файла. Я использую ini/JSON файлы с известным расположением. Далее конфигурационные файлы используем в обычной системе контроля версий
Для версионирования сложных систем продуктов использую самописную систему учета установленных версий (package manager) через надстройки VBA в XLSTART и Word/STARTUP соответственно. Функционал другой по сравнению с системой контроля версий. Основная задача - обновлять отдельные компоненты системы и вести учет текущих версий, diff не поддерживается.
Велосипеды на чистом VBA достаточно муторно писать если они выходят за границы объектной модели офиса. Для моих задач оказалось продуктивно добавлять скриптовый язык (PowerShell) для конфигурирования системы у пользователя и делать небольшую обвязку внутри VBA для передачи параметров.
ArtCapCorn Автор
Спасибо за отзыв.
Интересный подход, надо будет поискать информацию на эту тему, благодарю :)
Robastik
Тогда уж предъявляйте и невозможность обновления кода/конфига у всех пользователей и отсутствие хоть какой-нибудь защиты кода.
Какие плюсы вы нашли?
baldr
Общий тренд в использовании Excel-макросов - это просто куча формул и простыня макросов в разных частях документа. Здесь я вижу что человек пытается начать структурировать свою работу. Какой-то совсем общий подход к организации кода и документов рекомендовать сложно, не зная специфики.
Но сам факт попыток организации кода - большой плюс и это стоит поощрять.
IvanSTV
минус в том, что файл с конфигом за собой придется таскать и путь ему отдельно прописывать, и неясно что делать, если общий путь отрубается по тем или иным причинам. Например, если человек офлайн - с удаленкой вопрос встал ребром, и я все ссылки на общие папки поудалял и перенес справочники\настройки в файл основного макроса.
baldr
Альтернатива - иметь 20 файлов и в каждом из них одни и те же параметры? А если меняется, скажем, пароль к базе или какой-то другой параметр - то ходим и руками везде правим?
ArtCapCorn Автор
Спасибо за отзыв.
Возможно я не так понял, прошу поправить.
Я данный подход использую в книге с конкретным макросом/макросами. Когда «клиент» пользуется макросом, ему в любом случае нужна будет эта книга, а в ней уже заложена конфигурация, которую можно обновлять с учетом предпочтений конкретного пользователя.
На большую аудиторию, которая одновременно пользуется этим макросом, конечно, это не рассчитано.
Тут, скорее, частная история. Но удобство в том, что передавая эту частную историю другому пользователю, он сможет ее подстроить под себя.