Как в excel написать программу: Как создать программу в «Excel»

Содержание

Как написать программу в экселе

Один из наиболее часто задаваемых вопросов задаваемых начинающими пользователями звучит так: «Как создать программу в «Excel» и возможно ли это сделать в принципе?»

Ответ на него не так прост.

Создать полноценную программу или продвинутую игру инструментами «Эксель» практически невозможно.

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

Опытные программисты при помощи макросов написанных на языке VBA даже создают примитивные игры популярные в начале 90-х прошлого столетия: тетрис, змейка, пинг-понг и т.д.

Рассмотрим азы создания программ в VBA Excel.

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

Второй этап — подготовка программы «Excel» к написанию макросов: включить макросы, отобразить панель разработчика и т.д.
Подробно подготовка к написанию простого макроса описана в статье:

Как написать простой макрос в программе Excel

Третий этап — при помощи языка программирования VBA «оцифровываем» блок схему. Делаем ее понятной для компьютера. То есть пишем сам код на языке программирования VBA.

Некоторые варианты кодов макросов опубликованы на нашем сайте в разделе Макросы и VBA.

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

Что такое VBA

Программирование в Excel осуществляется посредством языка программирования Visual Basic for Application, который изначально встроен в самый известный табличный процессор от Microsoft.

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

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

Объекты, коллекции, свойства и методы

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

Главным из них является Application, соответствующий самой программе Excel. Затем следуют Workbooks, Worksheets, а также Range. Например, для обращения к ячейке A1 на конкретном листе следует указать путь с учетом иерархии.

Что касается понятия «коллекция», то это группа объектов того же класса, которая в записи имеет вид ChartObjects. Ее отдельные элементы также являются объектами.

Следующее понятие — свойства. Они являются необходимой характеристикой любого объекта. Например, для Range — это Value или Formula.

Методы — это команды, показывающие, что требуется сделать. При написании кода в VBA их необходимо отделять от объекта точкой. Например, как будет показано в дальнейшем, очень часто при программировании в «Эксель» используют команду Cells(1,1).Select. Она означает, что необходимо выбрать ячейку с координатами (1,1) т. е. A1.

Вместе с ней нередко используется Selection.ClearContents. Ее выполнение означает очистку содержимого выбранной ячейки.

Как начать

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

Затем необходимо перейти в приложение VB, для чего достаточно воспользоваться комбинацией клавиш «Alt» и «F11». Далее:

в строке меню, расположенном в верхней части окна, нажимают на иконку рядом с иконкой Excel;
выбирают команду Mudule;
сохраняют, нажав на иконку с изображением floppy disk;
пишут, скажем так, набросок кода.

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

Обратите внимание, что строка «’Наш код» будет выделена другим цветом (зеленым). Причина в апострофе, поставленном в начале строки, который обозначает, что далее следует комментарий.

Теперь вы можете написать любой код и создать для себя новый инструмент в VBA Excel (примеры программ см. далее).

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

Макросы в Excel

За таким названием скрываются программы, написанные на языке Visual Basic for Application. Таким образом, программирование в Excel — это создание макросов с нужным кодом. Благодаря этой возможности табличный процессор Microsoft саморазвивается, подстраиваясь под требования конкретного пользователя. Разобравшись с тем, как создавать модули для написания макросов, можно приступать к рассмотрению конкретных примеров программ VBA Excel. Лучше всего начать с самых элементарных кодов.

Пример 1

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

открывают вкладку «Вид»;
переходят на пиктограмму «Макросы»;
жмут на «Запись макроса»;
заполняют открывшуюся форму.

Для простоты в поле «Имя макроса» оставляют «Макрос1», а в поле «Сочетание клавиш» вставляют, например, hh (это значит, что запустить программку можно будет блиц-командой «Ctrl+h»). Нажимают Enter.

Теперь, когда уже запущена запись макроса, производят копирование содержимого какой-либо ячейки в другую. Возвращаются на исходную пиктограмму. Нажимают на «Запись макроса». Это действие означает завершение программки.

вновь переходят на строку «Макросы»;
в списке выбирают «Макрос 1»;
нажимают «Выполнить» (то же действие запускается начатием сочетания клавиш «Ctrl+hh»).

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

Имеет смысл увидеть, как выглядит код. Для этого вновь переходят на строку «Макросы» и нажимают «Изменить» или «Войти». В результате оказываются в среде VBA. Собственно, сам код макроса находится между строками Sub Макрос1() и End Sub.

Если копирование было выполнено, например, из ячейки А1 в ячейку C1, то одна из строк кода будет выглядеть, как Range(“C1”).Select. В переводе это выглядит, как «Диапазон(“C1”).Выделить», иными словами осуществляет переход в VBA Excel, в ячейку С1.

Активную часть кода завершает команда ActiveSheet.Paste. Она означает запись содержания выделенной ячейки (в данном случае А1) в выделенную ячейку С1.

Пример 2

Циклы VBA помогают создавать различные макросы в Excel.

Циклы VBA помогают создавать различные макросы. Предположим, что имеется функция y=x + x 2 + 3x 3 – cos(x). Требуется создать макрос для получения ее графика. Сделать это можно только, используя циклы VBA.

За начальное и конечное значение аргумента функции берут x1=0 и x2=10. Кроме того, необходимо ввести константу — значение для шага изменения аргумента и начальное значение для счетчика.

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

Do While x1 0 Then Cells(1, 1).Value = 1

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

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

При записи макроса записываются все действия, описанные в Visual Basic для приложений (VBA) коде. Эти действия могут включать ввод текста или чисел, нажатие ячеек или команд на ленте или в меню, форматирование ячеек, строк или столбцов, а также импорт данных из внешнего источника, например Microsoft Access. Приложение Visual Basic (VBA) — это подмножество мощного языка программирования Visual Basic, которое входит в большинство приложений Office. Несмотря на то, что VBA обеспечивает возможность автоматизации процессов между приложениями Office, вам не нужно знать код VBA или программное программирование, если это нужно.

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

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

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

Перед записью макросов полезно знать следующее:

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

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

В макросе могут содержаться и задачи, не относящиеся к Excel. Процесс макроса может охватывать прочие приложения Office и другие программы, которые поддерживают Visual Basic для приложений (VBA). Например, вы можете записать макрос, который сначала обновляет таблицу в Excel, а затем открывает Outlook для ее отправки по электронной почте.

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

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

В поле Имя макроса введите название макроса. Сделайте имя понятным, чтобы можно было быстро найти нужный макрос.

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

Чтобы назначить сочетание клавиш для запуска макроса, в поле Сочетание клавиш введите любую строчную или прописную букву. Рекомендуется использовать сочетания клавиш с CTRL+ SHIFT, так как они будут заменять собой совпадающие с ними стандартные сочетания клавиш в Excel, пока открыта книга, содержащая макрос. Например, если назначить сочетание клавиш CTRL+Z (Отменить), вы не сможете использовать его для функции «Отменить» в данном экземпляре Excel.

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

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

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

Работа с макросами, записанными в Excel

На вкладке Разработчик щелкните Макросы, чтобы просмотреть макросы, связанные с книгой. Кроме того, можно нажать клавиши ALT+F8. При этом откроется диалоговое окно Макрос.

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Сведения о параметрах безопасности макросов и их значении.

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

С помощью редактора Visual Basic можно изменять макросы, присоединенные к книге.

Если книга содержит макрос VBA, который нужно использовать где-либо еще, этот модуль можно скопировать в другую книгу с помощью редактора Microsoft Visual Basic.

Назначение макроса объекту, фигуре или графическому элементу

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

В поле Назначить макроса выберите макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Узнайте, как включать и отключать макросы в файлах Office.

Открытие редактора Visual Basic

Нажмите клавиши ALT+F11.

Узнайте, как найти справку по элементам Visual Basic.

Работа с записанным кодом в редакторе Visual Basic (VBE)

С помощью редактора Visual Basic (VBE) вы можете добавлять в записанный код собственные переменные, управляющие структуры и другие элементы, которые не поддерживает средство записи макросов. Так как средство записи макросов фиксирует почти каждый шаг, выполняемый во время записи, может также потребоваться удалить ненужный код. Просмотр записанного кода — отличный способ научиться программировать на VBA или отточить свои навыки.

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

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

Перед записью макросов полезно знать следующее:

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

Перейдите в раздел настройки _гт_ Excel . панель инструментов _Гт_ ленты _амп_.

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

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

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

В списке Сохранить в выберите книгу, в которой вы хотите сохранить макрос.

Как правило, макрос сохраняется в указанном расположении книги , но если вы хотите, чтобы макрос был доступен при использовании Excel, выберите пункт Личная книга макросов. Если выбрать команду Личная книга макросов, в Excel будет создана скрытая личная книга макросов (личное. XLSB), если он еще не существует, и сохранение макроса в этой книге. Книги в этой папке открываются автоматически при запуске Excel, а код, хранящийся в личной книге макросов, будет указан в диалоговом окне Макрос, которое описано в следующем разделе.

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

В поле Описание при необходимости введите краткое описание действий макроса.

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

Чтобы начать запись макроса, нажмите кнопку ОК.

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

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

Работа с макросами, записанными в Excel

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

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

Ниже приведены дополнительные сведения о работе с макросами в Excel.

Узнайте, как включать и отключать макросы в Excel для Mac.

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

Назначение макроса объекту, фигуре или графическому элементу

В поле Назначить макроса выберите макрос, который вы хотите назначить.

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

Вы можете назначать макросы формам и элементам ActiveX на листе.

Открытие редактора Visual Basic

На вкладке Разработчик щелкните Visual Basic или выберите Сервис > Макрос > Редактор Visual Basic.

Узнайте, как найти справку по элементам Visual Basic.

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

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

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Еще…Меньше

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

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

Процедура

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

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

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

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

Подробнее о макросах

Вы можете узнать немного о языке программирования Visual Basic путем редактирования макроса.

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

Узнайте, как записанные действия отображаются как код. Возможно, какой-то код вам понятен, а часть может показаться немного неявным.

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

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

Дополнительные информацию о создании макроса см. в теме Создание и удаление макроса.
org/ListItem»>
Чтобы узнать, как запускать макрос, см. в этой теме.

Процедура

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

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

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

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

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

При желании введите имя макроса в поле Имя макроса, введите сочетания клавиш в поле Сочетания клавиш и описание в поле Описание, а затем нажмите кнопку ОК, чтобы начать запись.
Выполните действия, которые нужно автоматизировать, например ввести шаблонный текст или заполнить столбец данных.
На вкладке Разработчик в группе Код нажмите кнопку Остановить запись.

Подробнее о макросах

Вы можете узнать немного о языке программирования Visual Basic путем редактирования макроса.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

VBA Excel. Начинаем программировать с нуля

Первое знакомство с редактором VBA Excel, создание процедур (подпрограмм) и написание простейшего кода, работающего с переменными и ячейками рабочего листа.

Начинаем программировать с нуля
Часть 1. Первая программа
[Часть 1] [Часть 2] [Часть 3] [Часть 4]

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

Знакомство с редактором VBA

Создайте новую книгу Excel и сохраните ее как книгу с поддержкой макросов с расширением .xlsm. В старых версиях Excel по 2003 год – как обычную книгу с расширением .xls.
Нажмите сочетание клавиш «Левая_клавиша_Alt+F11», которое откроет редактор VBA. С правой клавишей Alt такой фокус не пройдет. Также, в редактор VBA можно перейти по ссылке «Visual Basic» из панели инструментов «Разработчик» на ленте быстрого доступа. Если вкладки «Разработчик» на ленте нет, ее следует добавить в настройках параметров Excel.

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

Нажмите кнопку «Module» во вкладке «Insert» главного меню. То же подменю откроется при нажатии на вторую кнопку после значка Excel на панели инструментов.

После нажатия кнопки «Module» вы увидите ссылку на него, появившуюся в проводнике слева.

Первая программа на VBA Excel

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

Откройте стандартный модуль двойным кликом по его ссылке в проводнике. Поместите в него курсор и нажмите кнопку «Procedure…» во вкладке «Insert» главного меню. Та же ссылка будет доступна при нажатии на вторую кнопку после значка Excel на панели инструментов.

В результате откроется окно добавления шаблона процедуры (Sub).

Наберите в поле «Name» имя процедуры: «Primer1», или скопируйте его отсюда и вставьте в поле «Name». Нажмите кнопку «OK», чтобы добавить в модуль первую и последнюю строки процедуры.

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

Вставьте внутрь шаблона процедуры следующую строку: MsgBox "Привет".

Функция MsgBox выводит информационное сообщение с указанным текстом. В нашем примере – это «Привет».

Проверьте, что курсор находится внутри процедуры, и запустите ее, нажав клавишу «F5». А также, запустить процедуру на выполнение можно, нажав на треугольник (на изображении под пунктом меню «Debug») или на кнопку «Run Sub/UserForm» во вкладке «Run» главного меню редактора VBA Excel.

Если вы увидели такое сообщение, как на изображении, то, поздравляю – вы написали свою первую программу!

Работа с переменными

Чтобы использовать в процедуре переменные, их необходимо объявить с помощью ключевого слова «Dim». Если при объявлении переменных не указать типы данных, они смогут принимать любые доступные в VBA Excel значения. Комментарии в тексте процедур начинаются со знака «’» (апостроф).

Пример 2
Присвоение переменным числовых значений:

Public Sub Primer2()

‘Объявляем переменные x, y, z

Dim x, y, z

‘Присваиваем значение переменной x

x = 25

‘Присваиваем значение переменной y

y = 35

‘Присваиваем переменной z сумму

‘значений переменных x и y

z = x + y

‘Выводим информационное сообщение

‘со значением переменной z

MsgBox z

End Sub

Пример 3
Присвоение переменным строковых значений:

Public Sub Primer3()

‘Объявляем переменные x, y, z

Dim x, y, z

‘Присваиваем строку переменной x

x = «Добрый»

‘Присваиваем строку переменной y

y = «день!»

‘Присваиваем переменной z строку,

‘состоящую из строк x и y

‘с пробелом между ними

z = x & » » & y

‘Выводим информационное сообщение

‘со значением переменной z

MsgBox z

End Sub

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

Изменение содержимого ячеек

Для обозначения диапазонов, в том числе и отдельных ячеек, в VBA Excel имеется ключевое слово «Range». Ячейке A1 на рабочем листе будет соответствовать выражение Range("A1") в коде VBA Excel.

Пример 4

Public Sub Primer4()

‘Объявляем переменную x

Dim x

‘Присваиваем значение переменной x

x = 125.61

‘Присваиваем ячейке A1

‘значение переменной x

Range(«A1») = x

‘Присваиваем значение ячейке B1

Range(«B1») = 356.24

‘Записываем в ячейку C1

‘сумму ячеек A1 и B1

Range(«C1») = Range(«A1») + Range(«B1»)

End Sub

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

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

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

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

Пользовательские функции, такие как макросы, используют Visual Basic для приложений (VBA) 9Язык программирования 0007. Они отличаются от макросов двумя важными способами. Во-первых, они используют процедур Function вместо процедур Sub . То есть они начинаются с оператора Function вместо оператора Sub и заканчиваются End Function вместо End Sub . Во-вторых, они выполняют расчеты, а не действия. Определенные виды операторов, например операторы, которые выбирают и форматируют диапазоны, исключаются из пользовательских функций. В этой статье вы узнаете, как создавать и использовать пользовательские функции. Для создания функций и макросов вы работаете с Редактор Visual Basic (VBE) , который открывается в новом окне отдельно от Excel.

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

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

Чтобы создать пользовательскую функцию СКИДКИ в этой книге, выполните следующие действия:

Нажмите Alt+F11 , чтобы открыть редактор Visual Basic (на Mac нажмите FN+ALT+F11 ), а затем нажмите Вставить > Модуль . Окно нового модуля появится в правой части редактора Visual Basic.

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

Функция СКИДКА(количество, цена) Если количество >=100 Тогда СКИДКА = количество * цена * 0,1 Еще СКИДКА = 0 Конец, если СКИДКА = Заявка.Раунд(Скидка, 2) Конечная функция

Примечание. Чтобы сделать код более читаемым, можно использовать клавишу Tab для отступа строк. Отступы нужны только вам и не являются обязательными, так как код будет работать как с ними, так и без них. После ввода строки с отступом редактор Visual Basic предполагает, что следующая строка будет иметь такой же отступ. Чтобы сдвинуться (то есть влево) на один символ табуляции, нажмите Shift+Tab .

Теперь вы готовы использовать новую функцию СКИДКИ. Закройте редактор Visual Basic, выберите ячейку G7 и введите следующее:

=СКИДКА(D7,E7)

Excel вычисляет 10-процентную скидку на 200 единиц по цене 47,50 долларов США за единицу и возвращает 950,00 долларов США.

В первой строке вашего кода VBA функция СКИДКА (количество, цена) вы указали, что функция СКИДКА требует двух аргументов: количество и цена . Когда вы вызываете функцию в ячейке рабочего листа, вы должны включить эти два аргумента. В формуле =СКИДКА(D7;E7) D7 — это аргумент количества , а E7 — аргумент цены . Теперь вы можете скопировать формулу СКИДКИ в G8:G13, чтобы получить результаты, показанные ниже.

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

Оператор If в следующем блоке кода проверяет аргумент количество и определяет, больше или равно ли количество проданных товаров 100:

Если количество >= 100 Тогда
 СКИДКА = количество * цена * 0,1
Еще
 СКИДКА = 0
Конец, если

Если количество проданных товаров больше или равно 100, VBA выполняет следующую инструкцию, которая умножает количество значение на цену значение, а затем результат умножается на 0,1:

Скидка = количество * цена * 0,1

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

Если количество меньше 100, VBA выполняет следующую инструкцию:

Скидка = 0

Наконец, следующий оператор округляет значение, присвоенное переменной Discount , до двух знаков после запятой:

Скидка = Заявка.Раунд(Скидка, 2)

В VBA нет функции ОКРУГЛ, а в Excel есть. Поэтому, чтобы использовать ROUND в этом операторе, вы указываете VBA искать метод (функцию) Round в объекте Application (Excel). Вы делаете это, добавляя слово Application перед словом Round. Используйте этот синтаксис всякий раз, когда вам нужно получить доступ к функции Excel из модуля VBA.

Пользовательская функция должна начинаться с оператора Function и заканчиваться оператором End Function. В дополнение к имени функции оператор Function обычно указывает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel есть несколько встроенных функций — например, СЛУЧАЙ и СЕЙЧАС, — которые не используют аргументы.

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

Количество ключевых слов VBA, которые вы можете использовать в пользовательских функциях, меньше, чем количество, которое вы можете использовать в макросах. Пользовательским функциям не разрешено делать ничего, кроме возврата значения в формулу на листе или в выражение, используемое в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окон, редактировать формулу в ячейке или изменять параметры шрифта, цвета или узора для текста в ячейке. Если вы включаете код «действия» такого типа в процедуру функции, функция возвращает ошибку #ЗНАЧ! ошибка.

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

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

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

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

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

Чтобы использовать пользовательскую функцию, рабочая книга, содержащая модуль, в котором вы создали функцию, должна быть открыта. Если эта рабочая книга не открыта, вы получите #NAME? ошибка при попытке использовать функцию. Если вы ссылаетесь на функцию в другой рабочей книге, перед именем функции необходимо указать имя рабочей книги, в которой находится функция. Например, если вы создаете функцию с именем СКИДКА в книге с именем Personal.xlsb и вызываете эту функцию из другой книги, вы должны ввести =personal.xlsb!discount() , а не просто =discount() .

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

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

После создания необходимых функций нажмите Файл > Сохранить как .
В Excel 2007 нажмите кнопку Microsoft Office и выберите Сохранить как
В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions , в папке AddIns . В диалоговом окне «Сохранить как » будет предложена эта папка, поэтому все, что вам нужно сделать, это принять расположение по умолчанию.
После сохранения книги щелкните Файл > Параметры Excel .
В Excel 2007 щелкните значок Кнопка Microsoft Office и нажмите Параметры Excel .
В диалоговом окне Параметры Excel выберите категорию Надстройки .
В раскрывающемся списке Управление выберите Надстройки Excel . Затем нажмите кнопку Перейти .
org/ListItem»>
В диалоговом окне Add-Ins установите флажок рядом с именем, которое вы использовали для сохранения книги, как показано ниже.

После создания необходимых функций нажмите Файл > Сохранить как .
В диалоговом окне Сохранить как откройте раскрывающийся список Сохранить как тип и выберите Надстройка Excel . Сохраните книгу под узнаваемым именем, например MyFunctions .
org/ListItem»>
После сохранения книги щелкните Инструменты > Надстройки Excel .
В диалоговом окне Надстройки нажмите кнопку Обзор, чтобы найти надстройку, нажмите Открыть , затем установите флажок рядом с надстройкой в поле Доступные надстройки .

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

Двойной щелчок по этому модулю в Project Explorer приводит к тому, что редактор Visual Basic отображает код вашей функции. Чтобы добавить новую функцию, поместите точку ввода после оператора End Function, завершающего последнюю функцию в окне кода, и начните вводить текст. Таким образом вы можете создать столько функций, сколько вам нужно, и они всегда будут доступны в категории «Определяемые пользователем» в диалоговом окне «Вставить функцию».

Этот контент был первоначально создан Марком Доджем и Крейгом Стинсоном как часть их книги Microsoft Office Excel 2007 наизнанку . С тех пор он был обновлен, чтобы применяться и к более новым версиям Excel.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Программирование в Excel — пошаговое руководство по программированию на VBA

Программирование — это написание набора инструкций, которые сообщают Excel о выполнении одной или нескольких задач. Эти инструкции написаны на языке Visual Basic для приложений (VBA), так как это язык, понятный для Excel. Чтобы написать инструкцию, можно либо написать код на VBA, либо записать макрос в Excel. Записывается макрос для автоматизации повторяющихся задач. Когда макрос записывается, VBA генерирует код в фоновом режиме.

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

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

Содержание

Что такое программирование в Excel?
Этапы программирования в Excel VBA
Этап 1 — Включение вкладки «Разработчик» в Excel
Этап 2 — Запись макроса в Excel
Этап 3 — Просмотр и проверка кода VBA, сгенерированного записанным макросом
Этап 4 — Проверка кода VBA записанного макроса
Этап 5. Сохранение записанного макроса (или кода VBA)
Часто задаваемые вопросы
Рекомендуемые статьи

Этапы программирования в Excel VBA

Программирование в Excel VBA осуществляется в следующие этапы:

Включение вкладки «Разработчик» в Excel
Запись макроса в Excel
Просмотр и изучение сгенерированного кода VBA по записанному макросу
Проверка кода VBA записанного макроса
Сохранение записанного макроса (или кода VBA)

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

Этап 1. Включение вкладки «Разработчик» в Excel

Давайте разберемся, как включить вкладку «Разработчик» Вкладка «РАЗРАБОТЧИК» в ExcelВключение вкладки «Разработчик» в Excel может помочь пользователю выполнять различные функции для VBA, макросов и надстроек, таких как импорт и экспорт XML , разработка форм и т. д. Эта вкладка по умолчанию отключена в Excel; таким образом, пользователю необходимо сначала включить его в меню параметров. Подробнее в Excel. Эта вкладка позволяет записывать макрос и получать доступ к VBA. Когда вкладка «Разработчик» включена, она отображается на ленте Excel. Лента в ExcelЛенты в Excel 2016 предназначены для того, чтобы помочь вам легко найти команду, которую вы хотите использовать. Ленты организованы в логические группы, называемые вкладками, каждая из которых имеет собственный набор функций.Подробнее. Однако по умолчанию Excel не отображает вкладку «Разработчик».

Примечание: Чтобы узнать об альтернативном методе записи макроса и доступа к VBA, обратитесь к «альтернативе шагу 1» на этапах 2 и 3.

Действия по включению вкладки «Разработчик» в Excel перечислены ниже. :

Шаг 1: Перейдите на вкладку «Файл», отображаемую на ленте Excel.

Шаг 2: Выберите «опции», показанные на следующем рисунке.

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

Шаг 4: В разделе «выбрать команды из» (в левой части окна) убедитесь, что выбраны «популярные команды». В разделе «настроить ленту» (в правой части окна) выберите «основные вкладки» из раскрывающегося списка.

Далее установите флажок «Разработчик» и нажмите «ОК». Этот флажок показан на следующем изображении.

Шаг 5: На ленте Excel появится вкладка «Разработчик», как показано на следующем рисунке.

Этап 2. Запись макроса в Excel

Давайте узнаем, как записывать макросыЗапись макросов в ExcelЗапись макросов — это метод, с помощью которого Excel сохраняет задачи, выполняемые пользователем. Каждый раз, когда запускается макрос, именно эти действия выполняются автоматически. Макросы создаются либо на вкладке «Вид» (в раскрывающемся списке «Макросы»), либо на вкладке «Разработчик» Excel. читать дальше в экселе. Когда макрос записывается, все задачи, выполняемые пользователем (в рабочей книге), сохраняются в средстве записи макросов до тех пор, пока не будет выбрана опция «остановить запись». Помимо Microsoft Excel, макрос можно записать для всех приложений Office, поддерживающих VBA.

Шаги для записи макроса в Excel перечислены ниже:

Шаг 1: На вкладке «Разработчик» нажмите «запись макроса» из группы «код».

Альтернатива шагу 1: Можно также щелкнуть «запись макроса» в группе «макросы» на вкладке «Вид» Excel.

Шаг 2: Откроется окно «запись макроса», как показано на следующем рисунке. В этом окне можно назвать макрос перед его записью. Имя макроса по умолчанию, данное Excel, — «macro1». 9, #, *, &), кроме символа подчеркивания (_).

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

Длина не может превышать 255 символов.

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

Шаг 3: В поле «имя макроса» мы ввели имя «recording_macro».

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

Шаг 4: Нажмите «ОК», чтобы начать запись макроса. После нажатия кнопки «ОК» в предыдущем окне кнопка «Записать макрос» (на вкладке «Разработчик» или на вкладке «Вид») изменится на кнопку «Остановить запись».

Далее выполните задачи для записи в макросе «recording_macro».

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

Однако, если возникает ошибка последовательности, необходимо либо перезаписать действия, либо отредактировать код VBA Код VBA Код VBA относится к набору инструкций, написанных пользователем на языке программирования приложений Visual Basic в редакторе Visual Basic (VBE) для выполнения конкретной задачи.читать дальше вручную. Если макрос записывается впервые, рекомендуется сохранить копию рабочей книги, чтобы предотвратить любые нежелательные изменения сохраненных данных.

На следующих шагах (шаг 4a–шаг 4c) задачи, подлежащие записи, выполнены.

Шаг 4a: Выберите ячейку A1 рабочего листа. Это первая задача, которая записывается. Выбор показан на следующем изображении.

Шаг 4b: Введите «Добро пожаловать в VBA» в ячейку A1. Это вторая задача, которая записана. Исключите начальные и конечные двойные кавычки при вводе.

Шаг 4c: Нажмите клавишу «Ввод». В результате выделение перемещается из ячейки A1 в ячейку A2. Это становится третьей задачей, которая записывается.

Выбор показан на следующем рисунке.

Шаг 5: Нажмите «Остановить запись» в группе «Код» на вкладке «Разработчик». При выборе этого параметра Excel получает указание воздержаться от записи каких-либо дальнейших задач.

Опция «остановить запись» показана на следующем изображении.

Этап 3. Просмотр и изучение кода VBA, сгенерированного записанным макросом

Давайте посмотрим и изучим код VBA, сгенерированный макросом, записанным на этапе 2. Помните, что можно либо напрямую написать код в редакторе Visual Basic ( VBE или VB Editor) или позволить макросу сделать то же самое.

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

Шаги для просмотра и изучения кода, сгенерированного записанным макросом, перечислены ниже:

Шаг 1: Нажмите «visual basic» в группе «код» на вкладке «Разработчик». Этот вариант показан на следующем изображении.

Альтернатива шагу 1: Вместо предыдущего шага нажмите одновременно клавиши «Alt+F11». Это ярлык. Ярлыки в Excel. Ярлык Excel — это метод выполнения ручного задания более быстрым способом. Узнайте больше, чтобы открыть окно VBE.

Примечание. «Alt+F11» — это клавиша-переключатель, при повторном нажатии которой можно переключаться между VBE и Excel.

Шаг 2: Откроется окно редактора Visual Basic, как показано на следующем рисунке.

Слева от окна VBE отображаются рабочий лист, рабочая книга и модуль. Это окно слева (названное «Project-VBAProject») также известно как окно проекта или проводник проекта VBE.

Шаг 3: Дважды щелкните «модули», показанные на следующем рисунке.

Примечание: «Модули» — это папки, отображаемые в окне «Проект» после записи макроса. Они не отображаются до записи макроса. «Модули» также отображаются, когда модуль вставляется вручную с вкладки «Вставка» в VBE.

Шаг 4: Дважды щелкните «module1» под модулями. Код появится в правой части окна VBE. Это окно справа [названное «Book1-Module1 (Code)»] известно как окно кода модуля.

Код показан на следующем изображении.

Примечание: Код, сгенерированный записью макроса, можно проверить в папке «modules» (в окне кода модуля). В окне кода модуля также можно написать код вручную или скопировать-вставить его из другого источника.

На следующих шагах (шаги 4a–4d) был изучен код, сгенерированный записанным макросом.

Шаг 4a: Первое слово кода — «Sub». «Sub» означает подпрограмму или процедуру. В начале кода отображается слово «Sub», имя макроса (recording_macro) и пара пустых скобок. Затем следуют операторы, которые должны выполняться кодом. Вот эти операторы:

ActiveCell.FormulaR1C1 = «Добро пожаловать в VBA»

Диапазон («A2»). Выберите

Код заканчивается на «End Sub».

Начало или заголовок [Sub Recording_Macro ()] и конец или конец [End Sub] кода показаны на следующем рисунке.

Примечание 1: Слова «макрос» и «код» часто взаимозаменяемы некоторыми пользователями Excel. Однако некоторые пользователи также различают эти два слова.

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

Примечание 2: Перед «Sub» могут стоять слова «Private», «Public», «Friend» или «Static». Все эти слова задают область действия подпрограммы. Подпрограмма по умолчанию, используемая в VBA, называется «Public Sub». Итак, когда «Sub» написано в коде, это подразумевает «Public Sub».

«Общая подпрограмма» может быть инициирована подпрограммами различных модулей. Однако «Private Sub» не может быть инициирован подпрограммами других модулей.

Шаг 4b: Первое действие, которое мы выполнили (на шаге 4a этапа 2), состояло в том, чтобы выбрать ячейку A1. Соответственно, следующий оператор кода сообщает Excel, что активной ячейкой является R1C1.

ActiveCell.FormulaR1C1

При записи макроса VBA использует стиль R1C1 для обращения к ячейкам. В этом стиле за буквой R следует номер строки, а за буквой C следует номер столбца. Таким образом, ячейка R1C1 подразумевает, что номер строки равен 1 и номер столбца также равен 1. Другими словами, ячейка R1C1 такая же, как ячейка A1 Excel.

Шаг 4c: Второе действие, которое мы выполнили (на шаге 4b этапа 2), заключалось в том, чтобы ввести «Добро пожаловать в VBA» в ячейке A1. Итак, следующий оператор кода сообщает Excel, что значение в ячейке R1C1 — «Добро пожаловать в VBA».

  ActiveCell.FormulaR1C1 = «Добро пожаловать в VBA»

Шаг 4d: Третье действие, которое мы выполнили (на шаге 4c этапа 2), заключалось в нажатии клавиши «Ввод». При нажатии этой клавиши выделение сместилось с ячейки A1 на ячейку A2. Таким образом, следующий оператор указывает Excel выбрать ячейку A2.

  Диапазон («A2»). Выберите

Таким образом VBA генерирует код для всех действий, выполняемых на этапе 2 программирования в Excel. Построчное изучение кода облегчает его интерпретацию.

Этап 4. Проверка кода VBA записанного макроса

Проверим код при его многократном запуске. Обратите внимание, что макрос (или код) можно запускать столько раз, сколько нужно. Каждый раз, когда он запускается, он выполняет записанные задачи в Excel.

Шаги для тестирования кода, который мы рассмотрели на этапе 3, перечислены ниже:

Шаг 1: Удалите строку «Добро пожаловать в VBA» из ячейки A1 Excel. Пусть A1 останется пустой, выбранной ячейкой. На следующем изображении показана пустая ячейка A1.

Примечание: Чтобы вернуться из VBE в Excel, нажмите клавишу-переключатель «Alt+F11».

Шаг 2: Снова перейдите в VBE, нажав клавишу «Alt+F11». Щелкните в любом месте кода. Затем нажмите кнопку «Запустить Sub/UserForm (F5)». Эта кнопка показана в синей рамке на следующем изображении.

Примечание: В качестве альтернативы можно нажать клавишу F5, чтобы запустить код VBA.

Шаг 3: Результат показан на следующем рисунке. Предыдущий код вводит строку «Добро пожаловать в VBA» в ячейку A1. После этого выделение перемещается в ячейку A2. Строка «Добро пожаловать в VBA» была введена в ячейку A1, потому что эта ячейка была выбрана (на шаге 1) перед запуском кода.

При каждом запуске кода текущая выбранная ячейка (или активная ячейка) заполняется строкой «Добро пожаловать в VBA». Затем выделение перемещается в ячейку A2. Итак, если ячейка M10 является активной ячейкой, запуск кода заполняет эту ячейку указанной строкой и выбирает ячейку A2 в конце.

Однако, если бы была выбрана ячейка A2, запуск кода заполнил бы эту ячейку строкой «Добро пожаловать в VBA». Более того, в итоге выбранной ячейкой осталась бы ячейка А2.

Этап 5 — Сохранение записанного макроса (или кода VBA)

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

Шаги по сохранению книги, содержащей макрос (или код VBA), перечислены ниже:

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

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

Примечание 1: Команда «сохранить как» используется при первом сохранении книги. Он также используется, когда необходимо создать новую копию книги и в то же время сохранить исходную копию.

Примечание 2: Если рабочая книга, содержащая макрос, сохранена как обычная рабочая книга (с расширением «.xlsx»), макрос не будет сохранен. Далее можно потерять код записанного макроса.

Часто задаваемые вопросы

1. Что такое программирование и как оно осуществляется в Excel?

Программирование означает указание Excel выполнять одну или несколько задач. Чтобы проинструктировать Excel, можно написать код на Visual Basic для приложений (VBA) или записать макрос в Excel. Каждый раз, когда записывается макрос, VBA генерирует код в фоновом режиме.

Шаги для программирования в Excel перечислены ниже:

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

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

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

2. Как написать код для программирования в Excel?

Для программирования в Excel код пишется в редакторе Visual Basic (VBE), который является инструментом VBA. Шаги для написания кода в VBE перечислены ниже:

a. Откройте пустую книгу Excel.
б. Нажмите клавиши «Alt+F11», чтобы открыть VBE.
в. Выберите любой рабочий лист из «Объектов Microsoft Excel», перечисленных в «Окне проекта Project-VBA».
д. Перейдите на вкладку «Вставка» и выберите «модуль». Папка с именем «modules» и объект с именем «module1» создаются в «окне Project-VBA Project». При этом справа открывается окно с названием «Book1-Module1 (Code)».
эл. Введите код в окно «Book1-Module1 (Code)», которое открылось на предыдущем шаге.
ф. Щелкните в любом месте кода, когда он будет полностью написан.
г. Запустите код, нажав клавишу F5 или нажав кнопку «Выполнить подчиненную/пользовательскую форму (F5)».

Если код был введен правильно на шаге «e», Excel выполнит поставленные перед ним задачи. Однако, если в коде есть ошибка, появится сообщение об ошибке.

Примечание: Для сохранения кода обратитесь к этапу 5 программирования в Excel, приведенному в этой статье.

3. Как научиться программировать в Excel?

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

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

Примеры кода VBA для Эксель

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

Ниже вы найдете список основных примеров макросов для общих задач автоматизации Excel.

Копирование и вставка строки с одного листа на другой

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

Sub Paste_OneRow()
'Копировать и вставить строку
Листы("лист1").Диапазон("1:1").Копировать Листы("лист2").Диапазон("1:1")
Application.CutCopyMode = Ложь
Конец сабвуфера

Отправить электронное письмо

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

Sub Send_Mail ()
    Dim OutApp как объект
    Затемнить OutMail как объект
    Установите OutApp = CreateObject("Outlook.Application")
    Установить OutMail = OutApp.CreateItem(0)
    С исходящей почтой
        .to = "[email protected]"
        .Subject = "Тестовое письмо"
        .Body = "Тело сообщения"
        .Attachments.Add ActiveWorkbook.FullName
        .Отображать
    Конец с
    Установите OutMail = Ничего
    Установить OutApp = Ничего
Конец сабвуфера

Список всех листов в рабочей книге

Этот макрос выводит список всех листов в рабочей книге.

Подлисты списка()
    
    Dim ws As рабочий лист
    Dim x как целое число
    
    х = 1
    
    ActiveSheet.Range("A:A").Очистить
    
    Для каждого ws в рабочих листах
        ActiveSheet.Cells(x, 1) = ws.Name
        х = х + 1
    Следующий мс
    
Конец сабвуфера

Показать все рабочие листы

Этот макрос отобразит все рабочие листы.

' Показать все рабочие листы
Sub Показать все рабочие листы ()
    Dim ws As рабочий лист
    
    Для каждого ws в ActiveWorkbook.Worksheets
        ws.Visible = кслшитвисибле
    Следующий мс
    
Конец сабвуфера

Скрыть все рабочие листы, кроме активного

Этот макрос скроет все рабочие листы, кроме активного рабочего листа.

' Скрыть все листы, кроме активного листа
Sub HideAllExceptActiveSheet()
    Dim ws As рабочий лист
    
    Для каждого ws в ThisWorkbook.Worksheets
        Если ws.Name <> ActiveSheet.Name, тогда ws. Visible = xlSheetHidden
    Следующий мс
    
Конец сабвуфера

Снять защиту со всех листов

Этот пример макроса снимает защиту со всех листов в книге.

' Снять защиту со всех рабочих листов
Sub UnProtectAllSheets ()
    Dim ws As рабочий лист
    
    Для каждого ws в рабочих листах
        ws.Unprotect "пароль"
    Следующий мс
    
Конец сабвуфера

Защитить все листы

Этот макрос защитит все листы в книге.

'Защитить все рабочие листы
Sub ProtectAllSheets ()
    Dim ws As рабочий лист
    
    Для каждого ws в рабочих листах
        ws.protect "пароль"
    Следующий мс
    
Конец сабвуфера

Удалить все фигуры

Этот макрос удалит все фигуры на листе.

Sub Удалить все фигуры ()
Dim GetShape как форма
Для каждого GetShape в ActiveSheet.Shapes
  GetShape.Удалить
Следующий
Конец сабвуфера

Удалить все пустые строки в рабочем листе

Этот пример макроса удалит все пустые строки в рабочем листе.

Sub DeleteBlankRows()
Тусклый х как долго
С ActiveSheet
    Для x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Шаг -1
        Если WorksheetFunction.CountA(.Rows(x)) = 0 Тогда
            ActiveSheet.Rows(x).Удалить
        Конец, если
    Следующий
Конец с
Конец сабвуфера

Выделение повторяющихся значений в выделенном фрагменте

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

' Выделить повторяющиеся значения в выборе
Sub HighlightDuplicateValues ()
    Dim myRange As Range
    Затемнить ячейку как диапазон
    
    Установите myRange = Выбор
    
    Для каждой ячейки в моем диапазоне
        Если WorksheetFunction.CountIf(myRange, cell.Value) > 1 Тогда
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера

Подсветка отрицательных чисел

Этот макрос автоматизирует задачу выделения отрицательных чисел.

' Выделить отрицательные числа
Sub HighlightNegativeNumbers()
    Dim myRange As Range
    Затемнить ячейку как диапазон
    
    Установите myRange = Выбор
    
    Для каждой ячейки в моем диапазоне
        Если ячейка.Значение < 0 Тогда
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера

Выделение альтернативных строк

Этот макрос полезен для выделения альтернативных строк.

' Выделить альтернативные строки
ПодсветкаAlternateRows()
    Затемнить ячейку как диапазон
    Dim myRange As Range
    
    мой диапазон = выбор
    
    Для каждой ячейки в myRange.Rows
        Если Не Application.CheckSpelling(Word:=cell.Text), то
            ячейка.Interior.ColorIndex = 36
        Конец, если
    Следующая ячейка
Конец сабвуфера

Выделение пустых ячеек в выделенном фрагменте

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

' Выделить все пустые ячейки в выделении
Подсветка пустых ячеек ()
    Dim rng As Range
    
    Установить rng = Выбор
    rng.SpecialCells(xlCellTypeBlanks).Interior.Color = vbCyan
    
Конец сабвуфера

Примеры макросов Excel VBA — скачать бесплатно

Мы создали бесплатную надстройку VBA (Macros) Code Examples. Надстройка содержит более 100 готовых к использованию примеров макросов, включая приведенные выше примеры макросов!

Часто задаваемые вопросы о макросах Excel/VBA

Как писать код VBA (макросы) в Excel?

Чтобы написать код VBA в Excel, откройте редактор VBA (ALT + F11). Введите «Sub HelloWorld», нажмите Enter, и вы создали макрос! ИЛИ Скопируйте и вставьте одну из процедур, перечисленных на этой странице, в окно кода.

Что такое Excel VBA?

VBA — это язык программирования, используемый для автоматизации Excel.

Как использовать VBA для автоматизации Excel?

Вы используете VBA для автоматизации Excel путем создания макросов.

Как написать программу в экселе

Рассмотрим азы создания программ в VBA Excel.

Как написать простой макрос в программе Excel

Что такое VBA

Объекты, коллекции, свойства и методы

Как начать

Макросы в Excel

Пример 1

Пример 2

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

Работа с макросами, записанными в Excel

Работа с записанным кодом в редакторе Visual Basic (VBE)

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

Работа с макросами, записанными в Excel

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

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

Процедура

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

Процедура

VBA Excel. Начинаем программировать с нуля

Знакомство с редактором VBA

Первая программа на VBA Excel

Работа с переменными

Изменение содержимого ячеек

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

Нужна дополнительная помощь?

Программирование в Excel — пошаговое руководство по программированию на VBA

Содержание

Этапы программирования в Excel VBA

Этап 1. Включение вкладки «Разработчик» в Excel

Этап 2. Запись макроса в Excel

Этап 3. Просмотр и изучение кода VBA, сгенерированного записанным макросом

Этап 4. Проверка кода VBA записанного макроса

Этап 5 — Сохранение записанного макроса (или кода VBA)

Часто задаваемые вопросы

Рекомендуемые статьи

Примеры кода VBA для Эксель

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

Копирование и вставка строки с одного листа на другой

Отправить электронное письмо

Список всех листов в рабочей книге

Показать все рабочие листы

Скрыть все рабочие листы, кроме активного

Снять защиту со всех листов

Защитить все листы

Удалить все фигуры

Удалить все пустые строки в рабочем листе

Выделение повторяющихся значений в выделенном фрагменте

Подсветка отрицательных чисел

Выделение альтернативных строк

Выделение пустых ячеек в выделенном фрагменте

Примеры макросов Excel VBA — скачать бесплатно

Часто задаваемые вопросы о макросах Excel/VBA

Как писать код VBA (макросы) в Excel?

Что такое Excel VBA?

Как использовать VBA для автоматизации Excel?

Добавить комментарий Отменить ответ