Как использовать макросы в excel. Для чего нужны макросы в Excel? Ищем и используем команду
Статья предназначена для людей, которые хотят научиться писать программы на встроенном в Excel Visual Basic (VBA), но абсолютно не знают что это такое.
Для начала - несколько слов о том, зачем это нужно. Средство VBA в MS Excel, представляет нам универсальный инструмент для быстрого и точного решения любых индивидуальных пользовательских задач в MS Excel
. Можно конечно использовать и встроенные в MS Excel функции которых великое множество, однако они далеко не всегда решают поставленную задачу.
Итак, создадим для примера простейшую программу. Будем использовать MS Excel 2007. Откройте MS Excel, нажмите "сохранить как" и сохраните файл Вашей программы нажав "Книга ексель с поддержкой макросов".
Далее необходимо включить вкладку "Разработчик". Для этого нажимаем "Параметры Excel" 
Ставим галочку на "Показывать вкладку "Разработчик" на ленте"
После этого на ленте, в верху листа Excel, появится вкладка "Разработчик", которая содержит в себе инструменты для создания VBA макросов.
Представим себе небольшую задачу - допустим мы имеем 2 числа, нам необходимо их сложить и по полученной сумме получить значение из нашей таблицы.
Поставим в ячейки Листа1 следующие значения:
на Листе2 заполним ячейки, создав таблицу из 2 столбцов
Далее перейдем на Лист1, нажмем на вкладку "Разработчик", "Вставить", на ней выберем кнопку 
и нарисуем кнопку на Листе1, после чего сразу появится окно "Назначить макрос объекту", в котором выбираем "Создать" 
После этого откроется редактор Visual Basic, и автоматически напишется наименование процедуры, которая будет выполняться при нажатии кнопки. Под названием процедуры впишем следующий код:
Код выполнит следующие действия:
- MsgBox ("Это мой первый Макрос!") - сообщение
- Переменной q присваивается значение ячейки на Листе1, с координатами 2 строка, 2 столбец
- Переменной w присваивается значение ячейки на Листе1, с координатами 3 строка, 2 столбец
- В ячейку на Листе1, с координатами 4 строка, 2 столбец, записывается сумма q+w
Далее получим значение столбца В из Листа2, которое расположено на той же строке где значение нашей суммы совпадает с значением столбца А.
Введем следующий код:
и получим при нажатии на кнопку следующий результат:
из результата видно что макрос подобрал число из таблицы на Листе2 в соответствии с нашей суммой.
Не буду вдаваться в подробности этого хитрого кода, так как цель данной статьи - начать писать макросы. Для VBA в интернете есть масса ресурсов, с примерами и разъяснениями, хотя для автоматизации расчетов вполне хватит объема информации в справке.
Таким образом с помощью VBA возможно автоматизировать расчет любой сложности и последовательности. Справочные таблицы можно копировать из различной литературы на отдельные листы Excel и писать последовательный расчет с кнопками.
Сегодня я снова представляю вашему вниманию статью Чернякова Михаила Константинович , который расскажет о работе с макросами.
Поработав с документами Ехсеl 2010, вы можете обнаружить, что часто выполняете одни и те же задачи. Некоторые из них (такие как сохранение и закрытие файлов) выполняются достаточно бы-стро, другие включают последовательность шагов, что требует определенного времени и усилий. Вместо того чтобы каждый раз выполнять одни и те же действия вручную, можно создать МАКРО-СЫ , которые представляют собой программы, автоматизирующие выполнение заданной последовательности действий.
Прежде чем приступать к созданию макроса, нужно тщательно продумать процедуру, которую он должен автоматизировать. По-скольку современные компьютеры отличаются высоким быстро-действием, лишние шаги практически не влияют на результат, однако пропуск нужных шагов потребует перезаписи макроса. Спланировав все шаги, которые должен выполнить макрос, щелк-ните на стрелке кнопки Макросы в разделе Макросы на вкладке Вид За-пись макроса . Появится диалоговое окно
Создание макросов
1. Введите имя макроса, например, Таблица Умножения в предназначенное для этого поле.
2. Добавьте в сочетание клавиш букву «у».
3. В описание добавьте текст «Таблица умножения до 100 »
4. Щелк-ните на кнопке ОК .
5. Теперь можно выполнить действия, которые нужно записать в виде макроса, например, создайте таблицу умножения на 10.
6. В ячейку А2 введите 1, выделите эту ячейку и протяните на 9 ячеек вниз. При необходимости укажите в параметрах автозаполнения флажок Заполнить .
7. В ячейку В1 введите 1, выделите эту ячейку и протяните на 9 ячеек вправо. При необходимости укажите в параметрах автозаполнения флажок Заполнить .
8. Введите в ячейку В2 формулу: =$A2*B$1. Выделите ее и протяните сначала на 9 ячеек вниз, а затем выделенный столбец на 9 колонок вправо. Закончив, щелкните на стрелке кнопки Макросы на вкладке Вид Остановить запись
Аналогично можно создать и другие макросы, например, для очистки рабочего листа:
Удалить в разделе Ячейки на вкладке Вид , а затем щелкните на команде За-пись макроса .
2. Введите имя макроса, например, Очистка в предназначенное для этого поле и добавьте в сочетание клавиш букву «о ».
3. Выделите диапазон ячеек А1:К11 .
Макросы в разделе Макросы на вкладке Главная , а затем щелкните на команде Удалить ячейки - Удалить со сдвигом вверх .
5. Щелкните на стрелке кнопки Макросы на вкладке Вид , а затем щелк-ните на команде Остановить запись .
Запуск, изменение и удаление макроса
Щелк-ните на стрелке кнопки Макросы в разделе Макросы на вкладке Вид , а затем щелкните на команде Макросы . Появится диалоговое окно Макрос .
1. Чтобы запустить Макрос и щелкнуть на кнопке Выполнить.
2. Чтобы изменить существующий макрос, можно просто удалить его и записать снова.
3. Если же требуется незначительное измене-ние, можно открыть макрос в редакторе VBA и внести изменения в код макроса. Для этого достаточно выде-лить его в диалоговом окне Макрос и щелкнуть на кнопке Изменить.
4. Чтобы удалить макрос, достаточно выде-лить его в диалоговом окне Макрос и щелкнуть на кнопке Удалить.
Редактирование макросов
Редактирование макросов осуществляется средствами Microsoft Visual Basic for Applications (VBA ) . Редактор можно запустить командой Visual Basic в группе Код вкладки Разработчик или нажатием клавиш Alt + F 11 .
Добавление кнопок макросов на вкладки
Новый пользовательский интерфейс Ехсеl 2010 позволяет создавать и добавлять пользовательские вкладки, группы, а также команды, включая макросы.
1. Для этого необходимо правой кнопкой мыши вызвать контекстное меню любой вкладки, например, Разработчик , и выбрать команду Настройка ленты .
2. Выделить вкладку, например, Разработчик , и нажать кнопку Добавить группу .
3. Нажать кнопку Переименовать и ввести в поле Отображаемое имя название новой группы, например, Макросы ОК .
4. Из списка Выбрать команды щелкнуть Макросы .
5. Найти макрос ТаблицаУмножения и кнопкой Добавить Макросы.
6. Нажать кнопку Переименовать и ввести в поле Отображаемое имя ТаблицаУмножения , выбрать символ и нажать кнопку ОК .
7. Найти макрос Очистить и кнопкой Добавить включить его в созданную группу Макросы.
8. Нажать кнопку Переименовать и ввести в поле Отображаемое имя название новой команды, например, Очистить , выбрать символ и нажать кнопку ОК .
9. После нажатия кнопки ОК диалогового окна Параметры Excel на вкладке Разработчик появиться группа Макросы с командами Таблица умножения и Очистить .
Рис. 4. Добавление кнопок макросов на вкладку Разработчик
Добавление кнопки макросов на панель быстрого доступа
Новый пользовательский интерфейс Ехсеl 2010 позволяет быстро находить встроенные команды, однако потребуется несколько се-кунд, чтобы запустить макрос с помощью диалогового окна Мак-рос . В Ехсеl 2010 имеется несколько способов сде-лать макросы более доступными.
Можно упростить доступ к диалоговому окну Мак-рос , добавив кнопку Макросы на панель быстрого доступа.
1. Для этого правой кнопкой щелк-ните на стрелке кнопки Макросы в разделе Макросы на вкладке Вид .
2. В контекстном меню щелкните на команде Добавить на панель быстрого доступа.
3. На панели быстрого доступа появится кнопка Макросы , щелчок на которой открывает диалоговое окно Макрос .
Рис. 5. Добавление кнопки макросов на панель быстрого доступа
Если вы предпочитаете выполнять макрос, не открывая диало-говое окно Макрос , можно добавить кнопку, запускающую макрос, на панель быстрого доступа. Это особенно удобно, если мак-рос автоматизирует задачу, которая часто выполняется. Чтобы до-бавить кнопку для макроса на панель быстрого доступа:
1. Щелкните на кнопке Настройка напели быстрого доступа в конце панели быстрого доступа.
2. Щелкните на Другие команды , чтобы отобразить страницу Настройка диалогового окна Параметры Ехсе l .
3. Щелкните на стрелке поля Выбрать команды из .
5. Щелкните на макросе, для которого нужно создать кнопку.
6. Щелкните на кнопке Добавить .
7. Щелкните на кнопке ОК .
Другим способом добавления кнопки макроса Очистить на панель быстрого доступа является возможность ее установки с вкладки.
Щелкните на команде Очистить группы Макросы вкладки Разработчик правой кнопкой мыши и из контекстного меню выберите Добавить на панель быстрого доступа.
Создание объектов для выполнения макросов
Назначение макросов фигурам позволяет создавать «кнопки» более сложной формы, чем те, которые отображаются на панели быстрого доступа. При желании можно даже разрабо-тать собственные макеты кнопок для различных объектов. Чтобы назначить макрос фигуре, щелкните на ней правой кнопкой мыши, а затем щелкните на команде Назначить макрос в контекстном меню. В диалоговом окне, которое появится, щелкни-те на нужном макросе, а затем щелкните на кнопке ОК .
При назначении макросов фигурам не следует из-менять имя макроса, которое отображается в диалоговом окне Назна-чить макрос объекту , поскольку оно содержит ссылку на объект. Изменение имени макроса разрывает эту связь и препятству-ет выполнению макроса.
Для запуска макроса ТаблицаУмножения можно создать фигуру в виде прямоугольника, а для Очистить - в виде элипса:
1. Щелк-ните на стрелке кнопки Фигуры в разделе Иллюстрации на вкладке Вставка , а затем щелкните на команде Скругленный прямоугольник .
2. Впишите фигуру Скругленный прямоугольник в диапазон ячеек М2:Р4 . Введите текст «Таблица умножения ». Установите размер шрифта введенного текста 18.
3. Щелкните на прямоугольнике правой кнопкой мыши, а затем щелкните на команде Назначить макрос ТаблицаУмножения , а затем щелкните на кнопке ОК .
4. Щелк-ните на стрелке кнопки Фигуры в разделе Иллюстрации на вкладке Вставка , а затем щелкните на команде Овал .
5. Впишите фигуру Овал в диапазон ячеек М7:Р10 . Введите текст «Очистка ». Установите размер шрифта введенного текста 18. Выровняйте надпись по центру. Измените цвет заливки фигуры на красный.
6. Щелкните на овале правой кнопкой мыши, а затем щелкните на команде Назначить макрос в контекстном меню. В диалоговом окне, которое появится, щелкни-те на макросе Очистить , а затем щелкните на кнопке ОК .
Теперь для запуска макроса достаточно щелчка по фигуре. Щелкните по фигуре Овал и с листа исчезнет таблица. Щелкните по фигуре Скругленный прямоугольник и таблица умножения вновь появиться на листе.
1. Макросы целесообразно создавать для рутинных многократно повторяющихся действий. Для создания макросов не требуется знания языков программирования.
2. Макросы можно запускать, изменять, редактировать и удалять по мере необходимости. Для редактирования макросов необходимы знания алгоритмического языка Basic, оптимально Visual Basic for Applications. Последний позволяет управлять диалоговыми окнами и решать нестандартные задачи.
3. Кнопки запуска макросов можно располагать на вкладках, панели быстрого запуска или фигурах любых типов.
В Excel и Word макросы отлично помогают, когда надо срочно посмотреть конкретный кусок объектной модели, а под рукой нет интернета, например. Правда, чаще всего придется внимательно почистить полученный код от лишних "следов" перемещения по ячейкам и других необязательных действий. Но это того стоит. Запись и просмотр макроса займет минуты 2. А вот поиск некоторых видов информации (особенно, связанной с графиками) на MSDN в контексте объектной модели может длиться часами и не факт, что окажется результативным.
Ну, а для пользователей, не знакомых с VBA, макросы – вещь безусловно незаменимая и полезная.
Чтобы произвести впечатление на публику, и выставить себя в роли профессионалов, люди, готовы использовать макросы в таблицах, созданных в такой программе, как Microsoft Excel.
Из-за того, что человек будет использовать макросы, имеется вероятность, что каждая ячейка будет автоматизирована, и не будет возникать особых затруднений. В момент создания самой таблицы и подключения макроса будут записываться необходимые значения.
Что собой представляют макросы, и для чего они создаются?
Макросы подключить можно в разнообразных программах, от компании Microsoft. Они прекрасно помогают улучшить работы обычного пользователя в любом из утилит. Теперь гражданину не нужно будет выполнять однотипные действия бесконечное число раз, всё существенно изменится, и произойдет это из-за того, что весь процесс будет автоматизирован, и время будет сэкономлено.
Создав макрос, можно быстро выполнить необходимую работу, прекрасно понимая, что в тоже время, ручным способом, это всё создавалось намного тяжелее и дольше. Можно сказать, что макрос состоит из набора инструкций, и они позволяют утилите выполнять заложенные функции.
Помимо утилиты, которая предназначена для создания таблиц, многие люди сталкиваются с понятием макроса в Worde, набирая обычный текст. А всё из-за того, что макрос имеет большое число преимуществ.
Все процедуры и инструкции объединяются в один сценарий, и он необходим для оптимизации работы. Вызвать процедуру можно из панели инструментов, используя специальные комбинации клавиш.
Создание собственных макросов в различных версиях Excel.
Во-первых, необходимо открыть документ, и выбрать те ячейки, которые уже были проработаны. Во-вторых, нужно отобразить в ленте «Вкладку разработчика». Чтобы это сделать, необходимо открыть «Файл», выбрать «Параметры», и выставить таки значения, которые имеются на изображении.
После активации, необходимо это окно разработчика сделать главным, для этого необходимо выставить значения, как показано на изображении.
Теперь имеется возможность перейти к непосредственному созданию макроса. Нужно чётко понимать, что согласившись на его создание, вы согласитесь с тем, что любая из ячеек, указанных в диапазоне, будет выполнять автоматические, однотипные действия, которые задаст сам человек.
Чтобы создать макрос, необходимо во вкладке «Окно разработчика» выбрать соответствующую кнопку, и нажать на неё.
После того, как вы нажали на кнопку, перед вами появится специальное окно, в нём нужно будет указать название, а также комбинацию клавиш, которые будут её приводить в активное действие. Помимо всего этого, есть возможность написать короткий клочок текста, это необходимо лишь для того, что выделить макрос из всех остальных. Это активно при условии, что у вас имеется много макросов, и с ними вы не хотите запутаться.
После этого нажимаем на клавишу OK, и останавливаем полученную запись на панели управления.
После того, как действия будут записаны в созданный макрос, нужно не забывать о том, что имеется возможность записать всё в макрос. Чтобы выполнить эту манипуляцию, необходимо нажать на кнопку «Остановить макрос».
Как включить работу с макросами?
Сначала переходим на вкладку разработчика, и там выбираем меню «Макросы», сразу нажимаем на неё.
Выбираем необходимый макрос и нажимаем на такую кнопку, как «Выполнить».
Запуск макроса осуществляется с помощью комбинации необходимых клавиш. После того, как вы нажали на кнопку выполнить, все действия пройдут повторно.
Создание макросов и их удаление
Макросы, в нынешних условиях создаются с помощью языка программирования, а именно помощью Visual Basic. Но, всё настолько элементарно, что разобраться может даже заядлый новичок.
Сейчас любой макрос состоит из макрооператоров. Можно сказать, что макрооператоры – набор сведений и действий, необходимых для автоматизации всего процесса. Используя макросы, можно с уверенностью сказать, процесс будет проходить на полном автомате, и вмешательство не будет требоваться ос стороны человека.
Чтобы самостоятельно создать макрос, необходимо воспользоваться соответствующей функцией. Используя записывающий макрос, человек, делает всё возможное, чтобы даже при смене информации в ячейке, ничего не сбивалось, а оставалось на высоком уровне.
Макрос необходим для повторения действий, и сейчас многие граждане делают всё возможное, чтобы повторять действия, не используя лишних средств.
Удалить макрос не составляет никаких ситуаций. Откроется меню разработчика, и выберете там вкладку «Макросы». Дальше выбираем объект, который должен подвергнуться удалению, и просто начинаем незамедлительно сам процесс.
После удаления, таблица данных опять будет напоминать школьную программу.
Когда все готово, приступаем к записи.
Запуск макроса
Для проверки того, как работает записанный макрос, выполним несколько простых действий.
Редактирование макроса
Естественно, при желании вы можете корректировать созданный макрос, чтобы всегда поддерживать его в актуальном состоянии и исправлять некоторые неточности, допущенные во время процесса записи.
- Снова щелкаем на кнопку «Макросы» . В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить» .
- Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.
- Запись каждого макроса начинается с команды Sub , а заканчивается командой End Sub . Сразу же после Sub указывается имя макроса. Оператор Range("…").Select указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4» . Оператор ActiveCell.FormulaR1C1 используется для записи действий в формулах и других расчетов.
- Попытаемся немного изменить макрос, дописав выражение:
Range("C3").Select
ActiveCell.FormulaR1C1 = "11" - Выражение ActiveCell.FormulaR1C1 = "=R[-3]C+R[-2]C+R[-1]C" заменим на ActiveCell.FormulaR1C1 = "= R[-4]C+R[-3]C+R[-2]C+R[-1]C" .
- Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
- В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду Application.ScreenUpdating = False . Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду Application.ScreenUpdating = True .
- Добавим также команду Application.Calculation = xlCalculationManual в начало кода, а в его конец дописываем Application.Calculation = xlCalculationAutomatic . Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.
Вариант 2: Написание кода макроса с нуля
Продвинутые пользователи могут выполнять не только редактирование и оптимизацию записанных макросов, но и записывать их код с нуля.
Как видим, макросы в Microsoft Excel могут значительно упростить выполнение рутинных и однообразных процессов. Тем не менее в большинстве случаев для этого больше подходят макросы, код которых написан вручную, а не автоматически записанные действия. Кроме того, его код можно оптимизировать через редактор VBE для ускорения процесса выполнения задачи.
Также как и в других приложениях Microsoft Office , есть такая интересная штука как макрос. Если в двух словах, то макрос – это программируемая последовательность действий. Создать его можно двумя способами:
1. С помощью соответствующего пункта меню;
2. Вручную.
Первый способ очень простой и не требует специальных навыков программирования. Но прежде чем приступать к созданию макроса, необходимо поковырять настройки безопасности. Заходим в Файл/Параметры/Центр управления безопасностью/Параметры центра управления безопасностью/Параметры макросов . Здесь необходимо выбрать Включить все макросы .
Теперь все готово, и мы переходим непосредственно к созданию макроса. Открываем вкладку Разработчик и нажимаем .
Появится окно, в котором нужно указать имя макроса, назначить клавишу для быстрого запуска, а также выбрать книгу, в которую необходимо сохранить макрос.
После нажатия кнопки ОК начинается запись макроса. Все что вы будете делать, будет записываться. К примеру, можно создать таблицу и отредактировать ее – задать нужное количество строк, столбцов, их ширину. Затем, чтобы остановить запись, нужно нажать соответствующую кнопку.
Теперь, чтобы запустить наш макрос, нужно на вкладке Разработчик нажать Макросы .
В появившемся окне находим созданный нами макрос и нажимаем Выполнить . Также можно использовать комбинацию клавиш, которую мы вводили при создании макроса.
Преимуществ у данного способа создания макросов не очень много. В большинстве случаев такой макрос можно применить только в текущей книге и для какой-то конкретной ситуации. Гораздо эффективнее использовать макрос, написанный при помощи языка программирования Visual Basic . Однако создание такого макроса требует определенных навыков. Пример создания макроса на VBA рассмотрен .
