Как открыть макрос в excel для редактирования

Обновлено: 03.07.2024

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

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

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

Что такое макросы в Excel

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

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

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

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

Включите вкладку разработчика, чтобы создать макрос

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

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

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

Как создать собственные макросы в Excel

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

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

Настройте макросы в Excel, чтобы они работали лучше

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

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

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

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

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

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

Что такое макрос и для чего он применяется

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

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

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

Инструкции, которые передаются макросу, содержат макрооператоры – конкретные команды, которые офисному пакету нужно выполнить. Ряд из них являются специфическими, но большая часть соответствует командам из главного меню. Это делается для того, чтобы сделать процесс создания макросов более дружелюбным для пользователя.

Давайте приведем один пример. Любое приложение, независимо от используемой операционной системы, содержит кнопку «Закрыть». Если мы хотим сделать это через макрос, нам нужно ввести в среду разработки команду ActiveWindow.Close. Видим, что по названиям операторов можно много чего понять, поскольку все они пишутся на английском языке. Это также упрощает задачу пользователя, который знает английский язык.

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

Как создать новый макрос в Excel

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

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

Способ 1. Автоматический запуск макроса

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

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

После этого мы увидим вкладку «Разработчик» в нашей программе. Делаем переход по ней. Перед нами появляется большое количество инструментов разработчика. Из всего этого ассортимента нас интересует группа «Код». Там находится маленькая кнопка «Записать макрос», после клика по которой осуществляется непосредственная запись этой программы.

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

Имя макроса. Допустимо использование какого-угодно имени за исключением того, которое начинается с числа или символа. При этом нельзя в качестве имени использовать больше одного слова.
Комбинация клавиш. Это некий пусковой крючок для макроса. Он используется для того, чтобы включить макрос. Все горячие клавиши начинаются с клавиши Ctrl, а вторую можно настроить самостоятельно. Например, можно использовать горячую клавишу r. Также возможен ввод большой буквы. Для этого необходимо воспользоваться клавишей Shift, которая автоматически добавляется в комбинацию горячих клавиш.
Место хранения. По умолчанию предусмотрен вариант, когда макрос применяется к текущей книге. Тем не менее, пользователь может выбрать новую или создать собственную книгу для макросов.
Описание. При желании пользователь может в нескольких предложениях описать, что будет делать макрос. Это будет особенно полезно, если одной и той же книгой пользуются несколько человек.

После выполнения всех действий нужно нажать клавишу ОК, тем самым подтвердив изменения. Теперь все действия, совершаемые пользователем, будут автоматически записываться в макрос. Это будет продолжаться ровно до тех пор, пока запись программы не будет остановлена. Значительно проще разобрать это на реальном примере. Давайте сделаем программку, которая будет умножать содержимое двух ячеек. Для этого нужно запустить запись макроса, ввести в ячейку формулу (в нашем случае это =B2*B3), нажать клавишу Enter, после чего остановить запись. Кнопка остановки записи макроса находится на вкладке «Разработчик» в группе «Код».

Запуск выполнения макроса

Как же проверить, работает ли наш макрос? Для этого нужно выполнить следующие действия:

Открыть вкладку «Разработчик». Там в самой левой части есть кнопка «Макросы», по которой нужно сделать левый клик мышью один раз. Кроме этого, пользователь может открыть аналогичную опцию путем нажатия горячих клавиш Alt + F8.
После этого у нас откроется окно настройки макросов. Нужно выбрать тот, который мы только что создали, и нажать на кнопку «Выполнить». Этот же результат можно получить, если нажать сочетание клавиш, которое мы назначили для макроса раньше.
О том, что макрос работает, мы можем судить по действиям программы. Если они повторяют те, которые были сделаны раньше, значит все ОК.

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

Корректировка макроса

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

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

Далее следуют команды, которые выполняют конкретные действия. Например, номер ячейки мы получаем с помощью оператора Range. В нашем примере, мы получаем ячейку B2 с помощью команды Range(“B2″).Select. Это действие аналогично выбору ячейки левой кнопкой мышки. Значение ячейки, которая выбрана на данный момент указывается с помощью оператора ActiveCell.FormulaR1C1.

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

Внимание! Видим, что адреса в ячейках в данном примере записываются, как R1C1.

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

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

Также можно ускорить работу макроса путем добавления команды Application.ScreenUpdating = False. После этого на экране не будут показываться те промежуточные шаги, которые выполняет макрос, лишь конечный результат. Если же нужно, чтобы отображение на экране было вновь включено, тогда нужно заменить False на True.

Способ 2. Создание макроса в ручном режиме

Автоматическая запись макроса – это хорошо, но данный метод имеет множество недостатков. Профессионалы создают макросы вручную. Для этого нужно выучить язык VBA и выполнять следующие действия:

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

Как изменять существующие макросы

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

Заключение

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

Создание макросов

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

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

Метод 1: Записываем макрос в автоматическом режиме

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

Узнать, как это можно сделать, можно в нашей статье – “Как включать и отключать макросы в Excel”.

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

Переключаемся во вкладку “Разработчик”. В группе инструментов “Код” щелкаем по значку “Записать макрос”.
На экране появится окошко, в котором мы можем выполнить настройки записи. Здесь указывается:
имя макроса – любое, но должно начинаться с буквы (не с цифры), не допускаются пробелы.
комбинация клавиш, которая будет использоваться для запуска макроса. Обязательной является Ctrl, а вторую мы можем назначить в специальном поле. Допустим, пусть это будет клавиша “r”. Если нужно использовать заглавную букву, нужно зажать клавишу Shift, которая будет добавлена в сочетание клавиш.

Запускаем выполнение макроса

Чтобы проверить работу записанного макроса, нужно сделать следующее:

Корректируем макрос

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

Метод 2: создание макроса вручную

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

Заключение

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

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

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

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

:

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

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

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Способ 2. Запись макросов макрорекордером

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

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов

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

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :

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

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

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Макрос - это код, написанный на встроенном в Excel языке VBA (Visual Basic for Application). Макросы могут создаваться как вручную, так и записываться автоматически с помощью так называемого макрорекодера.

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

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

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

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

Перед тем как записывать макрос, нужно добавить на ленту меню Excel вкладку "Разработчик". Для этого выполните следующие шаги:
1. Щелкните правой кнопкой мыши по любой из существующих вкладок на ленте и нажмите «Настроить ленту». Он откроет диалоговое окно «Параметры Excel».
2. В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
3. Нажмите «ОК».
В результате на ленте меню появится вкладка "Разработчик"

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

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

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

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

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

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

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

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

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

Вот шаги по открытию редактора VB в Excel:
1. Перейдите на вкладку "Разработчик".
2. В группе "Код" нажмите кнопку "Visual Basic".
Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.
- Панель меню: содержит команды, которые можно использовать во время работы с редактором VB.
- Панель инструментов - похожа на панель быстрого доступа в Excel. Вы можете добавить к ней дополнительные инструменты, которыми часто пользуетесь.
- Окно проектов (Project Explorer) - здесь Excel перечисляет все книги и все объекты в каждой книге. Например, если у нас есть книга с 3 рабочими листами, она появится в Project Explorer. Здесь есть несколько дополнительных объектов, таких как модули, пользовательские формы и модули классов.
- Окно кода - собственно сам код VBA размещается в этом окне. Для каждого объекта, указанного в проводнике проекта, есть окно кода, например, рабочие листы, книги, модули и т. д. В этом уроке мы увидим, что записанный макрос находится в окне кода модуля.
- Окно свойств - вы можете увидеть свойства каждого объекта в этом окне. Я часто использую это окно для обозначения объектов или изменения их свойств.
- Immediate Window (окно предпросмотра) - На начальном этапе оно вам не пригодится. Оно полезно, когда вы хотите протестировать шаги или во время отладки. Он по умолчанию не отображается, и вы можете его отобразить, щелкнув вкладку «View» и выбрав опцию «Immediate Window».
Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:
- Был добавлен новый модуль.
- Макрос был записан с именем, которое мы указали - "ВводТекста"
- В окне кода добавлена новая процедура.
Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

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

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

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub - сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.
- Range("A2").Select - эта строка выбирает ячейку A2.
- ActiveCell.FormulaR1C1 = «Excel» - эта строка вводит текст "Excel" в активной ячейке. Поскольку мы выбрали ячейку A2 в качестве первого шага, она становится нашей активной ячейкой.
- Range("A3").Select - выбор ячейки A3. Это происходит, когда мы нажимаем клавишу Enter после ввода текста, результатом которого является выбор ячейки A3.
Надеюсь, что у вас есть некоторое базовое понимание того, как записывать макрос в Excel.

Обращаем внимание, что код, записанный через макрорекордер, как правило, не является эффективным и оптимизированным кодом. Макрорекордер часто добавляет дополнительные ненужные действия. Но это не значит, что не нужно пользоваться макрорекодером. Для тех, кто только изучает VBA , макрорекордер может быть отличным способом проанализировать и понять как все работает в VBA.

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

Вы уже знаете про абсолютные и относительные ссылки в Excel? Если вы используете абсолютную ссылку для записи макроса, код VBA всегда будет ссылаться на те же ячейки, которые вы использовали. Например, если вы выберете ячейку A2 и введете текст "Excel", то каждый раз - независимо от того, где вы находитесь на листе и независимо от того, какая ячейка выбрана, ваш код будет вводить текст "Excel" в ячейку A2.

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

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

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

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

Как вы заметите, макрос записал текст "Excel" не в ячейки A2. Это произошло, потому что вы записали макрос в режиме относительной ссылки. Таким образом, курсор перемещается относительно активной ячейки. Например, если вы сделаете это, когда выбрана ячейка B3, она войдет в текст Excel - ячейка B4 и в конечном итоге выберет ячейку B5.

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

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

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

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

Макро-рекордер отлично подходит для вас в Excel и записывает ваши точные шаги, но может вам не подойти, когда вам нужно сделать что-то большее.
- Вы не можете выполнить код без выбора объекта. Например, если вы хотите, чтобы макрос перешел на следующий рабочий лист и выделил все заполненные ячейки в столбце A, не выходя из текущей рабочей таблицы, макрорекодер не сможет этого сделать. В таких случаях вам нужно вручную редактировать код.
- Вы не можете создать пользовательскую функцию с помощью макрорекордера. С помощью VBA вы можете создавать пользовательские функции, которые можно использовать на рабочем листе в качестве обычных функций.
- Вы не можете создавать циклы с помощью макрорекордера. Но можете записать одно действие, а цикл добавить вручную в редакторе кода.
- Вы не можете анализировать условия: вы можете проверить условия в коде с помощью макрорекордера. Если вы пишете код VBA вручную, вы можете использовать операторы IF Then Else для анализа условия и запуска кода, если true (или другой код, если false).
Расширение файлов Excel, которые содержат макросы

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

До Excel 2007 был достаточен один формат файла - .xls. Но с 2007 года .xlsx был представлен как стандартное расширение файла. Файлы, сохраненные как .xlsx, не могут содержать в себе макрос. Поэтому, если у вас есть файл с расширением .xlsx, и вы записываете / записываете макрос и сохраняете его, он будет предупреждать вас о сохранении его в формате с поддержкой макросов и покажет вам следующее диалоговое окно:

Если вы выберете "Нет", Excel сохранить файл в формате с поддержкой макросов. Но если вы нажмете "Да", Excel автоматически удалит весь код из вашей книги и сохранит файл как книгу в формате .xlsx. Поэтому, если в вашей книге есть макрос, вам нужно сохранить его в формате .xlsm, чтобы сохранить этот макрос.

Читайте также: