Комментарии 30
В целом, я бы одобрил подход в целом. Держите плюс к статье.
В Excel довольно просто написать ужасный код, где макросы перемежаются с трехэтажными формулами.. Так что любую попытку структурировать код нужно поощрять.
Однако, я сразу вижу минусы. Если вы создаете новый документ - вы заново через копипаст переносите все? А потом еще раз в новый?
А если вы решаете в свой конфиг добавить новую фичу-функцию - как вы это добавите во все документы сразу? Скорее всего у вас будут старые документы, где еще нет этой функции и новые. А потом еще одну добавите или баг почините... А потом открываете старый документ - а там нет привычной функции..
Также не получится версионировать этот код. Например, выложить на github и получать пулл-реквесты.
В VBA, кажется, была возможность вызывать код из внешнего .vba файла - может быть оформить его в файл в какой-то общей папке и всем вызывать его? И для нового документа не накликивать каждый раз изменения, а создать общий макрос или шаблон?
Спасибо за отзыв и за плюс)
На самом деле я пытаюсь придумать этот «велосипед» с возможностью контроля версий. Даже что-то очень отдаленное удалось сделать (в виде надстройки). Но довольно костыльно. Увы, пока прям точных рецептов нет (в интернете особо ничего не удалось найти, не удивлюсь если плохо ищу).
Что касаемо GitHub’а, конкретно у меня проблема в том, что работодатель блокирует для сотрудников)) ну вот так, не суть важно. (Может стоит другие варианты контроля версий рассмотреть..) Короче говоря, возможно, этот вариант можно раскушать, но так как лично мне он не подходит, я за него пока не брался.
На счёт вызова из .vba не слышал ни разу. Можно подробнее? Освечу для себя этот момент.
В остальном, конечно, мой подход далеко не идеален и требует доработки. Но меня на данном этапе пока, в общем и целом, устраивает)
Ещё раз спасибо)
Я сам не очень пользовался, и давно это было, пришлось гуглить. Ищите по слову "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.
Благодарю.
Похоже, что это personal book. Да и надстройки, если мне память не изменяет, тоже запускаются из этой папки. Примерно таким образом у меня сейчас работает формирование листа и таблицы конфига в новых проектах. Ну и если код вынести в отдельный модуль, можно и его подключать так же.
Правда Вы меня натолкнули на другую идею. Пойду думать)
На счёт вызова из .vba не слышал ни разу. Можно подробнее?Если я правильно понял вопрос:
Workbooks.Open Filename:="C:\Users\Книга1.xlsb"
Application.Run"'Книга1.xlsb'!macros2"А XLSTART — это уже автозапуск.
По версионированию программ VBA есть небольшой опыт, могу поделиться парочкой подходов - может наведет на новыи идеи для Вашей задачи.
Для версионирования отдельных файлов можно "хардкодить" ваш конфиг в VBA модуль и автоматически экспортировать в отдельный файл. После чего файл размещать во внутреннем git репозитории (также можно сделать автоматически с помощью скриптов и запуска из VBA через Shell).
Версионирование общего конфига для нескольких продуктов / пользовательских файлов можно делать в рамках отдельного config-файла. Я использую ini/JSON файлы с известным расположением. Далее конфигурационные файлы используем в обычной системе контроля версий
Для версионирования сложных систем продуктов использую самописную систему учета установленных версий (package manager) через надстройки VBA в XLSTART и Word/STARTUP соответственно. Функционал другой по сравнению с системой контроля версий. Основная задача - обновлять отдельные компоненты системы и вести учет текущих версий, diff не поддерживается.
Велосипеды на чистом VBA достаточно муторно писать если они выходят за границы объектной модели офиса. Для моих задач оказалось продуктивно добавлять скриптовый язык (PowerShell) для конфигурирования системы у пользователя и делать небольшую обвязку внутри VBA для передачи параметров.
минусы
Тогда уж предъявляйте и невозможность обновления кода/конфига у всех пользователей и отсутствие хоть какой-нибудь защиты кода.
одобрил подход в целом
Какие плюсы вы нашли?
Какие плюсы вы нашли?
Общий тренд в использовании Excel-макросов - это просто куча формул и простыня макросов в разных частях документа. Здесь я вижу что человек пытается начать структурировать свою работу. Какой-то совсем общий подход к организации кода и документов рекомендовать сложно, не зная специфики.
Но сам факт попыток организации кода - большой плюс и это стоит поощрять.
минус в том, что файл с конфигом за собой придется таскать и путь ему отдельно прописывать, и неясно что делать, если общий путь отрубается по тем или иным причинам. Например, если человек офлайн - с удаленкой вопрос встал ребром, и я все ссылки на общие папки поудалял и перенес справочники\настройки в файл основного макроса.
Альтернатива - иметь 20 файлов и в каждом из них одни и те же параметры? А если меняется, скажем, пароль к базе или какой-то другой параметр - то ходим и руками везде правим?
Спасибо за отзыв.
Возможно я не так понял, прошу поправить.
Я данный подход использую в книге с конкретным макросом/макросами. Когда «клиент» пользуется макросом, ему в любом случае нужна будет эта книга, а в ней уже заложена конфигурация, которую можно обновлять с учетом предпочтений конкретного пользователя.
На большую аудиторию, которая одновременно пользуется этим макросом, конечно, это не рассчитано.
Тут, скорее, частная история. Но удобство в том, что передавая эту частную историю другому пользователю, он сможет ее подстроить под себя.
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Но может в этом есть некий смысл, которого я не вижу?
По сабжу. Я обычно использую имена (именованные диапазоны) в качестве конфигов. Но у меня количество таких данных на пальцах одной руки можно посчитать. Наверное, при большом количестве буду использовать Ваш подход. Спасибо.
Спасибо за отзыв.
Не совсем так. Формирование этой таблицы действительно можно сделать программным. Но проверять/формировать ее при каждом запуске нет необходимости. Она изначально потребуется при разработке.
Другой вопрос, что можно автоматизировать процесс ее первого появления. И такое есть у меня, но подход костыльный и требует доработки. Как только, так сразу опишу в отдельной статье :)
У нас были дефолтные параметры, если не создано конфига и первый раз при запуске спрашивалось — использовать дефолтные или предлагать вводить их ручками/указать путь к конфигу.
В одном своём проекте я заложил в процедуре установление настроек по дефолту, если меняется пользователь.
То есть - открывается книга, макрос проверяет текущего пользователя, и если он не соответствует тому, который записан в том же конфиге, конфиг полностью сбрасывает в дефолт.
А уже далее пользователь под себя все настраивает.
Я считаю что VBA слишком примитивен для написания сложных приложений, поэтому написание там конфига - заведомо неверный подход. Когда мне надо сделать на excel что то серьезное, то использую DNA Excel через C#. А на нем создаю на листе объект, конфиг сеариализирую через json и записываю его внутри объекта.
Соответственно работа с конфигом занимает 2 строки кода.
VBA слишком примитивен для написания сложных приложенийКаким-то снобизмом повеяло, извините. А если приложение не сложное, то ему конфиг не нужен?
Можете поделиться, что за объект на листе создаете и как туда json записываете?
Это правда. У каждого языка своя область применения.
Работа с excel через Interop:
using Excel = Microsoft.Office.Interop.Excel;
using Newtonsoft.Json;
Структура конфига:
struct conf
{
public long open_date_utc;
public double open;
public double high;
public double low;
}
Инициализация:
var xlApp = (Excel.Application)ExcelDnaUtil.Application;
var xlWb = xlApp.ActiveWorkbook;
var data = new conf { open_date_utc = DateTime.Now.Ticks, high = 2, low = 1 };
Внесение данных из переменной data любой структуры на первый объект типа надпись на листе (у него могут быть нулевые размеры и размещен в любом месте (т.е. невидим):
var json = JsonConvert.SerializeObject(data);
xlWb.ActiveSheet.Shapes[1].TextFrame2.TextRange.Characters.Text = json;
Получение данных обратно:
json = xlWb.ActiveSheet.Shapes[1].TextFrame2.TextRange.Characters.Text;
data = JsonConvert.DeserializeObject<conf>(json);
тоже самое можно кстати и на VBA сделать, но на нем неудобно с json работать.
А зачем так громоздко? Да еще и циклы... (для справки: можно использовать Find) Как понял, все сводится к "прочитать/записать/добавить/удалить". Решается тремя функциями, работает на порядок быстрее.
можно использовать FindFind подразумевает работу с объектом Range, что может быть медленнее, чем считать Range в массив и курочить его в памяти. Зависит от данных, их объема и т.п. Универсальных рецептов нет, но подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.
подозреваю, что конфиг автора не очень большой, так что разница будет неощутимой.
А качество кода в расчет уже не берется? По сути, опубликованное выше можно охарактеризовать, как "bloatware", т.к. налицо явная избыточность. К тому же Find работает значительно быстрее цикла, на списке в 1000 строк при чтении последнего ключа разница в порядок, т.е. в 10 раз.
Ну и напоследок вишенка на торт: если в вышеприведенном творчестве задать ключ "10", например (подойдет любое число в строковом формате), то при перезаписи этого ключа он будет дописываться в конец таблицы, а прочитать его значение не получится.
Для наглядности:
ConfigSheet.Config("10") = "тест"
данная конструкция при каждом вызове будет добавлять новую запись. В то же время записанное значение не читается:
aa = ConfigSheet.Config("10")
переменная получает значение Empty. (причина, надеюсь, ясна)
P.S. Поскольку конфиг - это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.
А качество кода в расчет уже не берется?Полагаю, 90% пишущих на VBA — это продвинутые пользователи, которым стало мало собственно листов и формул, и они открыли для себя «запись макроса». Кто им укажет на качество кода? Вокруг обычные пользователи, для которых =ВПР() уже из области фантастики. Сужу по себе. Набалатыкался на форуме планетаэксель, и теперь мне нет равных на работе в части экселя :)
причина, надеюсь, яснаНе, не очень. Но думаю, что при записи
"10" на лист, эксель преобразует его в число. Если заранее отформатировать столбец Key как текст, то всё должно работать. Или при записи в конфиг добавить .NumberFormat = "@". Надо набить себе шишек с экселевским неявным преобразованием форматов, чтобы такое учитывать. Или пользоваться.А еще думаю, что с ключом «1/2» будет так же весело.
Поскольку конфиг — это фиксированный список с изменяемыми значениями, также вызывает сомнение необходимость добавления/удаления строк.Вы правы, но тут опять возвращаемся к пользователям VBA. У программиста есть утвержденное ТЗ, а VBAшник имеет что-то примерное в голове. Поэтому ситуация «ой, мне надо еще пару строк в конфиг» — типичная.
По сути верно, происходит конфликт форматов, что и нужно учитывать при использовании 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Пару строк можно добавить и вручную, это прерогатива программиста. Пользователь же работает именно с фиксированным списком, давать ему возможность добавлять/удалять строки — неразумно.1. Программист и пользователь очень часто одно лицо (я всё еще про VBA)
2. Программист ленив.
3. :)
4. PROFIT
вот пример функцииВот сейчас ТСу обидно было наверное. А если серьезно, то вполне может быть, что ТС хотел попрактиковаться в Private Type и в Public Property. Я тоже смотрел на эти конструкции, но не придумал, как у себя применять.
Я однажды убил кучу времени, чтобы программно рисовать пользователям (мне и сотруднику за соседним столом) форму. Мне хотелось попрактиковаться в модулях класса и соответствующих ивентах. Однозначно задача решалась куда проще, и любой, кто глянет этот код, скажет — bloatware.
У меня вопрос по
fnRC2A1B2(1, 1, nn, 1) — нафига?Почему не:
With CFG.Cells(1, 1).Resize(nn, 1)?Я наверное могу еще пару вариантов получения Range накидать, если известны vRow1, vCol1, vRow2, vCol2. Функция выглядит избыточной.
Спасибо за отзывы!
Абсолютно никакой обиды нет, я в конце даже и написал, что уверен в возможности других решений, это нормально. Этим и круто программирование, что одну и ту же задачу можно решить кучей способов и все они будут правильными.
Про функцию Find как-то забыл, очень редко ее использую. Да и с Range в подобном формате почти не взаимодействую, гораздо чаще передаю в массив и уже с ним работаю. Конкретно тут почему так решил построить? Хм.. если честно, наверное, даже сам не смогу ответить :D
Касаемо вопросов о качестве кода - я все ещё учусь) Написанием макросов (да и в принципе программированием) занимаюсь примерно год с небольшим.
Статью же решил написать по нескольким причинам:
Показалось, что подобные решения смогут подтолкнуть кого-то (возможно таких же как и я новичков) на более глубокое изучение работы с макросами (все таки VBA, увы, не столь популярен в наше время).
Дать небольшую рекламу своему каналу в ТГ :)
Ещё раз, спасибо за отзывы :)
У меня вопрос по
fnRC2A1B2(1, 1, nn, 1)— нафига? Почему не:With CFG.Cells(1, 1).Resize(nn, 1)?
Это старая история... Как-то столкнулся с тем, что конструкция вида Sheet.Range(Cells(1, 1), Cells(2,2)) не работает на неактивном листе. В то же время Sheet.Range("A1:B2") отрабатывает без каких либо проблем. Недолго думая набросал функцию перевода координат из формата R1C1 в формат A1 и на этом успокоился. (программист ленив... :D ) Про Resize мне в то время не попалось, да и функция оказалась удобной. Висит у меня в надстройке, если надо быстро узнать диапазон в формате A1, то формула работает прямо на листе. А так да, если рассматривать с точки зрения избыточности, то без нее можно обойтись. ( и да, я в курсе, что можно записать и так: Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(2,2)) и оно будет работать, но мне такая запись не понравилась. :-) )
Как хранить Config в Excel