Библиотека готовых макросов для эксель. Макросы

Пособие содержит макросы на следующие темы:
Запуск макроса с поиском ячейки.
Запуск макроса при открытии книги.
Запуск макроса при вводе в ячейку «2».
Запуск макроса при нажатии «Ентер».
Добавить в панель свою вкладку «Надстройки» (Формат ячейки).
Работа с файлами (т. е. обмен данными с ТХТ, RTF, XLS и т. д.).
Проверка наличия файла по указанному пути.
Поиск нужного файла.
Автоматизация удаления файлов.
Произвольный текст в строке состояния.
Восстановление строки состояния.
Бегущая строка в строке состояния.

Быстрое изменение заголовка окна.
Изменение заголовка окна (со скрытием названия файла).
Возврат к первоначальному заголовку.
Что открыто в данный момент.
Работа с текстовыми файлами.
Запись и чтение текстового файла.
Обработка нескольких текстовых файлов.
Определение конца строки текстового файла.
Копирование из текстового файла в эксель.

Копирование содержимого в текстовый файл.
Экспорт данных в txt.
Экспорт данных в html.
Импорт данных, для которых нужно более 256 столбцов.
Создание резервных копий ценных файлов.
Подсчет количества открытий файла.
Вывод пути к файлу в активную ячейку.
Копирование содержимого файла RTF в эксель.
Копирование данных из закрытой книги.
Извлечение данных из закрытого файла.
Поиск слова в файлах.
Создание текстового файла и ввод текста в файл.
Создание текстового файла и ввод текста (определение конца файла).
Создание документов Word на основе таблицы Excel.
Команды создания и удаления каталогов.
Получение текущего каталога.
Смена каталога.

Посмотреть все файлы в каталоге.
Посмотреть все файлы в каталоге.
Рабочая область Microsoft Excel.
Рабочая книга.
Количество имен рабочей книги.
Защита рабочей книги.
Запрет печати книги.
Открытие книги (или текстовых файлов).
Открытие книги и добавление в ячейку А1 текста.
Сколько книг открыто.
Закрытие всех книг.
Закрытие рабочей книги только при выполнении условия.
Сохранение рабочей книги с именем, представляющим собой текущую дату.
Сохранена ли рабочая книга.
Создать книгу с одним листом.
Создать книгу.
Удаление ненужных имен.
Быстрое размножение рабочей книги.
Сортировка листов.
Поиск максимального значения на всех листах книги.
Рабочий лист.
Проверка наличия защиты рабочего листа.
Список отсортированных листов.
Создать новый лист.
Создать новый лист.
Удаление листов в зависимости от даты.
Копирование листа в книге.
Копирование листа в новую книгу (создается).
Перемещение листа в книге.
Перемещение нескольких листов в новую книгу.
Заменить существующий файл.
«Перелистывание» книги.
Вставка колонтитула с именем книги, листа и текущей датой.
Существует ли лист.
Существует ли лист.
Вывод количества листов в активной книге.
Вывод количества листов в активной книге в виде гиперссылок.
Вывод имен активных листов по очереди.
Вывод имени и номеров листов текущей книги.
Сделать лист невидимым.
Сколько страниц на всех листах?
Ячейка и диапазон (столбцы и строки).
Копирование строк на другой лист.
Копирование столбцов на другой лист.
Подсчет количества ячеек, содержащих указанные значения.
Подсчет количества ячеек в диапазоне, содержащих указанные значения.
Подсчет количества видимых ячеек в диапазоне.
Определение количества ячеек в диапазоне и суммы их значений.
Подсчет количества ячеек.
Автоматический пересчет данных таблицы при изменении ее значений.
Ввод данных в ячейки.
Ввод данных с использованием формул.
Последовательный ввод данных.
Ввод текстоввых данных в ячейки.
Вывод в ячейки названия книги, листа и количества листов.
Удаление пустых строк.
Удаление пустых строк.
Удаление пустых строк.
Удаление строки по условию.
Удаление скрытых строк.
Удаление используемых скрытых строк или строк с нулевой высотой.
Удаление дубликатов по маске.

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

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

Поиск с выделением найденных данных.
Поиск по условию в диапазоне.
Поиск последней непустой ячейки диапазона.
Поиск последней непустой ячейки столбца.
Поиск последней непустой ячейки строки.
Поиск ячейки синего цвета в диапазоне.
Поиск отрицательного значения в диапазоне и выделения синим цветом.
Поиск наличия значения в столбце.
Поиск совпадений в диапазоне.
Поиск ячейки в диапазоне.
Поиск ячейки в диапазоне.
Поиск приближенного значения в диапазоне.
Поиск начала и окончания диапазона, содержащего данные.
Поиск начала данных.
Автоматическая замена значений.
Быстрое заполнение диапазона (массив).
Заполнение через интервал(массив).
Заполнение указанного диапазона(массив).
Заполнение диапазона(массив).
Расчет суммы первых значений диапазона.
Размещение в ячейке электронных часов.
«Будильник».
Оформление верхней и нижней границ диапазона.
Адрес активной ячейки.
Координаты активной ячейки.
Формула активной ячейки.
Получение из ячейки формулы.
Тип данных ячейки.
Вывод адреса конца диапазона.
Получение информации о выделенном диапазоне.
Взять слово с 13 символа в ячейке.
Создание изменяемого списка (таблица).
Проверка на пустое значение.
Пересечение ячеек.
Умножение выделенного диапазона на.
Одновременное умножение всех данных диапазона.
Деление диапазона на.
Возведение каждой ячейки диапазона в квадрат.
Суммирование данных только видимых ячеек.
Сумма ячеек с числовыми значениями.
При суммировании — курсор внутри диапазона.

Начисление процентов в зависимости от суммы.
Начисление процентов в зависимости от суммы.
Сводный пример расчета комиссионного вознаграждения.
Движение по диапазону.
Сдвиг от выделенной ячейки.
Перебор ячеек вниз по колонне.
Создание заливки диапазона.
Подбор параметра ячейки.
Разбиение диапазона.
Объединение данных диапазона.
Объединение данных диапазона.
Узнать максимальную колонку или строку.
Ограничение возможных значений диапазона.
Тестирование скорости чтения и записи диапазонов.
Открыть MsgBox при выборе ячейки.
Скрытие строки.
Скрытие нескольких строк.
Скрытие столбца.
Скрытие нескольких столбцов.
Скрытие строки по имени ячейки.
Скрытие нескольких строк по адресам ячеек.
Скрытие столбца по имени ячейки.
Скрытие нескольких столбцов по адресам ячеек.
Мигание ячейки.
Работа с примечаниями.
Вывод на экран всех примечаний рабочего листа.
Функция извлечения комментария.
Список примечаний защищенных листов.

Перечень примечаний в отдельном списке.
Перечень примечаний в отдельном списке.

Подсчет количества примечаний.
Подсчет примечаний.
Выделение ячеек с примечаниями.
Отображение всех примечаний.
Изменение цвета примечаний.
Добавление примечаний.
Добавление примечаний в диапазон по условию.
Перенос комментария в ячейку и обратно.

Перенос значений из ячейки в комментарий.
Пользовательские вкладки на ленте.
Дополнение панели инструментов.
Добавление кнопки на панель инструментов.
Панель с одной кнопкой.
Панель с двумя кнопками.
Создание панели справа.

Создание пользовательского меню (вариант 1).
Создание пользовательского меню (вариант 2).
Создание пользовательского меню (вариант 3).
Создание пользовательского меню (вариант 4).
Создание пользовательского меню (вариант 5).
Создание пользовательского меню (вариант 6).
Создание списка пунктов главного меню Excel.
Создание списка пунктов контекстных меню.
Отображение панели инструментов при определенном условии.
Скрытие и отображение панелей инструментов.
Создать подсказку к моим кнопкам.
Создание меню на основе данных рабочего листа.
Создание контекстного меню.
Блокировка контекстного меню.
Добавление команды в меню Сервис.
Добавление команды в меню Вид.
Создание панели со списком.
Мультфильм с помощником в главной роли.
Дополнение помощника текстом, заголовком, кнопкой и значком.
Новые параметры помощника.
Использование помощника для выбора цвета заливки.
ДИАЛОГОВЫЕ ОКНА.
Функция INPUTBOX (через ввод значения).
Вызов предварительного просмотра.
Настройка ввода данных в диалоговом окне.

Открытие диалогового окна (Открыть файл).
Открытие диалогового окна (Печать).
Другие диалоговые окна.
Вызов броузера из Экселя.
Диалоговое окно ввода данных.
Диалоговое окно настройки шрифта.
Значения по умолчанию.
Форматирование текста. Таблицы. ГРАНИЦЫ И ЗАЛИВКА.
Вывод списка доступных шрифтов.
Выбор из текста всех чисел.
Прописная буква только в начале текста.
Подсчет количества повторов искомого текста.
Выделение из текста произвольного элемента.
Отображение текста «задом наперед».
Англоязычный текст — заглавными буквами.
Запуск таблицы символов из Excel.
информация о пользователе, компьютере, принтере и т. д.
Получить имя пользователя.
Вывод разрешения монитора.
Получение информации об используемом принтере.
Просмотр информации о дисках компьютера.
ЮЗЕРФОРМЫ.
ДИАГРАММЫ.
Построение диаграммы с помощью макроса.
Сохранение диаграммы в отдельном файле.
Построение и удаление диаграммы нажатием одной кнопки.
Вывод списка диаграмм в отдельном окне.
Применение случайной цветовой палитры.
Эффект прозрачности диаграммы.
Построение диаграммы на основе данных нескольких рабочих листов.
Создание подписей к данным диаграммы.
РАЗНЫЕ ПРОГРАММЫ.
Программа для составления кроссвордов.
Создать обложку DVD.
Игра «Минное поле».
Игра «Угадай животное».
Расчет на основании ячеек определенного цвета.
ДРУГИЕ ФУНКЦИИ И МАКРОСЫ.
Вызов функциональных клавиш.
Расчет среднего арифметического значения.
Перевод чисел в «деньги».
Поиск ближайшего понедельника.
Подсчет количества полных лет.
Расчет средневзвешенного значения.
Преобразование номера месяца в его название.
Использование относительных ссылок.
Преобразование таблицы Excel в HTML-формат.
Генератор случайных чисел.
Случайные числа — на основании диапазона.
Применение функции без ввода ее в ячейку.
Подсчет именованных объектов.
Включение автофильтра с помощью макроса.
Создание бегущей строки.
Создание бегущей картинки.
Вращающиеся автофигуры.
Вызов таблицы цветов.
Создание калькулятора.
Склонение фамилии, имени и отчества.
ДАТА И ВРЕМЯ.
Вывод даты и времени.
Вывод даты и времени.
Получение системной даты.
Извлечение даты и часов.
Функция ДатаПолная.Используемая версия MS Office не указана.

VBA считается стандартным языком написания сценариев для приложений Microsoft, и в настоящее время он входит в состав всех приложений Office и даже приложений других компаний. Следовательно, овладев VBA для Excel, вы сможете сразу перейти к созданию макросов для других программных продуктов Microsoft. Более того, вы сможете создавать полноценные программные продукты, одновременно использующие функции самых разных приложений.

Как включить макросы в Excel

По умолчанию, вкладка, отвечающая за управление и навигацию макросов в Excel, скрыта. Чтобы активировать данную опцию перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры Excel, перейдите по вкладке Настройка ленты, в правом поле со списком ставим маркер напротив вкладки Разработчик. Данные действия актуальны для версий Excel 2010 и старше.

На ленте появиться новая вкладка Разработчик с элементами управления автоматизации Excel.

Написание макросов в Excel

Во вкладке Разработчик в группе Код, нажмите кнопку Запись макроса. Появиться диалоговое окно Запись макроса, которая запрашивает некоторую информацию о будущем записываемом коде. Если вы впервые создаете макрос, можете просто нажать кнопку ОК. С данного момента Excel будет фиксировать каждое действие пользователя в модуле VBA, будь то ввод данных, форматирование или создание диаграмм. Чтобы остановить запись макроса, нажмите кнопку Остановить запись, которая находится в той же группе Код.

Вы также можете воспользоваться альтернативным вариантом записи макросов, воспользовавшись кнопкой Запись макроса, которая находится в левом нижнем углу рабочей книги Excel (правее статуса Готово ).

Теперь вы можете посмотреть список всех созданных макросов, нажав на кнопку Макрос, находящуюся в группе Код. В появившемся диалоговом окне вы можете дать более описательные имена своим кодам или задать сочетания клавиш, которые бы запускали тот или иной макрос. Альтернативным вариантом запуска данного окна является нажатие клавиш Alt + F8.

Редактирование макросов

Поздравляю! Вы написали свой первый макрос. Логичным будет проверить теперь, какой же код сгенерировал нам Excel. Сгенерированный код написан на языке VBA (Visual Basic for Applications). Чтобы увидеть его, нужно открыть Редактор VB (VBE), который запускается нажатием клавиш Alt + F11 или кнопкой Visual Basic на вкладке Разработчик.

Чтобы избежать путаницы в редакторе вы можете работать только с одной вкладкой рабочей книги, листа или модуля. Так выглядит редактор в реалии.

Предлагаю на данном этапе подробнее изучить различные окна и меню редактора VBA. В дальнейшем это поможет вам сэкономить кучу времени.

Для просмотра кода, щелкните по ветке Modules в окне проектов и дважды щелкните по появившейся ветке Module 1 . Редактор откроет окно с кодом, как изображено на картинке.

Здесь можно редактировать сгенерированный код, который был записан при работе в Excel. К примеру, вам требуется заполнить определенный столбец значениями от 1 до 10. У вас уже есть первые три шага, которые вводят значения 1, 2 и 3 в первые три ячейки столбца А. Нам необходимо дописать оставшиеся семь шагов.

Если вы посмотрите на приведенный выше код, вы увидите, что макрос определенным образом структурирован. Сначала приложение перемещает курсор на ячейку с помощью команды Range("A1").Select, затем редактирует его содержимое с помощью ActiveCell.FormulaR1C1 = "1". Таким образом, для оставшихся шагов мы можем повторить эти действия, меняя адрес ячейки и значение, которое вы хотите записать в эту ячейку. Например, чтобы задать ячейке A4 значение 4, вы должны написать:

Range("A4" ).Select ActiveCell.FormulaR1C1 = "4"

И повторить аналогичные шаги для оставшихся значений.

После того, как вы закончите редактирование, сохраните книгу. Запустить макрос вы сможете нажатием кнопки F5, либо, вернувшись в рабочую книгу Excel, перейти по вкладке Разработчик в группу Код -> Макросы и выбрать из списка, интересующий вас макрос.

Уделите несколько минут внимательному изучению кода, который сгенерировал Excel. Если вы новичок, вклад нескольких минут в изучение кода дадут потрясающий результат в знакомстве с объектами VBA в дальнейшем. Обратите внимание, что рассмотренный нами пример всего лишь иллюстрация. Есть более быстрые и эффективные способы достижения аналогичных результатов, о которых мы поговорим далее.

Увеличение скорости выполнения макросов Excel

Пока все хорошо. Давайте разберем пару хитростей, которые помогут ускорить выполнение макроса. Рассмотрим в качестве примера фрагмент кода, приведенный выше. Современные компьютеры отработают рассматриваемый код так быстро, что вы даже не заметите этого. Но что если вам необходимо выполнить операцию 50000 раз. Это займет какое-то время. Если макрос, написанный вами, исчисляется сотнями строк, ускорить выполнение кода можно путем обрезки части процессов, который не используется во время исполнения макроса.

Использование команды Application.ScreenUpdating

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

1 2 3 4 5 6 7 8 9 10

Sub Макрос1() Range("A1" ).Select Range("A2" ).Select Range("A3" ).Select End Sub

Команда Application.ScreenUpdating говорит Excel, чтобы он прекратил выводить пересчитанные данные на экран, а выдал готовые значения в конце выполнения кода.

Использование команды Application. Calculation

Вторая хитрость заключается в отключении автоматических вычислений. Давайте я поясню. Каждый раз, когда пользователь или процесс обновляет ячейку, Excel пытается пересчитать все зависимые от нее ячейки. Так скажем, если ячейка, которую пытается обновить макрос, влияет на 10000 остальных ячеек, Excel будет пытаться пересчитать их все до того, как закончится выполнение кода. Соответственно, если существует целый ряд влияющих ячеек, пересчет может значительно замедлить выполнение кода. Чтобы этого не происходило, вы можете установить команду Application. Calculation в начале кода, которая переключит пересчет формул в ручной режим, а затем вернуть автоматическое вычисление в конце макроса.

1 2 3 4 5 6 7 8 9 10 11 12

Sub Макрос1() Application.ScreenUpdating = False Range("A1" ).Select ActiveCell.FormulaR1C1 = "1" Range("A2" ).Select ActiveCell.FormulaR1C1 = "2" Range("A3" ).Select ActiveCell.FormulaR1C1 = "3" Application.ScreenUpdating = True End Sub

Будьте внимательны, не забудьте переключить данную опцию снова в автоматический режим в конце макроса. В противном случае, вам необходимо будет это сделать в самом Excel, перейдя по вкладке Формулы в группу Вычисление и выбрать Параметры вычислений –> Автоматический.

Избежание выбора ячеек и диапазонов

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

1 2 3 4 5 6 7 8 9 10 11

Sub Макрос1() Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Range("A1" ).Value = 1 Range("A2" ).Value = 2 Range("A3" ).Value = 3 Range("A4" ).Value = 4 Range("A5" ).Value = 5 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub

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

Примеры макросов Excel

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

Добрый день!

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

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

Если вы никогда не слышали о макросе, то самым точным его определением будет таким, это действия которые запрограммированы на определённую последовательность и записаны в среде программирования на языке Visual Basic for Applications (VBA) . Запуск макроса может производиться многократно и это заставит Excel выполнять любую последовательность необходимых нам действий, которые вручную выполнять нам просто не нравится или не хочется. Несмотря на великое множество языков программирования для всего комплекса Microsoft Office стандартом является именно VBA и он работает в любом приложении офисного пакета.

Итак, создать макрос в Excel возможно 2 способами:

Создать макрос в Excel с помощью макрорекордера

Для начала проясним, что собой представляет макрорекордер и при чём тут макрос.

Макрорекордер – это вшитая в Excel небольшая программка, которая интерпретирует любое действие пользователя в кодах языка программирования VBA и записывает в программный модуль команды, которые получились в процессе работы. То есть, если мы при включенном макрорекордере, создадим нужный нам ежедневный отчёт, то макрорекордер всё запишет в своих командах пошагово и как итог создаст макрос, который будет создавать ежедневный отчёт автоматически.

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

• Записать макрорекордер может только то, что может пощупать, а значит записывать действия он может только в том случае, когда используются кнопки, иконки, команды меню и всё в этом духе, такие варианты как для него недоступна;
• В случае, когда в период записи была допущена ошибка, она также запишется. Но можно кнопкой отмены последнего действия, стереть последнюю команду которую вы неправильно записали на VBA;
• Запись в макрорекордере проводится только в границах окна MS Excel и в случае, когда вы закроете программу или включите другую, запись будет остановлена и перестанет выполняться.

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

Следующим шагом в работе с макрорекордером станет настройка его параметров для дальнейшей записи макроса, это можно произвести в окне «Запись макроса» , где:

• поле «Имя макроса» — можете прописать понятное вам имя на любом языке, но должно начинаться с буквы и не содержать в себе знаком препинания и пробелы;
• поле «Сочетание клавиш» — будет вами использоваться, в дальнейшем, для быстрого старта вашего макроса. В случае, когда вам нужно будет прописать новое , то эта возможность будет доступна в меню «Сервис» — «Макрос» — «Макросы» — «Выполнить» или же на вкладке «Разработчик» нажав кнопочку «Макросы» ;
• поле «Сохранить в…» — вы можете задать то место, куда будет сохранен (но не послан) текст макроса, а это 3 варианта:
  • «Эта книга» — макрос будет записан в модуль текущей книги и сможет быть выполнен только в случае, когда данная книга Excel будет открыта;
  • «Новая книга» — макрос будет сохранен в тот шаблон, на основе которого в Excel создается пустая новая книга, а это значит, что макрос станет доступен во всех книгах, которые будут создаваться на этом компьютере с этого момента;
  • «Личная книга макросов» — является специальной книгой макросов Excel, которая называется «Personal.xls» и используется как специальное хранилище-библиотека макросов. При старте макросы из книги «Personal.xls» загружаются в память и могут быть запущены в любой книге в любой момент.
• поле «Описание» — здесь вы можете описать, что и как должен делать макрос, для чего он создавался и какие функции несет, это чисто информативное поле, что называется на память.

После того как вы запустили и записали свой макрос, выполнив все нужные действия, запись можно прекратить командой «Остановить запись» и ваш макрос с помощью макрорекордера будет создан.

Создать макрос в Excel в редакторе Visual Basic.

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

Запуск редактора программ на VBA происходит по-разному, в зависимости от версии вашей программы Excel:

• в версиях Excel 2003 и более старше , нам нужно в меню «Сервис» , выбрать пункт «Макрос» и нажать «Редактор Visual Basic» ;
• в версиях Excel 2007 и более новее , нам нужно на вкладке «Разработчик» нажать кнопку «Редактор Visual Basic» . В случае когда вы не находите эту вкладку вам нужно ее активировать выбрав пункт меню «Файл» — «Параметры» — «Настройка ленты» и в диалоговом окне флажком активируем вкладку «Разработчик» .

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

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

Все наши макросы будут храниться в так называемых программных модулях . В любой из книг любой версии Excel нам доступно создание любых программных модулей в любом количестве и размещать в них все созданные нами макросы. Один модуль в состоянии содержать в себе любое количество нужных вам или созданных макросов. Модули доступны в окне «Project Explorer» и размещаются в верхнем левом углу редактора макросов (так же его можно вызвать комбинацией клавиш CTRL+R ).

Программные модули в редакторе VBA существуют в нескольких видах и используются для разных вариантов и ситуаций:

Собственно сам макрос при работе в стандартном модуле выглядит следующим образом:

Рассмотрим на примере работающего макроса :

• Все макросы в обязательном порядке будут начинаться с оператора Sub , после которого следует имя вашего макроса и список аргументов в скобочках. В случаях, когда аргументы отсутствуют скобки нужно оставить пустыми;
• В обязательном порядке все макросы заканчиваются оператором End Sub ;
• Данные что находятся между операторами Sub и End Sub , является телом макроса, которое будет работать при запуске макроса. В примере, макрос проверяет и при вводе данных находит их в списке базы данных и , выводя указанное по критериям значение.

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

Создание кнопки для запуска макросов в панели инструментов

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

Данный способ доступен для любой из версий MS Excel и заключается он в том, что мы вынесем кнопку прямо на наш рабочий лист как графический объект. Для этого вам нужно:

• В MS Excel 2003 и более старше переходите в меню «Вид» , выбираете «Панель инструментов» и нажимаете кнопку «Формы» .
• В MS Excel 2007 и более новее вам нужно на вкладке «Разработчик» открыть выпадающее меню «Вставить» и выбрать объект «Кнопка» .

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

Как создать пользовательские функции на VBA

В принципе, создание, так называемых, пользовательских функций не особо отличаются от создания обычного макроса в стандартном программном модуле. Разница между этими понятиями заключается в том что макрос будет выполнять заложенные в нём действия с объектами книги или листа (это формулы, ячейки, и т.д), а вот пользовательская функция работает только со значениями которые она получает от нас и являются аргументами они же начальные данные для вычислений.

К примеру, для создания пользовательской функции налога на добавленную стоимость, он же НДС, нам нужно открыть наш редактор VBA и добавить новый модуль, выбрать в меню «Insert» пункт «Module» и ввести туда текст для нашей функции: Следует заметить, что главное отличие функции от макроса это заголовок Function заменило Sub и в наличии заполненный список аргументов, в нашем примере это Summa . После того как наш код будет введен, он станет доступен в стандартном окне Мастера функций, который находится в меню «Формулы» , пункт «Вставить функцию» .
И выбираем категорию «Определенные пользователем» в которой и будет отображаться наша написанная функция «NDS» .
После выбора нашей функции можно установить курсор на ячейку с аргументом, в которой будет содержаться та сумма, для которой мы считаем НДС, всё происходит, как и с обычной функцией.
А на этом у меня всё! Я очень надеюсь что статья о том как создать макрос в Excel вам была понятной и полезной. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Excel располагает мощнейшей, но в то же время очень редко используемой, возможностью создавать автоматически выполняющиеся последовательности действий с помощью макросов. Макрос – идеальный выход, если Вы имеете дело с однотипной задачей, которая повторяется множество раз. Например, обработка данных или форматирование документов по стандартизированному шаблону. При этом Вам не требуется знание языков программирования.

Вам уже любопытно, что такое макрос, и как он работает? Тогда смелей вперёд – далее мы шаг за шагом проделаем весь процесс создания макроса вместе с Вами.

Что такое Макрос?

Макрос в Microsoft Office (да, этот функционал работает одинаково во многих приложениях пакета Microsoft Office) – это программный код на языке программирования (VBA), сохранённый внутри документа. Чтобы было понятнее, документ Microsoft Office можно сравнить со страницей HTML, тогда макрос – это аналог Javascript. То, что Javascript умеет делать с данными в формате HTML, находящимися на web-странице, очень похоже на то, что макрос может делать с данными в документе Microsoft Office.

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

• Применять стили и форматирование.
• Выполнять различные операции с числовыми и текстовыми данными.
• Использовать внешние источники данных (файлы баз данных, текстовые документы и т.д.)
• Создавать новый документ.
• Проделывать все вышеперечисленные действия в любой их комбинации.

Создание макроса – практический пример

Для примера возьмём самый обычный файл CSV . Это простая таблица 10х20, заполненная числами от 0 до 100 с заголовками для столбцов и строк. Наша задача превратить этот набор данных в презентабельно отформатированную таблицу и сформировать итоги в каждой строке.

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

Чтобы создать макрос, откройте View (Вид) > Macros (Макросы) > Record Macro (Запись макроса…)

Дайте своему макросу имя (без пробелов) и нажмите ОК .

Начиная с этого момента, ВСЕ Ваши действия с документом записываются: изменения ячеек, пролистывание таблицы, даже изменение размера окна.

Excel сигнализирует о том, что включен режим записи макроса в двух местах. Во-первых, в меню Macros (Макросы) – вместо строки Record Macro (Запись макроса…) появилась строка Stop Recording (Остановить запись).

Во-вторых, в нижнем левом углу окна Excel. Иконка Стоп (маленький квадратик) указывает на то, что включен режим записи макроса. Нажатие на неё остановит запись. И наоборот, когда режим записи не включен, в этом месте находится иконка для включения записи макроса. Нажатие на неё даст тот же результат, что и включение записи через меню.

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

• =SUM(B2:K2) или =СУММ(B2:K2)
• =AVERAGE(B2:K2) или =СРЗНАЧ(B2:K2)
• =MIN(B2:K2) или =МИН(B2:K2)
• =MAX(B2:K2) или =МАКС(B2:K2)
• =MEDIAN(B2:K2) или =МЕДИАНА(B2:K2)

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

После выполнения этого действия в каждой строке должны появиться соответствующие итоговые значения.

Соответственно:

• =SUM(L2:L21) или =СУММ(L2:L21)
• =AVERAGE(B2:K21) или =СРЗНАЧ(B2:K21) – для расчёта этого значения необходимо взять именно исходные данные таблицы. Если взять среднее значение из средних по отдельным строкам, то результат будет другим.
• =MIN(N2:N21) или =МИН(N2:N21)
• =MAX(O2:O21) или =МАКС(O2:O21)
• =MEDIAN(B2:K21) или =МЕДИАНА(B2:K21) – считаем, используя исходные данные таблицы, по причине указанной выше.

Теперь, когда с вычислениями закончили, займёмся форматированием. Для начала для всех ячеек зададим одинаковый формат отображения данных. Выделите все ячейки на листе, для этого воспользуйтесь комбинацией клавиш Ctrl+A , либо щелкните по иконке Выделить все , которая находится на пересечении заголовков строк и столбцов. Затем нажмите Comma Style (Формат с разделителями) на вкладке Home (Главная).

• Жирное начертание шрифта.
• Выравнивание по центру.
• Заливка цветом.

И, наконец, настроим формат итоговых значений.

Вот так это должно выглядеть в итоге:

Если Вас все устраивает, остановите запись макроса.

Поздравляем! Вы только что самостоятельно записали свой первый макрос в Excel.

Чтобы использовать созданный макрос, нам нужно сохранить документ Excel в формате, который поддерживает макросы. Для начала необходимо удалить все данные из созданной нами таблицы, т.е. сделать из неё пустой шаблон. Дело в том, что в дальнейшем, работая с этим шаблоном, мы будем импортировать в него самые свежие и актуальные данные.

Чтобы очистить все ячейки от данных, щёлкните правой кнопкой мыши по иконке Выделить все , которая находится на пересечении заголовков строк и столбцов, и из контекстного меню выберите пункт Delete (Удалить).

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

Важный момент! Если Вы сохраните файл с расширением XLTX , то макрос в нём работать не будет. Кстати, можно сохранить книгу как шаблон Excel 97-2003, который имеет формат XLT , он тоже поддерживает макросы.

Когда шаблон сохранён, можно спокойно закрыть Excel.

Выполнение макроса в Excel

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

• Макрос может нанести вред.
• Ещё раз прочти предыдущий пункт.

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

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

Следующим шагом, мы импортируем последний обновлённый набор данных из файла CSV (на основе такого файла мы создавали наш макрос).

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

Когда импорт будет закончен, зайдите в меню Macros (Макросы) на вкладке View (Вид) и выберите команду View Macros (Макросы).

В открывшемся диалоговом окне мы увидим строку с именем нашего макроса FormatData . Выберите его и нажмите Run (Выполнить).

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

Заглянем под капот: Как работает макрос?

Как уже не раз упоминалось, макрос – это программный код на языке программирования Visual Basic for Applications (VBA). Когда Вы включаете режим записи макроса, Excel фактически записывает каждое сделанное Вами действие в виде инструкций на языке VBA. По-простому, Excel пишет программный код вместо Вас.

Чтобы увидеть этот программный код, нужно в меню Macros (Макросы) на вкладке View (Вид) кликнуть View Macros (Макросы) и в открывшемся диалоговом окне нажать Edit (Изменить).

Откроется окно Visual Basic for Applications , в котором мы увидим программный код записанного нами макроса. Да, Вы правильно поняли, здесь этот код можно изменить и даже создать новый макрос. Те действия, которые мы совершали с таблицей в этом уроке, вполне можно записать с помощью автоматической записи макроса в Excel. Но более сложные макросы, с тонко настроенной последовательностью и логикой действий требуют программирования вручную.

Добавим ещё один шаг к нашей задаче…

Представьте, что наш исходный файл с данными data.csv создаётся автоматически каким-то процессом и сохраняется на диске всегда в одном и том же месте. Например, C:\Data\data.csv – путь к файлу с обновляемыми данными. Процесс открытия этого файла и импорта данных из него тоже можно записать в макрос:

1. Откройте файл шаблона, в котором мы сохранили макрос – FormatData .
2. Создайте новый макрос с именем LoadData .
3. В процессе записи макроса LoadData сделайте импорт данных из файла data.csv – как мы это делали в предыдущей части урока.
4. Когда импорт будет завершён, остановите запись макроса.
5. Удалите все данные из ячеек.
6. Сохраните файл, как шаблон Excel с поддержкой макросов (расширение XLTM).

Таким образом, запустив этот шаблон, Вы получаете доступ к двум макросам – один загружает данные, другой их форматирует.

Если есть желание заняться программированием, Вы можете объединить действия этих двух макросов в один – просто скопировав код из LoadData в начало кода FormatData .

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

. .

В предыдущих статьях вы видели, как Excel может управлять другими приложениями Microsoft, такими как Word и Outlook. Но одновременно с этим другие приложения также могут быть использованы для управления Excel. Например, вы написали макрос для Word или Access, который создает таблицу Excel, заполняет ее данными, а затем сохраняет ее. Excel не обязательно должен при этом […]

. .

Вы можете использовать эту технологию для управления Microsoft Outlook и отправлять электронные сообщения прямо из вашего листа таблицы или копировать записи вашей записной книги. Конечно же, особенности Excel таковы, что но электронной почте будет отправлена таблица, но этот метод позволяет вам отправлять только часть таблицы. Для того чтобы этот код работал, вам необходимо иметь установленный […]

. .

Этот метод может оказаться очень полезным, например, если у вас есть стандартный документ с таблицами, заполненными данными макросами из таблиц Excel. Вы можете запустить макрос, и данные будут перенесены в таблицы в документе Word. Недавно у меня была задача по написанию программы по заполнению отчета по SLA (Service Level Agreement — уровень сервисных соглашений). Отчет […]

. .

Для автоматизации работы приложений часто используются макросы. Любой макрос — это последовательность действий, записанная под определенным именем. Если при работе с Microsoft Excel возникает необходимость несколько раз выполнить одну и ту же последовательность операций (например, сложное форматирование текущей ячейки или добавление новой строки с заполнением некоторых ее ячеек формулами), то можно записать эти действия, а […]