Содержание:
Даже если вы не умеете программировать, есть множество мест (книги, сайты, форумы), где можно найти готовый код макросов VBA для огромного количества типовых задач в Excel. По моему опыту, большинство пользователей рано или поздно собирают свою личную коллекцию макросов для автоматизации рутинных процессов, будь то перевод формул в значения, отображение сумм прописью или суммирование ячеек по цвету. И тут возникает проблема — код макроса в Visual Basic нужно где-то хранить, чтобы потом использовать в работе.
Самый простой вариант — сохранить код макроса прямо в рабочем файле, перейдя в редактор Visual Basic с помощью сочетания клавиш. другой+F11 и добавление нового пустого модуля через меню Вставка – Модуль:
Однако у этого метода есть несколько недостатков:
- Если рабочих файлов много и везде нужен макрос, например макрос преобразования формул в значения, то придется копировать код в каждой книге.
- Не следует забывать сохранить файл в формате с поддержкой макросов (xlsm) или в формате двоичной книги (xlsb).
- При открытии такого файла макрозащита каждый раз будет выдавать предупреждение, которое нужно подтвердить (ну или отключить защиту полностью, что не всегда может быть желательно).
Более элегантным решением было бы создать ваша собственная надстройка (надстройка Excel) — отдельный файл специального формата (xlam), содержащий все ваши «любимые» макросы. Преимущества этого подхода:
- Этого будет достаточно подключить дополнение один раз в Excel – и вы сможете использовать его процедуры и функции VBA в любом файле на этом компьютере. Пересохранение ваших рабочих файлов в xlsm- и xlsb-форматах, таким образом, не требуется, т.к. исходный код будет храниться не в них, а в файле надстройки.
- Protection вас также не будут беспокоить макросы. дополнения по определению являются надежными источниками.
- Сможет сделать отдельная вкладка на ленте Excel с красивыми кнопками для запуска макросов надстройки.
- Надстройка представляет собой отдельный файл. Его легко носить с собой с компьютера на компьютер, поделитесь с коллегами или даже продайте 😉
Давайте шаг за шагом рассмотрим весь процесс создания собственной надстройки Microsoft Excel.
Шаг 1. Создайте файл надстройки
Откройте Microsoft Excel с пустой книгой и сохраните ее под любым подходящим именем (например MyExcelAddin) в формате надстройки с помощью команды Файл — Сохранить как или ключи F12, указав тип файла Надстройка Excel:
Обратите внимание, что по умолчанию Excel хранит надстройки в папке C:UsersYour_nameAppDataRoamingMicrosoftAddIns, но, в принципе, вы можете указать любую другую, удобную вам папку.
Шаг 2. Подключаем созданную надстройку
Теперь надстройка, которую мы создали на последнем шаге MyExcelAddin должен быть подключен к Excel. Для этого зайдите в меню Файл – Параметры – Дополнения. (Файл — Параметры — Надстройки)нажмите на кнопку О нас (Идти) внизу окна. В открывшемся окне нажмите кнопку Обзор (Просматривать) и указываем расположение нашего файла надстройки.
Если вы все сделали правильно, то наш MyExcelAddin должно появиться в списке доступных дополнений:
Шаг 3. Добавьте макросы в надстройку
Наша надстройка подключена к Excel и успешно работает, но в ней пока нет ни одного макроса. Давайте наполним его. Для этого откройте редактор Visual Basic с помощью сочетания клавиш другой+F11 или по кнопке Визуальный Бейсик таб разработчик (Разработчик). Если вкладки разработчик не видно, его можно отобразить через Файл – Параметры – Настройка ленты. (Файл — Параметры — Настроить ленту).
В левом верхнем углу редактора должно появиться окно Проект (если его не видно, то включите его через меню Просмотр — Обозреватель проекта):
В этом окне отображаются все открытые книги и запущенные надстройки Microsoft Excel, включая нашу. ВБАПроект (MyExcelAddin.xlam) Выделите его мышкой и добавьте к нему новый модуль через меню. Вставка – Модуль. В этом модуле мы будем хранить код VBA наших макросов надстройки.
Вы можете либо набрать код с нуля (если умеете программировать), либо скопировать его откуда-нибудь в готовом виде (что гораздо проще). Давайте для проверки внесем в добавленный пустой модуль код простого, но полезного макроса:
После ввода кода не забудьте нажать на кнопку сохранения (дискета) в левом верхнем углу.
Наш макрос ФормулыToValues, как вы легко можете себе представить, преобразует формулы в значения в заранее выбранном диапазоне. Иногда эти макросы еще называют Процедуры. Для его запуска необходимо выделить ячейки с формулами и открыть специальное диалоговое окно. Макрос на вкладке разработчик (Разработчик — Макросы) или сочетание клавиш другой+F8. Обычно в этом окне отображаются доступные макросы из всех открытых книг, но макросы надстроек здесь не отображаются. Несмотря на это, мы можем ввести имя нашей процедуры в поле имя макроса (имя макроса)а затем нажмите кнопку Run (бежать) — и наш макрос будет работать:
| |
Здесь же можно назначить сочетание клавиш для быстрого запуска макроса — за это отвечает кнопка параметры (Параметры) в предыдущем окне Макрос:
При назначении клавиш имейте в виду, что они чувствительны к регистру и раскладке клавиатуры. Итак, если вы назначите комбинацию типа Ctrl+Й, то, собственно, в дальнейшем вам придется убедиться, что у вас включена раскладка, и нажать дополнительно Shiftчтобы получить заглавную букву.
Для удобства мы также можем добавить кнопку нашего макроса на панель быстрого доступа в левом верхнем углу окна. Для этого выберите Файл – Параметры – Панель быстрого доступа. (Файл — Параметры — Настроить панель быстрого доступа), а затем в раскрывающемся списке вверху окна выберите опцию Макрос. После этого наш макрос ФормулыToValues можно разместить на панели кнопкой Добавить (Добавить) и выберите для него иконку кнопкой Изменить (Редактировать):
Шаг 4. Добавьте функции в надстройку
Но макропроцедуры, это также функциональные макросы или как они называются UDF (Пользовательская функция = определяемая пользователем функция). Давайте создадим в нашем дополнении отдельный модуль (команда меню Вставка – Модуль) и вставьте туда код следующей функции:
Нетрудно заметить, что данная функция нужна для вычета НДС из суммы с учетом НДС. Не бином Ньютона, конечно, но нам он вполне подойдет в качестве примера, показывающего основные принципы.
Обратите внимание, что синтаксис функции отличается от синтаксиса процедуры:
- используется конструкция Функция…. Конечная функция вместо Суб… Конец сабвуфера
- после названия функции в скобках указываются ее аргументы
- в теле функции производятся необходимые вычисления и затем результат присваивается переменной с именем функции
Также обратите внимание, что эта функция не нужна и ее невозможно запустить, как предыдущую процедуру макроса, через диалоговое окно. Макрос и кнопка Run. Такую макрофункцию следует использовать как стандартную функцию рабочего листа (СУММ, ЕСЛИ, ВПР…), т.е. просто ввести в любую ячейку, указав в качестве аргумента значение суммы с НДС:
… или войти через стандартное диалоговое окно вставки функции (кнопка fx в строке формул), выбрав категорию User Defined (Определяемые пользователем):
Единственный неприятный момент здесь – отсутствие привычного описания функции внизу окна. Чтобы добавить его, вам придется сделать следующее:
- Откройте редактор Visual Basic с помощью сочетания клавиш. другой+F11
- Выберите надстройку на панели «Проект» и нажмите клавишу F2чтобы открыть окно Обозревателя объектов
- Выберите проект надстройки из раскрывающегося списка в верхней части окна.
- Щелкните правой кнопкой мыши появившуюся функцию и выберите команду. Объекты.
- Введите описание функции в окно Описание
- Сохраните файл надстройки и перезапусти эксель.
После перезапуска функция должна отобразить введенное нами описание:
Шаг 5. Создайте вкладку дополнения в интерфейсе.
Последним, хоть и не обязательным, но приятным штрихом станет создание отдельной вкладки с кнопкой запуска нашего макроса, которая появится в интерфейсе Excel после подключения нашей надстройки.
Информация о вкладках, отображаемых по умолчанию, содержится внутри книги и должна быть отформатирована в специальном XML-коде. Писать и редактировать такой код проще всего с помощью специальных программ – XML-редакторов. Одной из самых удобных (и бесплатных) является программа Максима Новикова. XML-редактор ленты.
Алгоритм работы с ним следующий:
- Закройте все окна Excel, чтобы не было конфликта файлов при редактировании XML-кода надстройки.
- Запустите программу Ribbon XML Editor и откройте в ней наш файл MyExcelAddin.xlam.
- С кнопкой Вкладки в левом верхнем углу добавьте фрагмент кода для новой вкладки:
- Вам нужно поставить пустые кавычки id нашу вкладку и группу (любые уникальные идентификаторы), а в этикетка — названия нашей вкладки и группы кнопок на ней:
- С кнопкой кнопка на левой панели добавьте пустой код кнопки и добавьте к нему теги:
- этикетка это текст на кнопке
— изображениеМсо — это условное название изображения на кнопке. Я использовал значок красной кнопки под названием AnimationCustomAddExitDialog. Названия всех доступных кнопок (а их несколько сотен!) можно найти на большом количестве сайтов в Интернете, если поискать по ключевым словам «imageMso». Для начала можно пойти сюда.
- onAction — это название процедуры обратного вызова — специального короткого макроса, который будет запускать наш основной макрос ФормулыToValues. Вы можете называть эту процедуру как угодно. Мы добавим его чуть позже.
- Проверить правильность всего проделанного можно с помощью кнопки с зеленой галочкой вверху панели инструментов. Там же нажмите на кнопку с дискетой, чтобы сохранить все изменения.
- Закройте XML-редактор ленты.
- Открываем Excel, переходим в редактор Visual Basic и добавляем в наш макрос процедуру обратного вызова. KillFormulasчтобы он запускал наш основной макрос для замены формул значениями.
- Сохраняем изменения и, вернувшись в Excel, проверяем результат:
Вот и все – надстройка готова к использованию. Наполните его своими процедурами и функциями, добавьте красивые кнопки – и использовать макросы в работе станет намного проще.
- Что такое макросы, как их использовать в работе, где взять код макросов в Visual Basic.
- Как сделать заставку при открытии книги в Excel
- Что такое персональная книга макросов и как ею пользоваться