Как создать свою надстройку для Microsoft Excel

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

Самый простой вариант - сохранять код макросов прямо в рабочем файле, зайдя в редактор Visual Basic с помощью сочетания клавиш Alt+F11 и добавив новый пустой модуль через меню Insert - Module:

Добавление модуля в книгу

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

  • Если рабочих файлов много, а макрос нужен везде, как например макрос преобразования формул в значения, то и копировать код придется в каждую книгу.
  • Нужно не забыть сохранить файл в формате с поддержкой макросов (xlsm) или в формате двоичной книги (xlsb).
  • При открытии такого файла защита от макросов будет каждый раз выдавать предупреждение, которое нужно подтвердить (ну, или отключить защиту полностью, что может быть не всегда желательно).

Более изящным решением будет создание своей собственной надстройки (Excel Add-in) - отдельного файла особого формата (xlam), содержащего все ваши "любимые" макросы. Плюсы такого подхода:

  • Достаточно будет один раз подключить надстройку в Excel - и можно будет использовать её VBA процедуры и функции в любом файле на этом компьютере. Пересохранять ваши рабочие файлы в xlsm- и xlsb-форматы, таким образом, не потребуется, т.к. исходный код будет храниться не в них, а в файле надстройки.
  • Защита от макросов вас тоже беспокоить уже не будет, т.к. надстройки по определению входят в доверенные источники.
  • Можно сделать отдельную вкладку на ленте Excel с красивыми кнопками для запуска макросов надстройки.
  • Надстройка - это отдельный файл. Его легко переносить с компьютера на компьютер, делиться им с коллегами или даже продавать ;)

Давайте рассмотрим весь процесс создания своей собственной надстройки для Microsoft Excel по шагам.

Шаг 1. Создаем файл надстройки

Открываем Microsoft Excel с пустой книгой и сохраняем ее под любым подходящим именем (например MyExcelAddin) в формате надстройки с помощью команды Файл - Сохранить как или клавиши F12, указав тип файла Надстройка Excel (Excel Add-in):

Сохраняем файл в формате надстройки

Обратите внимание, что стандартно Excel хранит надстройки в папке C:\Users\Ваше_имя\AppData\Roaming\Microsoft\AddIns, но, в приниципе, можно указать любую другую удобную вам папку.

Шаг 2. Подключаем созданную надстройку

Теперь созданную нами на прошлом шаге надстройку MyExcelAddin надо подключить к Excel. Для этого идем в меню Файл - Параметры - Надстройки (File - Options - Add-Ins), жмем на кнопку Перейти (Go) в нижней части окна. В открывшемся окне жмем кнопку Обзор (Browse) и указываем положение нашего файла надстройки.

Если вы все сделали правильно, то наша MyExcelAddin должна появиться в списке доступных надстроек:

Список надстроек

Шаг 3. Добавляем в надстройку макросы

Наша надстройка подключена к Excel и успешно работает, но в ней нет пока ни одного макроса. Давайте её наполним. Для этого нужно открыть редактор Visual Basic сочетанием клавиш Alt+F11 или кнопкой Visual Basic на вкладке Разработчик (Developer). Если вкладки Разработчик не видно, то её можно отобразить через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon).

В левом верхнем углу редактора должно быть окно Project (если его не видно, то включите его через меню View - Project Explorer):

Project Explorer

В этом окне отображаются все открытые книги и запущенные надстройки Microsoft Excel, в том числе и наша VBAProject (MyExcelAddin.xlam) Выделите её мышью и добавьте в неё новый модуль через меню Insert - Module. В этом модуле мы и будем хранить VBA-код наших макросов надстройки.

Код можно либо набрать "с нуля" (если вы умеете программировать), либо скопировать откуда-нибудь уже готовый (что гораздо проще). Давайте, для пробы, введем в добавленный пустой модуль код простого, но полезного макроса:

Код макроса

После набора кода не забудьте нажать на кнопку сохранения (дискетку) в левом верхнем углу.

Наш макрос FormulasToValues, как легко сообразить, преобразует формулы в значения в выделенном предварительно диапазоне. Иногда такие макросы называют еще процедурами. Чтобы его запустить, нужно выделить ячейки с формулами и открыть специальное диалоговое окно Макросы с вкладки Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. Обычно в этом окне отображаются доступные макросы из всех открытых книг, но макросы надстроек здесь не видны. Несмотря на это, мы можем ввести имя нашей процедуры в поле Имя макроса (Macro name), а затем нажать кнопку Выполнить (Run) - и наш макрос заработает:

Запускаем макрос из надстройки     
Результат

Здесь же можно назначить сочетание клавиш для быстрого запуска макроса - за это отвечает кнопка Параметры (Options) в предыдущем окне Макрос:

Назначаем сочетание клавиш макросу

При назначении клавиш имейте ввиду, что здесь учитывается регистр и раскладка клавиатуры. Поэтому, если вы назначите сочетание, например, Ctrl+Й, то, по факту, вам придется в будущем следить за тем, чтобы у вас была включена именно русская раскладка и жать дополнительно Shift, чтобы получить заглавную букву.

Для удобства можно добавить и кнопку для нашего макроса на панель быстрого доступа в левом верхнем углу окна. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar), а затем в выпадающем списке в верхней части окна опцию Макросы. После этого наш макрос FormulasToValues можно поместить на панель кнопкой Добавить (Add) и выбрать для него значок кнопкой Изменить (Edit):

Назначаем кнопку макросу на панели быстрого доступа

Шаг 4. Добавляем в надстройку функции

Кроме макросов-процедур, существуют еще и макросы-функции или как их еще называют UDF (User Defined Function = пользовательская функция). Давайте создадим в нашей надстройке отдельный модуль (команда меню Insert - Module) и вставим туда код такой функции:

Пользовательская функция

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

Заметьте, что синтаксис функции отличается от процедуры:

  • используется конструкция Function .... End Function вместо Sub ... End Sub
  • после названия функции в скобках указываются её аргументы
  • в теле функции производятся необходимые вычисления и затем результат присваивается переменной с названием функции

Также обратите внимание, что эту функцию не нужно, да и невозможно запустить как предыдущий макрос-процедуру через диалоговое окно Макросы и кнопку Выполнить. Такую макрофункцию нужно использовать как стандартную функцию листа (СУММ, ЕСЛИ, ВПР...), т.е. просто ввести в любую ячейку, указав в качестве аргумента значение суммы с НДС:

Используем созданную UDF

... или ввести через стандартное диалоговое окно вставки функции (кнопка fx в строке формул), выбрав категорию Определенные пользователем (User Defined):

Выбираем пользовательскую функцию

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

  1. Откройте редактор Visual Basic сочетанием клавиш Alt+F11
  2. Выделите надстройку в панели Project и нажмите клавишу F2, чтобы открыть окно Object Browser
  3. Выберите в верхней части окна в выпадающем списке свой проект надстройки
  4. Щелкните по появившейся функции правой кнопкой мыши и выберите команду Properties.
  5. Введите описание функции в окно Description
  6. Сохраните файл надстройки и перезапустите Excel.

Добавляем свое описание для UDF

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

Описание функции

Шаг 5. Создаем вкладку надстройки в интерфейсе

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

Информация об отображаемых вкладках по умолчанию содержится внутри книги и должна быть оформлена в виде специального XML-кода. Проще всего писать и редактировать такой код с помощью специальных программ - XML-редакторов. Одна из самых удобных (и бесплатных) - это программа Максима Новикова Ribbon XML Editor.

Алгоритм работы с ней следующий:

  1. Закройте все окна Excel, чтобы не было конфликта файлов, когда мы будем редактировать XML-код надстройки.
  2. Запустите программу Ribbon XML Editor и откройте в ней наш файл MyExcelAddin.xlam
  3. При помощи кнопки tabs в левом верхнем углу добавьте заготовку кода для новой вкладки:

    Добавляем новую вкладку для надстройки

  4. В пустые кавычки нужно вписать id нашей вкладки и группы (любые уникальные идентификаторы), а в label - названия нашей вкладки и группы кнопок на ней:

    Добавляем id и названия

  5. При помощи кнопки button на панели слева добавляем заготовку кода для кнопки и дописываем к ней теги:

    Добавляем кнопку

    - label - это текст на кнопке
    - imageMso - это условное название изображения на кнопке. Я использовал иконку с красной кнопкой, которая имеет название AnimationCustomAddExitDialog. Названия всех доступных кнопок (а их несколько сотен!) можно найти на большом количестве сайтов в интернете, если искать по ключевым словам "imageMso". Для начала можно сходить сюда.
    - onAction - это имя процедуры обратного вызова - специального короткого макроса, который будет запускать наш основной макрос FormulasToValues. Назвать эту процедуру можно как угодно. Мы добавим её чуть позже.
  6. Проверить правильность всего сделанного можно с помощью кнопки с зеленой галочкой сверху на панели инструментов. Там же рядом нажмите на кнопку с дискетой для сохранения всех изменений.
  7. Закрываем Ribbon XML Editor
  8. Открываем Excel, заходим в редактор Visual Basic и добавляем к нашему макросу процедуру обратного вызова KillFormulas, чтобы она запускала наш основной макрос замены формул на значения.

    Добавляем процедуру обратного вызова
  9. Сохраняем внесенные изменения и, вернувшись в Excel проверяем результат:

    Созданная вкладка для надстройки

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

Ссылки по теме




11.09.2018 15:55:51
Если это надстройка понадобится в дальнейшем для его быстрого подключения лучше упаковать его в архив и создать самораспакующий архив который распакует в путь %AppData%\Microsoft\Excel\XLSTART
18.09.2018 23:58:27
а вот нифига подобного…
никаких архивов не надо и лежать может где угодно — хоть на сетевом диске (я так в конторе сделал)
13.09.2018 19:13:08
Привет, Николай!
Спасибо за подарок к Дню программиста!
"Чего бы поймал, знакомым раздал, не жалко"
Доброго времени суток!
Давно ждал такого позновательного видео на эту тему! Спасибо!

Есть 2 вопроса, прошу помощи.

Подскажите пожалуйста, возможно ли как-то редактировать такую надстройку, например, добавить еще одну кнопку в свою вкладку? Или надо все делать с нуля?

Еще вопрос. Если у меня, например, есть перечень средних цен товаров, которые я хочу выводить на экран (быстро смотреть цену какого-то товара) через вот такую надстройку (чтобы всегда было под рукой), где хранить данный перечень? Перечень у меня на листе excel. Могу ли я создавать надстройку сразу с таким перечнем на листе и потом создать форму с быстрым поисковиком, которая будет вызываться через вкладку "Моя надстройка"?
Так и быть, сам отвечу на свои вопросы:
1. Да, открываем надстройку через Ribbon XML Editor и продолжаем добовлять кнопки и что хотим.
2. Да! Для редактирования в свойствах книг IsAddin ставите False, надстройка будет отображаться с листами, как обычная книга. Добавляем сколько надо листов, делаете с ними что хотите, затем возвращаете свойство IsAddin в True, сохраняемся.
;)
18.09.2018 18:47:47
Николай,  Спасибо большое! С макросами дружу на UpperIntermediate уровне. Пыталась сама создать ленту и кнопки для надстройки, тоже через эту программу, но ничего не поняла. Вами так все ясно изложено. Прям потираю руки,  что у моих макросов наконец таки появятся кнопочки! А не вот это вот всё)
18.09.2018 23:56:20
Как известно, "лишь тот разбирается в своём деле, кто может новичку объяснить смысл простыми словами. И он поймёт".
Как всегда блестяще, Николай! ))

P.S.: как ваше отчество? Развейте уже сомнения на форуме))))
19.09.2018 17:28:54
Спасибо! Рад помочь.

P.S. Владимировичи мы :)
19.09.2018 17:31:02
спасибо! :)
Добрый день!
Спасибо, отличная статья!
Только не получилось сохранить описание к функции по извлечения НДС. У меня EXCEL 2016 :cry:
27.11.2018 08:40:16
После ввода описания функции нужно обязательно сохранить файл надстройки и перезапустить Excel.
12.10.2018 11:36:36
СПАСИБО ОГРОМНОЕ, НИКОЛАЙ!!!
Очень полезно и очень познавательно, очень практично и удобно. Все в одной вкладке и можно не таскать с собой макросы и не ставить на разные машины, на которых работаешь.
Ещё раз - СПАСИБО!
29.10.2018 15:53:39
Добрый день! Николай, подскажите, пожалуйста, у меня при создании своей надстройки возникает следующая проблема: когда я сохраняю файл с расширением "надстройка Excel" ( в нем уже есть весь код), то он работает только в данной книге. Когда я открываю новую книгу (все остальные закрыты), то ексель просто не видит мою надстройку. Хотя сам файл с кодом я вижу в той папке в которую я его сохранил. Как можно это исправить? надстройка подключена.
Заранее спасибо)
04.12.2018 18:41:40
Николай, добрый день.
Спасибо большое за столь ценную информацию и возможность самостоятельно создать надстройку (в большей степени для сохранения интересных макросов).
Наверх