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

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

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)


  1. baldr
    27.09.2022 08:58
    +2

    В целом, я бы одобрил подход в целом. Держите плюс к статье.

    В Excel довольно просто написать ужасный код, где макросы перемежаются с трехэтажными формулами.. Так что любую попытку структурировать код нужно поощрять.

    Однако, я сразу вижу минусы. Если вы создаете новый документ - вы заново через копипаст переносите все? А потом еще раз в новый?

    А если вы решаете в свой конфиг добавить новую фичу-функцию - как вы это добавите во все документы сразу? Скорее всего у вас будут старые документы, где еще нет этой функции и новые. А потом еще одну добавите или баг почините... А потом открываете старый документ - а там нет привычной функции..

    Также не получится версионировать этот код. Например, выложить на github и получать пулл-реквесты.

    В VBA, кажется, была возможность вызывать код из внешнего .vba файла - может быть оформить его в файл в какой-то общей папке и всем вызывать его? И для нового документа не накликивать каждый раз изменения, а создать общий макрос или шаблон?


    1. ArtCapCorn Автор
      27.09.2022 09:26
      +1

      Спасибо за отзыв и за плюс)

      На самом деле я пытаюсь придумать этот «велосипед» с возможностью контроля версий. Даже что-то очень отдаленное удалось сделать (в виде надстройки). Но довольно костыльно. Увы, пока прям точных рецептов нет (в интернете особо ничего не удалось найти, не удивлюсь если плохо ищу).

      Что касаемо GitHub’а, конкретно у меня проблема в том, что работодатель блокирует для сотрудников)) ну вот так, не суть важно. (Может стоит другие варианты контроля версий рассмотреть..) Короче говоря, возможно, этот вариант можно раскушать, но так как лично мне он не подходит, я за него пока не брался.

      На счёт вызова из .vba не слышал ни разу. Можно подробнее? Освечу для себя этот момент.

      В остальном, конечно, мой подход далеко не идеален и требует доработки. Но меня на данном этапе пока, в общем и целом, устраивает)

      Ещё раз спасибо)


      1. baldr
        27.09.2022 09:32
        +1

        Я сам не очень пользовался, и давно это было, пришлось гуглить. Ищите по слову "XLSTART":

        What is xlstart?

        The XLStart folder is a special folder created when you install Excel. That's where Excel stores the workbook template, Book. xltx. In addition, if you store a workbook in this folder, Excel will open it automatically every time you launch Excel.


        1. ArtCapCorn Автор
          27.09.2022 09:42
          +2

          Благодарю.

          Похоже, что это personal book. Да и надстройки, если мне память не изменяет, тоже запускаются из этой папки. Примерно таким образом у меня сейчас работает формирование листа и таблицы конфига в новых проектах. Ну и если код вынести в отдельный модуль, можно и его подключать так же.

          Правда Вы меня натолкнули на другую идею. Пойду думать)


      1. qyix7z
        27.09.2022 10:31
        +3

        На счёт вызова из .vba не слышал ни разу. Можно подробнее?
        Если я правильно понял вопрос:
        Workbooks.Open Filename:="C:\Users\Книга1.xlsb"
        Application.Run"'Книга1.xlsb'!macros2"

        А XLSTART — это уже автозапуск.


      1. ArietZ
        27.09.2022 14:16
        +2

        По версионированию программ VBA есть небольшой опыт, могу поделиться парочкой подходов - может наведет на новыи идеи для Вашей задачи.

        1. Для версионирования отдельных файлов можно "хардкодить" ваш конфиг в VBA модуль и автоматически экспортировать в отдельный файл. После чего файл размещать во внутреннем git репозитории (также можно сделать автоматически с помощью скриптов и запуска из VBA через Shell).

        2. Версионирование общего конфига для нескольких продуктов / пользовательских файлов можно делать в рамках отдельного config-файла. Я использую ini/JSON файлы с известным расположением. Далее конфигурационные файлы используем в обычной системе контроля версий

        3. Для версионирования сложных систем продуктов использую самописную систему учета установленных версий (package manager) через надстройки VBA в XLSTART и Word/STARTUP соответственно. Функционал другой по сравнению с системой контроля версий. Основная задача - обновлять отдельные компоненты системы и вести учет текущих версий, diff не поддерживается.

        Велосипеды на чистом VBA достаточно муторно писать если они выходят за границы объектной модели офиса. Для моих задач оказалось продуктивно добавлять скриптовый язык (PowerShell) для конфигурирования системы у пользователя и делать небольшую обвязку внутри VBA для передачи параметров.


        1. ArtCapCorn Автор
          27.09.2022 14:17
          +1

          Спасибо за отзыв.

          Интересный подход, надо будет поискать информацию на эту тему, благодарю :)


    1. Robastik
      27.09.2022 10:54

      минусы

      Тогда уж предъявляйте и невозможность обновления кода/конфига у всех пользователей и отсутствие хоть какой-нибудь защиты кода.

      одобрил подход в целом

      Какие плюсы вы нашли?


      1. baldr
        27.09.2022 11:05
        +2

        Какие плюсы вы нашли?

        Общий тренд в использовании Excel-макросов - это просто куча формул и простыня макросов в разных частях документа. Здесь я вижу что человек пытается начать структурировать свою работу. Какой-то совсем общий подход к организации кода и документов рекомендовать сложно, не зная специфики.

        Но сам факт попыток организации кода - большой плюс и это стоит поощрять.


    1. IvanSTV
      27.09.2022 11:04

      минус в том, что файл с конфигом за собой придется таскать и путь ему отдельно прописывать, и неясно что делать, если общий путь отрубается по тем или иным причинам. Например, если человек офлайн - с удаленкой вопрос встал ребром, и я все ссылки на общие папки поудалял и перенес справочники\настройки в файл основного макроса.


      1. baldr
        27.09.2022 11:10

        Альтернатива - иметь 20 файлов и в каждом из них одни и те же параметры? А если меняется, скажем, пароль к базе или какой-то другой параметр - то ходим и руками везде правим?


      1. ArtCapCorn Автор
        27.09.2022 14:22
        +1

        Спасибо за отзыв.

        Возможно я не так понял, прошу поправить.

        Я данный подход использую в книге с конкретным макросом/макросами. Когда «клиент» пользуется макросом, ему в любом случае нужна будет эта книга, а в ней уже заложена конфигурация, которую можно обновлять с учетом предпочтений конкретного пользователя.

        На большую аудиторию, которая одновременно пользуется этим макросом, конечно, это не рассчитано.

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


  1. 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

    Но может в этом есть некий смысл, которого я не вижу?

    По сабжу. Я обычно использую имена (именованные диапазоны) в качестве конфигов. Но у меня количество таких данных на пальцах одной руки можно посчитать. Наверное, при большом количестве буду использовать Ваш подход. Спасибо.


    1. ArtCapCorn Автор
      27.09.2022 09:51

      Спасибо за отзыв!

      Пихаю Goto где надо и где нет..надо по рукам себя бить начинать) Конечно, лучше так как Вы написали. Будет возможность, поправлю.


  1. s_f1
    27.09.2022 10:15
    +1

    Если это конфиг макроса, то, наверное, при запуске нужно сначала программно создавать всё то, что описано в Создаем лист ConfigSheet, при отсутствии оного в книге. Ну и сверять список ключей с текущей версией макроса, если ConfigSheet уже существует.


    1. ArtCapCorn Автор
      27.09.2022 10:25

      Спасибо за отзыв.

      Не совсем так. Формирование этой таблицы действительно можно сделать программным. Но проверять/формировать ее при каждом запуске нет необходимости. Она изначально потребуется при разработке.

      Другой вопрос, что можно автоматизировать процесс ее первого появления. И такое есть у меня, но подход костыльный и требует доработки. Как только, так сразу опишу в отдельной статье :)


      1. sergio_deschino
        27.09.2022 11:19

        У нас были дефолтные параметры, если не создано конфига и первый раз при запуске спрашивалось — использовать дефолтные или предлагать вводить их ручками/указать путь к конфигу.


        1. ArtCapCorn Автор
          27.09.2022 14:26
          +1

          В одном своём проекте я заложил в процедуре установление настроек по дефолту, если меняется пользователь.

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

          А уже далее пользователь под себя все настраивает.


  1. Bagir123
    29.09.2022 06:50
    -1

    Я считаю что VBA слишком примитивен для написания сложных приложений, поэтому написание там конфига - заведомо неверный подход. Когда мне надо сделать на excel что то серьезное, то использую DNA Excel через C#. А на нем создаю на листе объект, конфиг сеариализирую через json и записываю его внутри объекта.

    Соответственно работа с конфигом занимает 2 строки кода.


    1. qyix7z
      29.09.2022 08:32

      VBA слишком примитивен для написания сложных приложений
      Каким-то снобизмом повеяло, извините. А если приложение не сложное, то ему конфиг не нужен?

      Можете поделиться, что за объект на листе создаете и как туда json записываете?


  1. GreedyHamster
    29.09.2022 06:55

    А зачем так громоздко? Да еще и циклы... (для справки: можно использовать Find) Как понял, все сводится к "прочитать/записать/добавить/удалить". Решается тремя функциями, работает на порядок быстрее.


    1. qyix7z
      29.09.2022 08:25

      можно использовать Find
      Find подразумевает работу с объектом Range, что может быть медленнее, чем считать Range в массив и курочить его в памяти. Зависит от данных, их объема и т.п. Универсальных рецептов нет, но подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.


      1. GreedyHamster
        29.09.2022 18:36

        подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.

        А качество кода в расчет уже не берется? По сути, опубликованное выше можно охарактеризовать, как "bloatware", т.к. налицо явная избыточность. К тому же Find работает значительно быстрее цикла, на списке в 1000 строк при чтении последнего ключа разница в порядок, т.е. в 10 раз.

        Ну и напоследок вишенка на торт: если в вышеприведенном творчестве задать ключ "10", например (подойдет любое число в строковом формате), то при перезаписи этого ключа он будет дописываться в конец таблицы, а прочитать его значение не получится.

        Для наглядности:

        ConfigSheet.Config("10") = "тест"

        данная конструкция при каждом вызове будет добавлять новую запись. В то же время записанное значение не читается:

        aa = ConfigSheet.Config("10")

        переменная получает значение Empty. (причина, надеюсь, ясна)

        P.S. Поскольку конфиг - это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.


        1. qyix7z
          29.09.2022 19:44

          А качество кода в расчет уже не берется?
          Полагаю, 90% пишущих на VBA — это продвинутые пользователи, которым стало мало собственно листов и формул, и они открыли для себя «запись макроса». Кто им укажет на качество кода? Вокруг обычные пользователи, для которых =ВПР() уже из области фантастики. Сужу по себе. Набалатыкался на форуме планетаэксель, и теперь мне нет равных на работе в части экселя :)
          причина, надеюсь, ясна
          Не, не очень. Но думаю, что при записи "10" на лист, эксель преобразует его в число. Если заранее отформатировать столбец Key как текст, то всё должно работать. Или при записи в конфиг добавить .NumberFormat = "@". Надо набить себе шишек с экселевским неявным преобразованием форматов, чтобы такое учитывать. Или пользоваться.
          А еще думаю, что с ключом «1/2» будет так же весело.
          Поскольку конфиг — это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.
          Вы правы, но тут опять возвращаемся к пользователям VBA. У программиста есть утвержденное ТЗ, а VBAшник имеет что-то примерное в голове. Поэтому ситуация «ой, мне надо еще пару строк в конфиг» — типичная.


          1. 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