navferty

navferty

https://github.com/navferty
пикабушник
1891 рейтинг 65 подписчиков 248 комментариев 8 постов 6 в горячем

Длинные строки обрезаются вместо переноса по словам

@SupportTech

При ширине окна браузера меньше 1368 пикселей строка длинной 72 символа обрезается - последние две буквы скрываются за многоточием

Длинные строки обрезаются вместо переноса по словам Баг на Пикабу, Верстка, CSS, Основы HTML

Строка:

На днях в сквере Ивана Франко срубили-таки 300-летний дуб и всю дубраву


При большем размере окна строка видна полностью

Длинные строки обрезаются вместо переноса по словам Баг на Пикабу, Верстка, CSS, Основы HTML

Браузер Chrome, версия 87.0.4280.88

Разрешение дисплея 1920x1080 (монитор DELL P2414H 23.8")

OS Windows 10

127

Приемы редактирования текста в VS Code: мультикурсор

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Visual Studio Code - текстовый редактор с отрытым исходным кодом, развиваемый компанией Microsoft. Благодаря огромному количеству расширений, написанных энтузиастами, эту программу можно превратить в мощный инструмент для широкого спектра задач, от програмирования до редактирования текстовых публикаций.


При работе с текстовыми файлами часто возникает необходимость массового редактирования некоторых фрагментов. С простыми случаями (заменить 'abc' на 'def') справляется интуитивно понятная функция автозамены, которая есть в любом распространённом редакторе. Но что делать, если Вам нужно, например, найти все слова, начинающиеся на букву "t", и поменять их местами со словом, которое идет после них?


Мультикурсор через поиск


Для начала давайте разберёмся, как составить выражение для поиска. Откроем стандатное окно поиска командой Ctrl-F (от слова Find) и вводим ' t'. Редактор автоматически подсвечивает найденные места (буква t, перед которой есть пробел), и мы можем заметить, что забыли о словах, перед которыми стоит знак препинания (например, кавычка или дефис). К счастью, в задачах вроде нашей приходят на помощь регулярные выражения, в частности, вида '\Wt', где \W - специальная последовательность, обозначающая начало слова. Активировать функцию "регулярок" можно кнопкой .*


Регулярные выражения - это отдельная очень большая тема. Если кому-то интересно, пишите в комментариях - напишу отдельный пост, посвященный им. Также можете изучить их самостоятельно на regexone.com и потренироваться на regex101.com


Теперь мы можем нажать Alt-Enter, и редактор вставит по курсору в каждом месте, где был найден искомый фрагмент:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

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

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

после выделения слова я дополнительно выделил еще один символ, чтобы "захватить" последующий пробел или знак препинания


Теперь можно вырезать выделенные фрагменты (Ctrl-X), переместиться на одно слово вправо (Ctrl-вправо) и вставить их (Ctrl-V). Если количество курсоров при вставке осталось неизменным, то каждый вырезанный фрагмент будет вставлен на своё место:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Конечно, пример "высосан из пальца", но есть немало задач, где этот способ будет полезен. Например, найти и заменить все email-адреса в тексте, исправить тэги в html или xml файле, поменять местами колонки в файле csv.


Вставка мультикурсора вручную


Множественные курсоры можно также вставить вручную, командами Ctrl-D (следующий совпадающий с выделенным фрагмент) и Ctrl-Shift-L (вставить курсоры в каждый фрагмент, совпадающий с выделенным). Например, я выделил фрагмент 'текст' и два раза нажал Ctrl-D:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Кстати, вам не нужно заучивать сочетания клавиш, которые я упомянул. Достаточно лишь запомнить "магическую" команду Ctrl-Shift-P, которая даёт доступ ко всем командам:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

Причём поиск "умный", позволяет находить команды по первым буквам, например:

Приемы редактирования текста в VS Code: мультикурсор Полезное, Редактирование текста, Длиннопост

В эта статье я рассказал лишь о нескольких функциях, которыми обладает этот редактор. Вы можете найти больше полезной информации на официальном сайте: Visual Studio Code Tips and Tricks. Поделитесь в комментариях, какие функции и расширения VS Code Вы считаете самыми полезными.


TL;DR

Основные команды:

Ctrl-D - добавлять курсоры по одному, на каждое совпадение (с выделенным куском)

Ctrl-Shift-L - добавить курсоры на все совпадения

Alt-Enter (в окошке поиска) - добавить курсоры на все совпадения к запросу

Показать полностью 6
27

Курсы валют с сайта НБУ на VBA

В комментариях к моему прошлому посту с примером создания макроса, который загружает курсы валют с сайта ЦБ РФ, уважаемая @Mrass19 задала вопрос про аналогичную задачу загрузки курсов с сайта НБУ. Среди пикабушников немало жителей Украины, и кому-то может оказаться полезным этот макрос.


Не буду утомлять описанием процесса, тем более что он аналогичен тому, который был подробно описан в прошлом посте, поэтому сразу перейду к описанию. Макрос объявляет две User-defined functions (UDF), которые можно вставлять в формулы, в ячейках на листе Excel:


=GetNbuCurrency("RUB";"5/31/2020")

загрузка курса выбранной валюты на заданную дату. Загрузка происходит с официального API НБУ:

https://bank.gov.ua/NBU_Exchange/exchange?date=31.12.2019


=NbuTodayCurrency(C10;NOW())

загрузка курса валюты на текущую дату. Значение второго аргумента может быть любым, функция NOW используется только для того, чтобы принудительно пересчитывать значение (запрашивая API) всякий раз при изменении этого аргумента (при открытии книги, и даже при изменении значения любой ячейки в любой открытой книге Excel). Будьте осторожны при использовании этой функции - если в Вашей книге будет много ячеек с такой формулой, Вы рискуете "заDDoSить" сайт, что может обернуться баном для Вас. Разумнее всего будет добавить эту формулу в одно место (например, на отдельный лист), откуда уже использовать значение, ссылаясь на эту ячейку в своих формулах.

Курсы валют с сайта НБУ на VBA Microsoft Excel, Vba, Курс валют, Длиннопост

Ссылка на репозиторий, где можно скачать книгу с макросом и примерами, а также ознакомиться с исходным кодом:


https://github.com/navferty/NBU-VBA-Currencies


Если найдёте косяки, или будут идеи по доработке макроса - пишите в комментариях!

Не могу не упомянуть о надстройке для MS Excel, о которой я недавно публиковал пару постов. Проект с открытым исходным кодом, опубликован под свободной лицензией MIT (неограниченное право на использование, копирование, изменение).

Курсы валют с сайта НБУ на VBA Microsoft Excel, Vba, Курс валют, Длиннопост

Инструкции по установке и описание функций можно найти на сайте проекта:

https://www.navferty.ru


или на странице проекта на гитхабе:

https://github.com/navferty/NavfertyExcelAddIn

Показать полностью 2
94

Курсы валют с сайта ЦБ на VBA (с автообновлением)

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

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Итак, на сайте ЦБ РФ находим официальные API для загрузки динамики курсов (на случай, если нам потребуется загружать курс на определенную дату в прошлом):

HTTP GET-запрос вида

http://www.cbr.ru/scripts/XML_dynamic.asp?date_req1=02/03/20...

где R01235 - это код валюты (доллар США). Список кодов валют для указания в запросе также доступен.

API возвращает данные в XML-формате, и для нас это даже удобнее, чем JSON - так как в VBA есть поддержка XML, а для парсинга JSON нужны сторонние библиотеки. Вот пример ответа:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видим, мы получили список курсов на каждую дату в интервале, с указанием кода валюты - именно то, что нам нужно.


Теперь попробуем сделать такой запрос из VBA. Для начала подключим в нашей книге ссылку на зависимость MSXML 6.0 (если у вас доступна другая версия, ничего страшного - могут немного поменяться имена типов, в целом логика такая же)

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Заодно добавим ссылку на Microsoft Scripting Runtime - оттуда мы сможем использовать тип Dictionary для хранения соответствий кодов валют (USD -> R01235).

Итак, создадим простую функцию, которая отправит запрос и загрузит ответ в XML-документ (код примера я выложил на гитхабе: https://github.com/navferty/CBR-VBA-Currencies ):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Кстати, неплохо расписан механизм XmlHttpRequest с примерами авторизации тут: https://codingislove.com/http-requests-excel-vba/


Как видим, у нас есть XML-документ, по которому мы можем пройти в цикле, извлекая значения курса валюты на каждую дату. Давайте попробуем это сделать! Но для начала, добавим класс для того, чтобы хранить эти значения. Нам нужны три свойства (property) - код валюты (строка), дата и собственно значение (для сумм желательно использовать тип decimal). Добавим class module:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Свойства можно добавлять командой Insert -> Procedure... , это автоматически сгенерирует геттеры и сеттеры. Можно обойтись и просто публичными полями - но публичный доступ через свойства позволяет управлять доступом к данным, и вообще считается хорошим тоном =)


Функция CDec - преобразование значения в десятичное число. Настоятельно рекомендую использовать именно этот тип, а не Double, для работы с денежными суммами, это позволит избежать ошибок округления десятичных дробей:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Но вернёмся к нашей задаче. Итак, создадим коллекцию, которую будем наполнять записями типа CurrencyRecord:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как видно на скриншоте, мы обходим элементы, которые содержат курсы на каждую дату (можно немного переделать обращение к resultXmlDocument.LastChild.ChildNodes с использованием XPath - для более наглядной навигации по XML документу).


Убедившись, что запрос и обработка ответа работают, перепишем нашу функцию для запроса валюты по ее коду, добавив словарь с соответствием кодов валют, а также конструирование пути с запросом с учётом даты начала/окончания и кода валюты:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Также я поменял CurrencyCode при создании записи в цикле - вместо "R01235" запись будет содержать общеизвестный код валюты "USD").


RequestGetXml - вспомогательная функция, которая загружает XML-документ с помощью HTTP-запроса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Проверим, что вся функция работает как ожидается, добавив временную процедуру для теста:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Теперь добавим публичную функцию, которую можно будет использовать в формулах на листе (так называемую UDF - user-defined function):

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

С помощью функции DateAdd определяем интервал дат для загрузки - от вчера до сегодня. Получив две записи, возьмём значение из последней:

Set currItem = col.Item(col.Count) ' не забываем, что в VBA нумерация с 1


Обратите внимание на второй аргумент - volatileArg. Его значение не используется, но он понадобится для того, чтобы заставить Excel пересчитывать значение при изменении значения этого аргумента в формуле ячейки:

=GetTodayCurrency("USD"; NOW())

Чтобы убедиться, что обновление курса было выполнено, также добавим Debug.Pring с сообщением об успешной загрузке. После успешной отладки этот вызов можно будет удалить, чтобы не засорять окно Immediate.


Теперь проверим, что при открытии книги будет выполнена загрузка курса:

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Как мы видим, в окне Immediate есть сообщение об успешной загрузке курса - сразу после открытия книги. Тем не менее, такое же сообщение будет появляться и при любом изменении на листе - Excel будет пересчитывать значение формулы. Поэтому если у Вас в книге будет много ячеек с вызовом этой функции, лучше добавить кэш - например, статическую переменную, в которой будет словарь (Dictionary). Ключом может выступать сочетание кода валюты и даты, значение - собственно CurrencyRecord. Это поможет избежать множества одинаковых запросов на сайт ЦБ. Если кому-то будет интересно - напишите, добавлю его в комментарии или отдельным постом =)


Спасибо всем кто дочитал, предлагайте в комментариях варианты доработки и улучшения предложенного решения! За идею поста спасибо @AlexeyE30 !

Напоследок упомяну о надстройке для MS Excel - я недавно писал о ней. Проект с открытым исходным кодом, опубликован под свободной лицензией MIT (неограниченное право на использование, копирование, изменение).

Курсы валют с сайта ЦБ на VBA (с автообновлением) Vba, Курс валют, Автоматизация, Длиннопост

Инструкции по установке и описание функций можно найти тут: Надстройка для MS Excel


Страница проекта на гитхабе:

https://github.com/navferty/NavfertyExcelAddIn

Показать полностью 11
215

Надстройка для MS Excel (часть 2)

Всем привет!

В прошлом посте я представил на суд общественности свой open-source проект - надстройку для MS Excel, с набором полезных функций. Пост был встречен очень тепло, многие пикабушники оставили пожелания о добавлении новых функций, а несколько человек написали мне с предложениями подключиться к разработке. Спасибо @1041618 за редизайн - мы обзавелись новыми иконками в едином стиле и поддержкой серой и тёмной тем, а также учитываем в локализации язык пакета MS Office.

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


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

Транслитерация кириллицы в латинские буквы


Конвертирует содержимое текстовых ячеек, содержащих символы кириллицы, в латинские символы (по стандарту ICAO doc 9303), за идею спасибо @negotivko

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Подсветка дублей


Раскрашивает разными цветами группы одинаковых значений:

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

«Размерживание» объединенных ячеек с их заполнением


При разбиении объединенных ячеек стандартной функцией Excel все ячейки области, кроме левой верхней, остаются пустыми. Но теперь можно заполнить их значением исходной ячейки:
Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Экспорт таблицы в markdown


Markdown - это удобный язык разметки, используемый при форматировании текстов во многих системах: wiki (Confluence), GitHub, Gitlab, Reddit, Stack Exchange, OpenStreetMap и множество других. Функция копирования таблицы в markdown помещает в буфер обмена отформатированную таблицу с заголовками, и Вы легко можете вставить ее в свой документ:

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Проверка значений в выделенных ячейках (числа, даты, ИНН ЮЛ/ФЛ и т.д.)


Можно проверить, например, список ИНН организаций на корректность (по контрольным цифрам в номере):

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Кроме того, есть две функции по работе с XML-файлами. Они не относятся напрямую к функциональности Excel, но иногда в них возникает потребность:

Сформировать пример XML файла на основе XSD-схемы


Если у вас есть файл XSD, который содержит xml-схему, Вы можете сформировать образец XML-файла на основе этой схемы:

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

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


Проверить XML по XSD-схеме


Позволяет проверить имеющийся XML-документ на соответствие схеме, описанной в XSD-файле. Выберите оба файла, и при наличии ошибок все они будут выведены на лист Excel.

Установка надстройки


Для установки надстройки выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel (часть 2) Microsoft Excel, Надстройка, Полезное, Длиннопост

Важный момент: при установке надстройки Excel "запоминает" путь к папке, из которой она была установлена, и в будущем установка обновлений будет возможна только из этой папки, в противном случае нужно будет воспользоваться "установкой и удалением программ" через Панель управления Windows. В настоящее время исследуется вопрос о публикации установочных файлов в сети для облегчения процесса установки и доставки обновлений.


Для установки надстройки нужно запустить файл .vsto


Хотел бы предупредить об одном ограничении: при вызове функций надстройки (как COM-надстроек, так и VBA) в MS Excel очищается стек последних действий пользователя для отмены (Undo). Я работаю над тем, чтобы обойти это ограничение хотя бы для возможности отмены выполнения самой функции, но пока что будьте внимательны при запуске функций - отменить действие можно будет, только закрыв книгу без сохранения.


Более подробная инструкция по установке, а также инструкции по использованию и исходный код - на странице проекта в гитхабе:

https://github.com/navferty/NavfertyExcelAddIn


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


Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

Показать полностью 6
855

Надстройка для MS Excel

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

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Исходный код проекта и инструкции по установке и использованию опубликованы на GitHub под лицензией MIT (неограниченное право на использование, копирование, изменение).


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

Ниже приведу краткое описание некоторых функций.

Конвертация чисел, форматированных как текст


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


Ниже пример преобразования чисел при помощи надстройки. Столбец B для примера заполнен формулой "=A2+1", которая демонстрирует, является ли значение слева числом:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Переключение регистра текста


В MS Word есть удобная функция, доступная по Shift+F3, которая переключает регистр выделенного текста (в последовательности "Sentence case" -> "lowercase" -> "UPPERCASE"). Иногда такой функции не хватает и в Excel, но надстройка восполняет этот пробел:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Очистка текста от пробельных символов


Позволяет очистить текст от пробелов в начале и конце значения, а также от повторяющихся пробелов и переносов строки в середине текста:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Интерактивный поиск ячеек, в которых произошла ошибка вычисления


Показывает список всех ячеек с ошибкой (например, "#Н/Д"), и позволяет быстро перемещаться к выбранной ячейке:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

И другие функции:


- Подсветка дублей (разными цветами группы одинаковых значений)

- «Размерживание» объединенных ячеек с их заполнением

- Снятие пароля с защищённой книги и листов

- Экспорт таблицы в markdown

- Проверка значений в выделенном диапазоне ячеек (числа, даты, корректный ИНН ЮЛ/ФЛ и т.д.)


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


- Сформировать пример XML файла на основе XSD-схемы

- Проверить XML по XSD-схеме


Установка надстройки


Для автоматической сборки установочных файлов настроена сборка в Azure.

Выберите последнюю по времени успешную сборку пайплайна NavfertyExcelAddIn - Publish и скачайте опубликованные файлы:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Важный момент: при установке надстройки Excel "запоминает" путь к папке, из которой она была установлена, и в будущем установка обновлений будет возможна только из этой папки, в противном случае нужно будет воспользоваться "установкой и удалением программ" через Панель управления Windows.


Для установки надстройки нужно запустить файл .vsto. Разумеется, установка возможно только при наличии установленного MS Excel =)


Если всё сделано правильно, то Вы увидите новую вкладку при следующем запуске Excel:

Надстройка для MS Excel Microsoft Excel, Надстройка, Полезное, Длиннопост

Если Вы .NET-разработчик и хотите присоединиться к работе над проектом, пишите мне на почту (указана в профиле гитхаба), заводите issue и пулл-реквесты.

Если Вы только изучаете платформу .NET, и хотите поучаствовать - не стесняйтесь! Читайте исходный код, задавайте вопросы, если что-то непонятно - буду рад объяснить и поделиться знаниями =)

Еще раз ссылка на проект (там же инструкции по использованию и установке):

https://github.com/navferty/NavfertyExcelAddIn

Показать полностью 6
139

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel

Многие из тех, кто часто работает в MS Excel, собрали коллекцию макросов на VBA, которые облегчают их ежедневный труд. У кого-то эти макросы хранятся в отдельной книге, кто-то собрал их в "личной книге макросов" (personal.xlsb), доступной на уровне всего приложения, и вручную добавил кнопки вызова нужных скриптов на панель инструментов. В первом случае коллекцией удобно делиться с коллегами - достаточно переслать файл, но чтобы ей воспользоваться, необходимо каждый раз открывать эту книгу. Во втором случае доступ к функциональности есть сразу при запуске Excel, но могут возникнуть проблемы с передачей наработок другим пользователям.

В данном посте описан способ создания VBA-настройки с пользовательской панелью инструментов на ленте Excel (Ribbon), которая позволяет воспользоваться преимуществами обоих подходов. Это файл в формате xlam, который можно передавать как обычную Excel-книгу. При её открытии пользователь видит появившуюся панель инструментов, которая даёт доступ к функциональности надстройки:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

При желании можно установить её на постоянной основе, чтобы не возникало необходимости её открывать каждый раз.

Используемые инструменты: MS Excel, 7zip, Visual Sudio Code. Последние два необязательны, подойдёт любой архиватор с поддержкой zip-формата и любой текстовый редактор, умеющий в UTF-8 (если Вы хотите использовать кириллицу).

Также желательно обладать базовым представлением о формате XML.


Код и результат в виде книги xlsm можно найти на гитхабе:

https://github.com/navferty/SampleExcelXlamAddin

Итак, для начала откроем новую книгу, и добавим модуль с образцом кода:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Сохраним книгу в формате xlsm (книга с поддержкой макросов). Современные форматы документов MS Office (xlsx, xlsm, docx, docm, pptx и т.д.) основаны на стандарте OpenXML и представляют из себя обычный zip-архив, который состоит из компонентов (например, XML-файлы, соответствующие листам, изображения, бинарный контент и прочее), а также файлов отношений (.rels), которые задают структуру всего документа, путём ссылок на компоненты.

Чтобы получить доступ к внутреннему содержимому книги, можно переименовать файл Sample.xlsm в Sample.zip, после чего открыть его любым архиватором. Некоторые архиваторы, например 7zip, умеют открывать файлы напрямую, без изменения имени файла:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Чтобы добавить ribbon-панель, нам нужно будет создать новый компонент customUI/customUI.xml, а также добавить ссылку на него в корневой файл отношений _rels/.rels

Разархивируем весь документ в отдельную папку ("Распаковать в "Sample\""), и создадим в ней новую папку customUI:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь добавим собственно компонент:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Ниже приводится текстовая версия содержимого customUI.xml


<?xml version="1.0" encoding="utf-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id="customTab" label="Sample Add-in" keytip="XT">

<group id="Tools" label="Group 1">

<button id="HelloWorldBtn" imageMso="BlackAndWhiteAutomatic" keytip="H" label="Привет, Мир!"

onAction="SayHelloWorld" screentip="Поприветствовать мир" size="large" />

<button id="DuplicatesBtn" imageMso="SmartArtChangeColorsGallery" keytip="D"

label="Выделение цветом дублей" onAction="DuplicateColors"

screentip="Выделение парными цветами повторов в выделенном диапазоне" size="large" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Коротко расскажу о важных элементах и атрибутах.

Значения идентификаторов (атрибут id) должны быть уникальными. Для некоторых элементов (например, tab - в случае, если Вы объявляете новую вкладку, а не существующую, путем указания idMso) также обязательно указать label - иначе Excel не отобразит этот элемент.

С помощью атрибута imageMso можно определить иконку элемента из числа имеющихся в MS Office. Список доступных значений весьма объемный, поэтому здесь приводить не буду - он легко гуглится.

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

Атрибут onAction задаёт название процедуры, которая будет выполнена при основном взаимодействии с элементом (например, при нажатии на кнопку button). Процедура должна иметь определённую сигнатуру (количество и тип аргументов), их можно найти в спецификации. Например, метод для onAction у элемента button принимает один аргумент типа IRibbonControl.

Название этого метода также не должно совпадать с названием модуля (либо необходимо явно указать имя модуля перед именем самого метода: "Module1.SayHelloWorld").

Атрибут keytip поможет в навигации с помощью клавиатуры - с помощью клавиши Alt можно осуществлять навигацию по вкладкам MS Excel.


Вы можете скопировать образец вёрстки на официальном сайте с документацией от Microsoft:

Образец customUI на docs.microsoft.com


Для тех, кто хочет подробно изучить стандарт, есть спецификация, также на docs.microsoft.com:

[MS-CUSTOMUI]: Custom UI XML Markup Specification

Обратите внимание, что при наличии не-ASCII символов (например, кириллицы) файл следует сохранить в кодировке UTF-8, иначе Вы можете столкнуться с тем, что Excel не будет отображать новую вкладку.


Чтобы MS Excel "увидел" и "понял", что за компонент мы добавили, нужно указать ссылку на него в корневом файле отношений, который лежит в папке _rels, и называется .rels

Он также имеет xml-формат, и для удобства можно отформатировать его (в VS Code для этого есть команда: Ctrl-Shift-P -> Format Document).

Добавляем элемент Relashionship, указав путь к customUI.xml, тип компонента, а также идентификатор (не имеет значения, какой именно. Главное, чтобы он был уникальным среди других элементов Relashionship):

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Добавляю текст отношения для удобства копирования (чтобы парсер не "съел" URL, добавил пробел перед .com - не забудьте его убрать при копировании)

<Relationship Id="rID4" Target="customUI/customUI.xml" Type="http://schemas.microsoft .com/office/2006/relationships/ui/extensibility" />

Теперь заархивируйте обратно все файлы, и верните исходное имя файла Sample.xlsm

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Если всё было сделано правильно, то при открытии книги Вы увидите новую вкладку на ленте:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Однако при попытке запустить макрос HelloWorld Вы можете столкнуться с таким предупреждением:

Wrong number of arguments or invalid property assignment

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

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Добавьте аргумент "rc As IRibbonControl", и Вы увидите долгожданное приветствие:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Список сигнатур можно найти на сайте документации Microsoft


Теперь осталось лишь сохранить книгу с макросами как надстройку. В редакторе VBE выберите объект ThisWorkbook и установите свойство IsAddin в значение True.

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь нужно сохранить книгу в новом формате (Excel предупредит об этом, если Вы попытаетесь сохранить книгу нажатием Ctrl-S)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Теперь надстройку можно открыть, как обычную книгу Excel. Она не будет отображаться в отдельном окне, как книга, но её можно увидеть в Project Explorer'е редактора VBE:

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Для того, чтобы надстройка была доступна сразу при запуске Excel, установите её в меню Excel Add-ins на вкладке разработчика...

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

...или через настройки Excel, в разделе Надстройки (Add-ins)

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

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

P.S. В качестве бонуса приведу примеры использования различных элементов в Custom UI

toggleButton - кнопка, которая может быть в двух состояниях

splitButton - составной компонент из button или toggleButton и выпадающего меню

dropDown - выпадающее меню с заранее определённым набором элементов

comboBox - интерактивное поле ввода, которое может содержать заранее определённый набор элементов

dynamicMenu - выпадающий список, элементы которого определяются динамически в методе getContent

checkBox - чекбокс, который может быть в двух состояниях

Туториал: создание VBA-надстройки с отдельной вкладкой в MS Excel Microsoft Excel, Vba, Туториал, Вкладки, Длиннопост

Разметка и код VBA модуля ниже, также их можно найти на гитхабе


customUI.xml


<?xml version="1.0" encoding="utf-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>

<tabs>

<tab id="customTab" label="Sample Add-in" keytip="XT">

<group id="SampleControls" label="Group 2">

<splitButton id="splitButton" size="large" >

<button id="SplitButton" imageMso="HappyFace" label="Split Button" />

<menu id="menu">

<button id="button1" label="Button 1" onAction="OnSplitButton1Click" />

<button id="button2" label="Button 2" onAction="OnSplitButton2Click" />

</menu>

</splitButton>

<toggleButton id="ToggleButton" label="Toggle Button" onAction="OnToggleButtonClick" />

<dropDown id="DropDown" label="DropDown" onAction="OnDropDownSelected" >

<item id="DropDownItem1" label="Item 1" />

<item id="DropDownItem2" label="Item 2" />

<item id="DropDownItem3" label="Item 3" />

<button id="button" label="Button..." />

</dropDown>

<comboBox id="ComboBox" label="Combo Box" onChange="OnComboBoxSelected" >

<item id="ComboBoxItem1" label="Item 1" />

<item id="ComboBoxItem2" label="Item 2" />

<item id="ComboBoxItem3" label="Item 3" />

</comboBox>

<separator id="separator" />

<dynamicMenu id="DynamicMenu" label="Dynamic Menu" getContent="GetMenuContent" />

<checkBox id="CheckBox" label="Check Box" onAction="OnCheckBoxToggled" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

VBA модуль SampleControls

Option Explicit


Public Sub OnSplitButton1Click(rc As IRibbonControl)

MsgBox "Split button 1 was clicked"

End Sub


Public Sub OnSplitButton2Click(rc As IRibbonControl)

MsgBox "Split button 2 was clicked"

End Sub


Public Sub OnToggleButtonClick(rc As IRibbonControl, isButtonPressed As Boolean)

MsgBox "Toggle button was toggled, button now is " & IIf(isButtonPressed, "pressed", "not pressed")

End Sub


Public Sub OnDropDownSelected(rc As IRibbonControl, selectedItemId As String, selectedItemIndex As Integer)

MsgBox "DropDown was changed, selected item id is " & selectedItemId

End Sub


Public Sub OnComboBoxSelected(rc As IRibbonControl, comboBoxValue As String)

MsgBox "Combo box was changed, value is " & comboBoxValue

End Sub


Public Sub GetMenuContent(rc As IRibbonControl, ByRef returnedVal)

Dim xml As String


xml = "<menu xmlns=""http://schemas.microsoft.com/office/2006/01/customui"">" & _

"<button id=""but1"" imageMso=""Help"" label=""Help"" onAction=""OnHelpPressed""/>" & _

"<button id=""but2"" imageMso=""FindDialog"" label=""Find"" onAction=""OnFindPressed""/>" & _

"</menu>"


returnedVal = xml

End Sub


Public Sub OnCheckBoxToggled(rc As IRibbonControl, isButtonChecked As Boolean)

MsgBox "Check box was toggled, value is " & IIf(isButtonChecked, "checked", "not checked")

End Sub


Public Sub OnHelpPressed(rc As IRibbonControl)

MsgBox "Help button pressed"

End Sub


Public Sub OnFindPressed(rc As IRibbonControl)

MsgBox "Find button pressed"

End Sub

Более подробная информация о различных элементах Custom UI есть в документации:

Документация по элементам Custom UI от Microsoft

P.P.S. Дополнительный бонус для тех кто дочитал до конца - горячие клавиши редактора VBE

Навигация по редактору

Ctrl-R - перейти в окно проектов (Project Explorer)

F4 - перейти к свойствам

Ctrl-G - перейти к Immediate window

F7 - перейти в окно редактора кода

Ctrl-Tab (Ctrl-Shift-Tab) - переключение между открытыми окнами модулей

Ctrl-F4 -закрыть текущий модуль

Alt-F11 - открыть редактор кода VBE


Редактирование и навигация по коду

Ctrl-Space - автодополнение

Ctrl-J - показать доступные варианты (IntelliSense)

Ctrl-I - показать информацию о методе (аргументах)

Shift-F2 - перейти к определению переменной/метода


Отладка

F5 - запустить макрос/продолжить исполнение

F8 - шаг вперёд (с заходом во вложенные функции)

Shift-F8 - шаг вперёд (без захода вглубь)

F9 - установить/снять точку останова (breakpoint)

Shift-F9 - добавить наблюдателя (quick watch)

Ctrl-L - показать стек вызовов (кликабельный)

Показать полностью 14
90

Приёмы отладки VBA-кода (на примере MS Excel)

Многие из тех, кто часто работает в MS Excel, используют книги или надстройки с VBA-макросами. Зачастую требуется доработать существующее решение, добавив новую функциональность или исправив найденный баг. В поисках места, где скрывается баг или производится нужное вычисление, можно провести немало времени, особенно если проект содержит десятки тысяч строк кода (увы, такие макросы иногда встречаются). Можно прибегнуть к текстовому поиску (например по тексту сообщения, вшитому в код), но иногда удобнее запустить макрос и прибегнуть к пошаговой отладке.

В этом посте описаны несколько приёмов, которые могут быть полезны при отладке макросов на VBA в приложениях MS Office на примере MS Excel.


1. Стандартные точки останова (breakpoint). Step-into, step-over

2. Окна Immediate ("REPL-терминал"), Locals (локальные переменные), Watches (наблюдатели)

3. Условные точки останова на базе watch expression

4. Обработка ошибок

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

появится такая отметка (можно установить и кликом мышью по области слева, где красный кружок):

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Теперь как только поток исполнения доберётся до этой строки, он будет приостановлен, а Вы увидите желтый указатель, указывающий на следующую инструкцию:

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Многие не знают, что этот указатель можно перемещать на любой statement в пределах функции, просто перетащив мышью желтую стрелку!


Когда Вы приостановили исполнение кода, можете продолжить его исполнение пошагово, с помощью команд step-into, step-over и step-out:

* step-into (F8) - шаг вперёд. Будет выполнен код на строке под указателем, а сам указатель перемещён на следующую строку. Однако если в текущей строке есть вызов другой функции, указатель переместится внутрь этой функции

* step-over (Shift-F8) - полностью аналогичен step-into, за исключением того, что эта команда позволяет не "проваливаться" в вызов вложенных функций

* step-out (Ctrl-Shift-F8) - текущая функция будет исполнена до конца, а исполнение приостановлено в вызывающей функции. Полезна, если Вы случайно провалились в длинную функцию командой step-into


Теперь немного о том, как узнать значения переменных и иную полезную информацию.

2. В нижней части экрана VBE (Visual basic editor) Вы можете увидеть три окна:


* Immediate (терминал для выполнения кода и вывода текстовой информации)

* Locals (локальные переменные)

* Wathes (наблюдатели)

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Если Вы не видите эти окна, включите их отображение в меню View

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Разберём подробнее, какие возможности открывают нам эти инструменты.


Окно Immediate представляет собой REPL-терминал, в котором можно ввести выражение и запустить его нажатием Enter. Чтобы вывести результат в этот же терминал, передайте результат выражения в функцию Debug.Print (кстати, эту функцию можно вызвать и в основном коде макроса - результат так же будет выведен в консоль Immediate). В самой консоли можно воспользоваться и сокращённой версией этой команды - знаком вопроса:

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Обратите внимание, что с помощью сочетаний Ctrl+Space и Ctrl+J можно вызывать окно IntelliSense , также как и в основном редакторе кода.


Окно Locals позволяет увидеть значения переменных, доступных в текущем контексте:

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Например, текущее значение счётчика цикла или состав коллекции. Обратите внимание, что для объектов Collection при раскрытии отображаются значения элементов коллекции, а для словарей (Dictionary) - их ключи. Поэтому чтобы узнать значение самого элемента в словаре, нужно вычислить выражение, например такое:

dict.Items("key1")

Это можно сделать при помощи "наблюдателя" - watch expression

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Выделите выражение и выполните команду Add watch... в контекстном меню (доступном с помощью правой кнопки мыши), или введите нужно выражение вручную в окне добавления наблюдателя:

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Осторожно: выражение d.Item("key1") при вычислении может добавить элемент к словарю, если такой ключ в словаре отсутствует. Поэтому не забудьте удалить наблюдатель, когда он станет ненужным, иначе в дальнейшем можно провести немало времени, выясняя откуда взялся лишний элемент в словаре.

3. Условные точки останова, которые можно реализовать с помощью наблюдателей.


Вероятно, Вы обратили внимание, что в меню добавления наблюдателя помимо стандартной опции "Watch Expression" есть ещё две: "Break When Value Is True" и "Break When Value Changes". Они позволяют добиться приостановки исполнения макроса при наступлении соответствующего условия (выражение истинно, либо значение выражения изменилось). Эта возможность может оказаться особенно полезной, например, при отладке циклов. Если Вы знаете, что макрос, который в цикле обрабатывает строки на листе Excel, сталкивается с ошибкой, скажем, на строке 42, то можно избежать ручного "пролистывания" 41 предшествующей итерации, добавив выражения вида i=42 с опцией Break When Value Is True, где i - это переменная счётчика цикла.

4. Напоследок хотел бы немного рассказать об обработке ошибок в VBA.

В настройках VBE Вы можете найти выбор из трёх опций (меню Tools -> Options, вкладка General):

Break on All Errors

Break in Class Module

Break on Unhandled Errors

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

По умолчанию, как правило, установлена последняя опция, которая позволяет программисту обрабатывать ошибки с помощью директив On Error Goto (показать сообщение об ошибке, "прибраться за собой" - закрыть книгу или соединение) и On Error Resume Next (игнорировать ошибку и продолжать исполнение). Однако, когда Вы столкнётесь с ошибкой вида "Извините, что-то пошло не так =(", которая отображается из обработчика в On Error Goto, будет трудно определить место, где произошла ошибка. В этом случае пригодится опция Break on All Errors, которая позволит проигнорировать директивы On Error и перейти к тому месту в коде, где и произошла ошибка. В этот момент может пригодиться и окно Locals, где Вы сможете увидеть, например, текущее значение счётчика цикла, и понять, при обработке какой строки на листе Excel возникла ошибка.


Указанные опции обработки ошибок устанавливаются на уровне настроек пользователя, и не сохраняются в VBA-проекте.

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


Минутка рекламы.

В свободное время я пилю надстройку общего назначения для MS Excel на платформе VSTO. Проект доступен на гитхабе под лицензией MIT (можете свободно использовать в своих проектах, форкать и изменять под свои нужды):

https://github.com/navferty/NavfertyExcelAddIn

Приёмы отладки VBA-кода (на примере MS Excel) Microsoft Excel, Vba, Длиннопост

Призываю неравнодушных пользователей оставить пожелания по добавлению новых функций (заводите issue на гитхабе, или пишите прямо тут в комментариях), а разработчиков, знакомых с технологией VSTO (или желающих познакомиться) - присоединяться к разработке!


Это мой первый пост на Пикабу, буду рад конструктивной критике в комментариях и ЛС.

Показать полностью 8
Отличная работа, все прочитано!