Язык для написания макросов

Как записать макрос в Excel? Пошаговая инструкция.

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

Для начала немного о терминологии.

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

Теперь давайте погрузимся и посмотрим, как записать макрос в Excel.

Отображение вкладки «Разработчик» в ленте меню

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку «Разработчик». Для этого выполните следующие шаги:

В результате на ленте меню появится вкладка «Разработчик»

Запись макроса в Excel

Теперь давайте запишем очень простой макрос, который выбирает ячейку и вводит в нее текст, например «Excel».

Вот шаги для записи такого макроса:

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

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

Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «Excel» будет вставлен в ячейку A2 и выбрана ячейка A3. Это происходит за миллисекунды. Но на самом деле макрос последовательно выполнил записанные действия.

Примечание. Вы также можете запустить макрос с помощью сочетания клавиш Ctrl + Shift + N (удерживайте клавиши Ctrl и Shift, а затем нажмите клавишу N). Это тот же самый ярлык, который мы назначили макросу при его записи.

Что записывает макрос?

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

Вот шаги по открытию редактора VB в Excel:

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

Когда мы записали макрос «ВводТекста», в редакторе VB произошли следующие вещи:

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

Вот код, который записан макрорекодером:

Теперь давайте пробежим по каждой строке кода и опишем что и зачем.

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

Абсолютная и относительная запись макроса

Если вы используете параметр относительной ссылки для записи макроса, VBA не будет привязываться к конкретному адресу ячейки. В этом случае программа будет «двигаться» относительно активной ячейки. Например, предположим, что вы уже выбрали ячейку A1, и вы начинаете запись макроса в режиме относительной ссылки. Теперь вы выбираете ячейку A2, вводите текст Excel и нажмите клавишу Enter. Теперь, если вы запустите этот макрос, он не вернется в ячейку A2, вместо этого он будет перемещаться относительно активной ячейки. Например, если выбрана ячейка B3, она переместится на B4, запишет текст «Excel» и затем перейдет к ячейке K5.

Теперь давайте запишем макрос в режиме относительных ссылок:

Макрос в режиме относительных ссылок будет сохранен.

Теперь сделайте следующее.

Вот код, который записал макрорекодер:

Обратите внимание, что в коде нет ссылок на ячейки B3 или B4. Макрос использует Activecell для ссылки на текущую ячейку и смещение относительно этой ячейки.

Не обращайте внимание на часть кода Range(«A1»). Это один из тех случаев, когда макрорекодер добавляет ненужный код, который не имеет никакой цели и может быть удален. Без него код будет работать отлично.

Что нельзя сделать с помощью макрорекодера?

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

Расширение файлов Excel, которые содержат макросы

Когда вы записываете макрос или вручную записываете код VBA в Excel, вам необходимо сохранить файл с расширением файла с поддержкой макросов (.xlsm).

Источник

Краткое руководство: создание макроса

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

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

Процедура

Перед записью макроса

Макросы и средства VBA находятся на вкладке Разработчик, которая по умолчанию скрыта, поэтому сначала нужно включить ее. Дополнительные сведения см. в статье Отображение вкладки «Разработчик».

В группе код на вкладке разработчик нажмите кнопку запись макроса.

Выполните действия, которые вы хотите автоматизировать (например, введите Стандартный текст или заполните столбец данных).

На вкладке Разработчик щелкните Остановить запись.

Подробное рассмотрение макроса

Чтобы ознакомиться с языком программирования Visual Basic, можно изменить макрос.

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

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

Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, что происходит, если что-то не так.

Дальнейшие действия

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

Сведения о том, как выполнить макрос, можно найти в разделе Запуск макроса.

Процедура

Перед записью макроса

В категории Настроить ленту в списке Основные вкладки установите флажок Разработчик, а затем нажмите кнопку Сохранить.

На вкладке Разработчик нажмите кнопку Запись макроса.

Выполните действия, которые вы хотите автоматизировать (например, введите Стандартный текст или заполните столбец данных).

На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

Подробное рассмотрение макроса

Чтобы ознакомиться с языком программирования Visual Basic, можно изменить макрос.

Чтобы изменить макрос, на вкладке разработчик нажмите кнопку макросы, выберите имя макроса, а затем нажмите кнопку изменить. Откроется редактор Visual Basic.

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

Поэкспериментируйте с кодом, закройте редактор Visual Basic и снова запустите макрос. На этот раз посмотрите, что происходит, если что-то не так.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник

Что такое макросы?

Всем привет, меня зовут Андрей!

В данной статье мне бы хотелось рассказать про макросы. Что такое макрос?

Макрос – это прежде всего компьютерный код, или программа, которая написана на языке VBA – Visual Basic for Applications. Макрос – одна из основных частей не только Excel, но и других продуктов MS Office. Потому язык VBA так и назван – ведь «for applications» так и переводится – «для приложений». А такие программы, как Word или Excel – это и есть те самые приложения.

Для тех, кто хоть немного знаком с английским языком или с любой версией языка программирования бейсик, работа с макросами не составит труда. Основная цель макросов – упростить работу с Excel. Как я уже говорил, и макросы, и язык VBA существуют не только при Excel – они есть при многих продуктах MS Office. Но раз мой блог – об Excel, то я буду рассказывать именно об Excel. И о тех макросах, которые помогут в работе с Excel.

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

Итак, мы видим следующее: в столбце A – значения углов (от 0 до 1 градуса, с шагом 0,1); в столбце B – синусы этих углов, в столбце C – косинусы, в столбце D – тангенсы.

Пока строк в таблице мало, заполнять эту таблицу не сложно. Сначала заполняем «шапку» (заголовки столбцов), затем в ячейку A2 вводим нуль. Потом в ячейку B2 вводим следующую формулу:

Аналогично вводятся формулы для других столбцов второй строки. Например, формула для C2:

Почему именно «радианы»? Потому, что в столбце A у нас приводятся градусы в углах, а все тригонометрические функции без функции «радианы» предполагают, что наши исходные цифры находятся не в углах, а в радианах. Эта дополнительная функция – просто для преобразования градусов в радианы.

Теперь заполним следующую строку таблицы – ту, в которой находится угол в 0,1 градуса и все его тригонометрические функции. Со столбцами от B до D все понятно и просто – достаточно просто скопировать те формулы, что были раньше в тех же столбцах (строка 2). При небольшом количестве строк такой вариант вполне допустим. Поскольку все эти формулы содержат относительные ссылки (что это такое – мы подробно рассматривали в прошлой статье моего канала), то при копировании этих формул на другие строки изменится и сами формулы, и их значения, но не изменится главное: столбцы всегда будут показывать основные тригонометрические функции углов, причем и угол, и его основные функции расположены в одной строке, в той же последовательности, что и в заголовке, или «шапке» таблицы. Эта закономерность будет справедлива для любой строки таблицы. Пока строк мало – все легко и просто.

С тригонометрическими функциями все понятно. Но что касается остальных ячеек таблицы – то есть столбца A (ячейка A3) – там нам нужна такая формула:

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

Теперь видно, почему мы обратили особое внимание на третью строчку. Эта таблица уже готова к дальнейшему заполнению. Как это понимать? Если мы выделим все ячейки третьей строки (от A3 до D3 включительно), скопируем данные этих ячеек, а затем выделим ту область, в которую нам необходимо вставить эти данные и выполним команду «Вставить», то мы получим продолжение заполнения данной таблицы. Если мы выделим только ячейку A4 и выполним команду «вставить» – то заполнится все 4 ячейки – от A4 до D4 включительно. Это правило работает всегда: если мы скопировали несколько ячеек и нам даже не известно, сколько именно ячеек в скопированной области, нам при вставке достаточно выбрать левую верхнюю из тех ячеек, в которых должна будет находиться скопированная область.

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

И вот тогда на помощь придут. макросы! Оказывается, длинную таблицу можно заполнить не только с помощью тандема операций «копировать-вставить». Можно воспользоваться макросами. Конечно же, некоторые макросы могут выполнять, кроме прочих функций, функции копирования и вставки, но при больших объемах информации такое использования макросов, хотя и возможно, но не желательно и даже иногда может привести к «зависанию» программы.

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

Для начала найдем вкладку «Разработчик». Если этой вкладки на нашей ленте нет, ее можно добавить с помощью меню Файл→Параметры→Настройка ленты→разработчик.

Пока нас интересует только левая часть этой вкладки. Кнопка «Visual Basic» позволяет переходить в редактор Visual Basic; кнопка «Макросы» предполагает выполнение различных действий с макросами:

Почему в данной ситуации кнопка «Создать» не активна? Все очень просто: в графе «Имя макроса» есть имя уже существующего макроса (это имя Макрос1), а два макроса с одним именем не могут существовать. Не созданный макрос можно только создать, а с уже созданным макросом можно выполнять различные действия.

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

Итак, вернемся к нашей тригонометрической таблице.

Допустим, что нам нужно заполнить около 3600 строк. Начнем заполнение со столбца A.

Напишем простой макрос. Назовем его Макрос1:

Если мы выполним этот макрос, то увидим следующее:

— во-первых, столбец A будет заполнен числами от 0 до 360 с шагом 0,1;

— во-вторых, столбец A будет заполнен достаточно быстро – всего за несколько секунд;

— в-третьих, столбец A будет состоять не из формул, а из значений. Например, если раньше мы вводили в ячейку A3 формулу «=A2+1», и эта формула всегда была видна при наведении на эту ячейку, то теперь – мы видим только конкретные результаты. Кстати, чем больше формул и чем меньше констант, например текстовых или числовых, тем лучше для файла Excel – ведь наличие многих формул предполагает постоянный пересчет тех ячеек, которые содержат эти формулы. Чем меньше формул, тем быстрее работает Excel.

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

Вот как будет выглядеть весь макрос «тригонометрические функции» (назовем этот макрос «Функции»):

А теперь приведем еще один пример. И снова начнем без макросов. Предположим, что нам нужно создать таблицу умножения. Но не простую, а большую – 100 х 100.

Но вначале – небольшое отступление от основной темы. Здесь самое время рассказать про закрепление областей. Что это такое и в каких случаях оно бывает достаточно удобным?

Если у нас есть большая таблица. в которой много строк и (или) много столбцов, то закрепление областей позволит существенно улучшить работу с этой таблицей.

Итак, заполняем таблицу умножения 100 х 100.

1. В ячейку B1 вводим единицу (цифру 1), в ячейку C1 – формулу:

2. Копируем формулу из ячейки C1 в диапазон ячеек D1-CW1, получим нужный нам результат – то есть числа от 1 до 100 включительно в первой строке Excel, в ячейках от B1 до CW1 включительно.

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

В таблице – фрагмент файла Excel. Как быстро заполнить цифры в первой строке от единицы до ста?

— сначала заполняем первые несколько чисел (в ячейку B1 – единицу. в ячейку C1 – нужную нам формулу);

— затем переходим к крайней правой ячейке того диапазона, в который мы должны ввести нужную нам формулу. В нашем случае это ячейка CW1. Если ввести в поле Имя «CW1», то активной станет именно ячейка CW1.

Кстати, о поле Имя. Оно находится чуть выше ячейки A1:

Но продолжим заполнять таблицу умножения.

3. Аналогично заполняем заголовок строк: сначала в ячейку A2 вводим единицу, затем в ячейку A3 вводим формулу:

4. Затем нужно скопировать эту формулу на весь диапазон ячеек – от A4 до A101 включительно. Как мы уже говорили, при закрепленных областях это будет сделать достаточно просто.

Кстати, мы уже говорили, что есть несколько вариантов заполнения «шапки» таблицы умножения, то есть заголовков строк и столбцов.

Рассмотрим второй вариант.

1. В ячейку B1 вводим единицу, в ячейку B2 – двойку.

2. Выделим мышкой ячейки B1 и B2, а курсор – след от мыши – аккуратно разместим в нижний правый угол выделенных ячеек таким образом, чтобы курсор изменил свой вид до своеобразного крестика – как на рисунке:

Кстати – это интересно: если нажать кнопку Prt Scr – Print Screen – то скопируется всё, кроме. курсора, следа от «мышки». Как этого избежать? Все достаточно просто.

1. Устанавливаем программу, которая позволяет писать видео с компьютера (записывать все то, что делается на компьютере). Одна из доступных и бесплатных программ – OBS.

2. Начинаем запись на видео всего того, что делается на компьютере.

3. Создаем на компьютере именно ту картинку, включая след от «мышки», которую мы в конце концов должны получить в виде графического файла.

4. Останавливаем запись видео.

5. С помощью программ просмотра видео запускаем тот файл, который нам нужен (только что записанное видео с компьютера), и находим в нем нужную нам картинку.

6. Ставим видео на паузу в нужном нам месте.

7. Нажимаем кнопку Prt Scr. Очевидно, что тот след от мыши, который был на видео, останется в оперативной памяти компьютера. И когда мы войдем в графический редактор и выполним команду «Вставить», то получится вставка рисунка экрана вместе с тем следом курсора мыши, который нас интересует.

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

Кстати, заголовки строк можно заполнить аналогичным образом – сперва заполняем единицу и двойку (ячейки A2 и A3), затем выделяем две ячейки с единицей и двойкой, а потом перемещаем след от мыши в правый нижний угол выделенных ячеек. И, не отпуская левой кнопки мыши, переводим курсор вниз.

Есть и третий способ заполнения «шапки».

В ячейку B1 вводим формулу:

и тогда уже после этого, подведя курсор к правой нижней части ячейки B1, можно проделывать то, что мы проделывали ранее, после заполнения ячейки B2.

Конечно же, для «шапки» строк будет такая формула:

Допустим, «шапку», то есть заголовки строк и столбцов нашей таблицы умножения, мы уже заполнили. Теперь останется самое главное – заполнить саму таблицу. Как будем заполнять? Здесь есть тоже несколько вариантов.

Первый вариант – самый простой. С помощью одной формулы.

Если в ячейку B2 – это левая верхняя ячейка того диапазона, где нам нужна эта формула, ввести формулу:

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

А теперь перейдем ко второму способу заполнения нашей таблицы умножения.

Если до этого мы сохраняли наш файл – то его нужно сохранить под другим названием. Главное – под другим расширением. Нас интересует расширение «Книга Excel с поддержкой макросов».

Кстати, еще одна интересная «фишечка». Если нажать только одну кнопку «Print Screen», то скопируется весь экран – кроме курсора от «мышки». Но если нас интересует не весь экран, а какая-то одна экранная форма, как это и представлено в нашем случае, то нужно нажать одновременно с этой кнопкой еще и Alt. Тогда другая информация с экрана не будет загружена в оперативную память.

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

Если мы запустим этот макрос, то увидим, что на заполнение таблицы умножения размеро 100 на 100 уйдет всего несколько секунд. Чтобы избавиться от формул и заменить их константами, можно выделить весь лись Excel, скопировать его, и тут же все вставить с помощью специальной вставки, задействовав переключатель «Значения».

А на этом пока всё, в новых статьях я буду рассказывать ещё больше интересного о работе с электронными таблицами Excel.

Источник

Поделиться с друзьями
admin
Оцените автора
( Пока оценок нет )
Как переводится?
Adblock
detector