В интернете есть хорошие примеры даже не макроса, а целого комплекса, встраиваемого в Excel в качестве меню в ленте. Это ПО является платным. Я просмотрел одну из предлагаемых в ПО возможностей по сбору данных из ряда листов по диапазону.
Обычная методика создания кода предполагает его создание в цикле, разбитом на несколько этапов.
Если пытаться делать аналогичное более примитивное ПО по сбору данных некоторого диапазона на один лист, то на это может уйти несколько часов. Если данное ПО надо передавать кому-то, то надо затратить несколько дней. Если же пускать его в продажу, то надо весь код вылизать и перепроверить неоднократно на различных ситуациях. На это может уйти несколько месяцев. Плюс необходима постоянная поддержка. Наша молодежь отличается часто тем, что завышает свои возможности и занижает трудности. С одной стороны это и хорошо. Я за несколько дней создал пока лишь 8 вариант своего ПО. В окончательном виде происходит и консолидация данных. При консолидации надо оелючить выборку до конца файла и наоборот, консолидация невозможна, если обработка ведётся до конца файла. Это логично.
Главное в написании первичного кода программы состоит в том, чтобы добиться создания пусть отрабатывающего не все ситуации кода, но работающего. До сих пор для меня существуют некоторые неясности и недопонимание отдельных нюансов и ситуаций. Если Вы писали какой-то код ранее, то просто потом используете накопившиеся наработки. Беда состоит лишь в том, что любой код через некоторое время забывается, поэтому изначально надо вставлять в программу хорошие комментарии и отладочные операторы.
Возникает ряд простых вопросов
- Как задается диапазон? А если в нем всего одна ячейка?
- Как задается диапазон в случае представления R1C1 и A1? Можно ли две ситуации привести к одному случаю? (Можно)
inputFormula = a
a = Application.ConvertFormula( _
Formula:=inputFormula, _
fromReferenceStyle:=xlR1C1, _
toReferenceStyle:=xlA1)
- Почему в окне выборки диапазона RefEdit не выводится наименование файла, но он присутствует. Надо ли его убирать?(Можно.Имя папки заключено в квадратные скобки [ ] ).
- Сохранять ли в диапазоне название листа выборки? (Да, но это не избавляет от проблемы возникновения ошибки при замене имени выбираемого листа пользователями на другое) (Имя листа ограничено знаком !) (Обработка ошибки желательна)
- Как настроить диалог выборки папки и файла на заданные изначально, чтобы не выбирать их повторно?
- Откуда диалоговое окно вообще берёт первичные данные, где хранятся настройки?
- Как обмениваться начальными данными с диалоговыми окнами? Надо использовать скрытый лист для обмена или можно (желательно) применять механизм обмена данными через реестр? Если последнее, то как это делать?
- Очищать ли листы перед сбором данных?
- Выбирать диапазон обработки с листа сбора или с одного из листов с данными?
- Как правильно вычленить число строк в шапке, диапазоне сбора и файле?
Чаще всего используется переменная типа Range для ссылки на диапазон или ячейку и команда определения адреса непрерывного диапазона типа
Activecell.CurrentRegion.Address
Начало и конец выделенного диапазона можно определить после выделения начальной и конечной ячеек этого диапазона и создания объекта r типа Range ссылающегося на эти ячейки. Тогда можно легко найти начальный и конечный номера строки и столбца.
- Как задать выборку до конца файла? (Легко, если реализован предыдущий пункт.)
- Можно ли вместо выборки данных с листов произвести консолидацию данных? (Суммирование данных по каждой ячейке диапазона) (Можно легко реализовать, заменяя перенос данных на лист сбора сведений суммированием этих данных, т.е. без использования заложенного в Excel изначально метода консолидации данных).
- А как быть, если консолидацию надо произвести с множества листов, но только одной книги? Стоит ли создавать новую программу, новую ветвь программы или, учитывая то, что аналогичное ПО уже создано ранее? Можно предусмотреть создание новой папки и копирование в неё отдельных листов книги как файлов, и тогда задача уже будет реализована разбираемой программой.
- Проблема отслеживания начальных, промежуточных, вычисляемых и передаваемых данных, и поиск и отслеживание ошибок. (Я не привык использовать окна отладки, а больше использую оператор MsgBox и On Error с последующим переводом этих команд в комментарии или удаления совсем.)
- Как изменить алгоритм сбора данных, если мне нужно добавлять не строки диапазона, а столбцы, и при этом давать им оригинальные и узнаваемые названия? Возможно, что надо просто дополнить алгоритм, меняя смещение на строки смещением на столбцы и, изменив перебор строка- столбец на перебор столбец-строка.
На часть основных вопросов я уже ответил, реализовав 9 вариант. Возможно, что ошибки ещё и присутствуют, но пока на моих примерах всё работает. Теперь можно разбираться и с передачей данных через реестр. На последнем этапе возможно создание надстройки. Но опять, возможно, что надо будет что-то переделывать.
Возможно появятся и другие более частные вопросы. Текст этой записи.
22 марта 2017 года я реализовал вариант сбора данных и в правых столбцах. Это уже 12 вариант изменений. Но я уверен в том, что любую программу можно улучшать бесконечно. На последнем этапе желательно преобразовывать файл Excel в шаблон и работать уже с ним. Одновременно это служит и кой-какой защите Вашего ПО от копирования.
В моем распоряжении оказались макросы перевода ФИО в родительный и дательный падежи. Я это уже сам реализовывал ранее лет 10 назад. Но новый вариант преобразования данных работает точнее, хотя и его можно улучшить в смысле оформления и сервиса.
Предлагаю Вам начальный вариант программы. Для использования файл должен иметь имя Шаблон. Переименуйте имеющийся файл в Шаблон или запустите его сначала без макросов.