Как создать и работать с макросами в ms excel: пошаговая инструкция

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

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

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

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

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

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

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

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

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

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

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

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

1. Перейдите на вкладку «Разработчик».
2. В группе «Код» нажмите кнопку «Запись макроса». Откроется одноименное диалоговое окно.
3. В диалоговом окне «Запись макроса» введите имя для своего макроса, например «ВводТекста». Есть несколько условий именования, которые необходимо соблюдать при назначении макроса. Например, вы не можете использовать пробелы между ними. Обычно я предпочитаю сохранять имена макросов как одно слово, с разными частями с заглавным первым алфавитом. Вы также можете использовать подчеркивание для разделения двух слов — например, «Ввод_текста».
4. Если вы хотите, то можете задать сочетание клавиш. В этом случае мы будем использовать ярлык Ctrl + Shift + N. Помните, что сочетание, которое вы указываете, будет отменять любые существующие горячие клавиши в вашей книге. Например, если вы назначили сочетание Ctrl + S, вы не сможете использовать это для сохранения рабочей книги (вместо этого, каждый раз, когда вы его используете, он выполняет макрос).
5. В поле «Сохранить в» убедитесь, что выбрана опция «Эта книга». Этот шаг гарантирует, что макрос является частью рабочей книги. Он будет там, когда вы сохраните его и снова откроете, или даже если вы поделитесь файлом с кем-то.
6. Введите описание при необходимости. Обычно я этого не делаю, но если у вас много макросов, лучше указать, чтобы в будущем не забыть что делает макрос.
7. Нажмите «ОК». Как только вы нажмете OK, Excel начнет записывать ваши действия. Вы можете увидеть кнопку «Остановить запись» на вкладке «Разработчик», которая указывает, что выполняется запить макроса.
8. Выберите ячейку A2.
9. Введите текст «Excel» (или вы можете использовать свое имя).
10. Нажмите клавишу Enter. Вы попадете на ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

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

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

1. Удалите текст в ячейке A2. Это нужно, чтобы проверить будет ли макрос вставлять текст в ячейку A2 или нет.
2. Выберите любую ячейку — кроме A2. Это нужно проверить, выбирает ли макрос ячейку A2 или нет.
3. Перейдите на вкладку «Разработчик».
4. В группе «Код» нажмите кнопку «Макросы».
5. В диалоговом окне «Макрос» щелкните макрос «ВводТекста».
6. Нажмите кнопку «Выполнить».

Вы увидите, что как только вы нажмете кнопку «Выполнить», текст «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 ВводТекста()
'
' ВводТекста Макрос
'

'
    Range(«A2»).Select
    ActiveCell.FormulaR1C1 = «Excel»
    Range(«A3»).Select
End Sub

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

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

Код начинается с 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, и вы начинаете запись макроса в режиме относительной ссылки.

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

1. Выберите ячейку A1.
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Относительные ссылки». Он будет подсвечиваться, указывая, что он включен.
4. Нажмите кнопку «Запись макроса».
5. В диалоговом окне «Запись макроса» введите имя для своего макроса. Например,  имя «ОтносительныеСсылки».
6. В опции «Сохранить в» выберите «Эта книга».
7. Нажмите «ОК».
8. Выберите ячейку A2.
9. Введите текст «Excel» (или другой как вам нравится).
10. Нажмите клавишу Enter. Курсор переместиться в ячейку A3.
11. Нажмите кнопку «Остановить запись» на вкладке «Разработчик».

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

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

1. Выберите любую ячейку (кроме A1).
2. Перейдите на вкладку «Разработчик».
3. В группе «Код» нажмите кнопку «Макросы».
4. В диалоговом окне «Макрос» кликните на сохраненный макрос «ОтносительныеСсылки».
5. Нажмите кнопку «Выполнить».

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

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

Sub ОтносительныеСсылки()
'
' ОтносительныеСсылки Макрос
'

'
    ActiveCell.Offset(1, 0).Range(«A1»).Select
    ActiveCell.FormulaR1C1 = «Excel»
    ActiveCell.Offset(1, 0).Range(«A1»).Select
End Sub

Обратите внимание, что в коде нет ссылок на ячейки 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, чтобы сохранить этот макрос.

Источник: https://micro-solution.ru/excel/vba/first-macros

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

1689 14.10.2012 Скачать пример

Введение

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

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

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

В принципе, существует великое множество языков программирования (Pascal, Fortran, C++, C#, Java, ASP, PHP…), но для всех программ пакета Microsoft Office стандартом является именно встроенный язык VBA. Команды этого языка понимает любое офисное приложение, будь то Excel, Word, Outlook или Access.

Способ 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).

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

• Обычные модули — используются в большинстве случаев, когда речь идет о макросах. Для создания такого модуля выберите в меню Insert — Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры или копируя их из другого модуля, с этого сайта или еще откуда нибудь:
• Модуль Эта книга — также виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.):
• Модуль листа — доступен через Project Explorer и через контекстное меню листа, т.е. правой кнопкой мыши по ярлычку листа — команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (изменение данных в ячейках, пересчет листа, копирование или удаление листа и т.д.)

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

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

• Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
• Любой макрос должен заканчиваться оператором End Sub.
• Все, что находится между Sub и End Sub — тело макроса, т.е. команды, которые будут выполняться при запуске макроса. В данном случае макрос выделяет ячейку заливает выделенных диапазон (Selection) желтым цветом (код = 6) и затем проходит в цикле по всем ячейкам, заменяя формулы на значения. В конце выводится окно сообщения (MsgBox).

С ходу ясно, что вот так сразу, без предварительной подготовки и опыта в программировании вообще и на 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):

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

1. Кнопка на панели быстрого доступа в Excel 2007 и новее
2. Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна 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):
• После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

Источник: https://www.planetaexcel.ru/techniques/3/59/

Как начать писать макросы в MS Excel 2007 | Планета Решений — ПРОГРАММЫ ДЛЯ ПРОЕКТИРОВАНИЯ

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

      Итак, создадим для примера простейшую программу. Будем использовать MS Excel 2007.  Откройте MS Excel, нажмите «сохранить как» и сохраните файл Вашей программы нажав «Книга ексель с поддержкой макросов». 

      Далее необходимо включить вкладку «Разработчик». Для этого нажимаем «Параметры Excel» 
       Ставим галочку на «Показывать вкладку «Разработчик» на ленте»

       Поставим в ячейки Листа1 следующие значения:

 
       Далее перейдем на Лист1, нажмем на вкладку «Разработчик», «Вставить», на ней выберем кнопку 
и нарисуем кнопку на Листе1, после чего сразу появится окно «Назначить макрос объекту», в котором выбираем «Создать» 
       После этого откроется редактор Visual Basic, и автоматически напишется наименование процедуры, которая будет выполняться при нажатии кнопки. Под названием процедуры впишем следующий код:

• MsgBox («Это мой первый Макрос!») — сообщение
• Переменной q присваивается значение ячейки на Листе1, с координатами 2 строка, 2 столбец
• Переменной w присваивается значение ячейки на Листе1, с координатами 3 строка, 2 столбец
• В ячейку на Листе1, с координатами 4 строка, 2 столбец, записывается сумма q+w

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

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

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

Источник: http://slpl.ru/node/428

Руководство и примеры работы с макросами в Excel — Microsoft Excel для начинающих

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

Макрос – это программа на Visual Basic, созданная для того, чтобы автоматизировать задачи в Microsoft Office. В своих примерах я использую Excel 2010, но с таким же успехом Вы можете использовать Excel 2007.

Создаем макрос при помощи команды «Запись макроса»

1. Для начала откройте вкладку View (Вид) на Ленте. В выпадающем списке Macros (Макросы) нажмите кнопку Record Macro (Запись макроса).Откроется диалоговое окно Record Macro (Запись Макроса).
   
2. Задайте имя макросу (не допускаются пробелы и специальные символы), клавишу быстрого вызова, а также, где бы Вы хотели сохранить свой макрос. При желании, Вы можете добавить описание.
3. С этого момента макрос записывает действия. Например, Вы можете ввести слово “Hello” в ячейку A1.
4. Теперь снова нажмите иконку Macros (Макросы) и в раскрывшемся меню выберите Stop Recording (Остановить запись).

Доступ к записанному макросу можно получить с помощью команды View Macros (Макросы), которая находится на вкладке View (Вид) в выпадающем меню Macros (Макросы). Откроется диалоговое окно Macro (Макрос), в котором Вы сможете выбрать нужный. Дважды кликните по имени макроса, чтобы выполнить программу.

Кроме этого, Вы можете связать макрос с кнопкой. Для этого:

1. На вкладке File (Файл) нажмите Options (Параметры) > Quick Access Toolbar (Панель быстрого доступа).
2. В поле Choose commands from (Выбрать команды из) выберите All Commands (Все команды).
3. Найдите команду Option Button (Кнопка), нам нужна та, что относится к разделу Form Control (Элементы управления формы). Выделите ее и нажмите Add (Добавить). Затем нажмите ОК, чтобы закрыть параметры Excel.
4. Выберите команду, только что добавленную на Панель быстрого доступа, и начертите контур кнопки на рабочем листе Excel.
5. Назначьте макрос объекту.

Примечание: Если у вас включена вкладка Developer (Разработчик), то получить доступ к элементам управления формы можно с нее. Для этого перейдите на вкладку Developer (Разработчик), нажмите на иконку Insert (Вставить) и из раскрывающегося меню выберите нужный элемент.

Не знаете, как отобразить вкладку Developer (Разработчик)? Excel 2007: жмем на кнопку Office > Excel Options (Параметры Excel) > Popular (Основные) и ставим галочку напротив опции Show Developer tab in the Ribbon (Показывать вкладку “Разработчик” на ленте). Excel 2010: жмем по вкладке File (Файл) > Options (Параметры) > Customize Ribbon (Настройка ленты) и в правом списке включаем вкладку Developer (Разработчик).

Цикл FOR

В следующем примере Вы увидите, как использовать цикл FOR. Цикл FOR позволяет нам выполнить повторение цикла с разными значениями. Давайте посмотрим, как можно заполнить числами от 1 до 5 ячейки A1:A5.

Для этого на вкладке Developer (Разработчик) нажмите Visual Basic. Дважды кликните по объекту из списка Microsoft Excel Objects, в котором должен быть сохранён макрос. Введите вот такой код:

Sub Macro1 ()
For n = 1 To 5
Cells(n, 1) = n
Next n
End Sub

Сохраните файл. Чтобы выполнить макрос, перейдите View > Macros > View Macros (Вид > Макросы > Макросы), выберите из списка название нужного макроса и нажмите Run (Выполнить).

Следующий код отображает фразу “Hello World” в окне сообщений Windows.

Sub MacroName()
MsgBox («Hello World!»)
End Sub

В следующем примере мы создаём сообщение с выбором Yes (Да) или No (Нет). Если выбрать вариант Yes (Да), то значение ячейки будет удалено.

Sub MacroName()
Dim Answer As String

Answer = MsgBox(«Are you sure you want to delete the cell values ?»,
vbQuestion + vbYesNo, «Delete cell»)

If Answer = vbYes Then
ActiveCell.ClearContents
End If
End Sub

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

Если Вы нажмёте Yes (Да), значение в выделенной ячейке будет удалено. А если No (Нет) – значение сохранится.

Конструкция IF

В Microsoft Excel Вы также можете использовать конструкцию IF. В этом коде мы будем раскрашивать ячейки в зависимости от их значения. Если значение в ячейке больше 20, то шрифт станет красным, иначе – синим.

Sub MacroName()
Dim CellValue As Integer
CellValue = ActiveCell.Value

If CellValue > 20 Then
With Selection.Font
.Color = -16776961
End With
Else
With Selection.Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0
End With
End If
End Sub

Для проверки этого кода выберем ячейку со значением больше 20:

Когда Вы запустите макрос, цвет шрифта изменится на красный:

При выполнении второго условия шрифт станет синим:

Конструкция CASE

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

Sub MacroName()
Dim CellValue As Integer
CellValue = ActiveCell.Value

Select Case CellValue
Case 60 To 200
MsgBox «The person is old»

Case 30 To 59
MsgBox «The person is adult»

Case 18 To 29
MsgBox «The person is young»

Case 0 To 17
MsgBox «The person is a child»

Case Else
MsgBox «Unknown age»

End Select
End Sub

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

Заключение

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

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/rukovodstvo-i-primery-raboty-s-makrosami-v-excel-210.html

Как работать с макросами в Excel 2010 без программирования кода

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

Вам даже не нужно быть программистом и знать язык программирования «VBA» чтобы создавать свои макро-программы с помощью инструмента записи макросов.

Сначала надо включить панель разработчика. Для этого в меню «Файл» открываем группу опций «Параметры». В появившемся окне «Параметры Excel» открываем группу «Настройка ленты». Обратите внимание на правую колонку настроек под аналогичным названием «Настройка ленты». В ней следует отметить галочкой опцию «Разработчик» как показано ниже на рисунке:

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

Применение VBA и макросов в Microsoft Excel

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

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

Как записать готовый макрос в Excel? Очень просто:

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



2. В появившимся диалоговом окне заполняем параметры макроса. И нажимаем «ОК».



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



4. Для выполнения или редактирования записанного макроса нажимаем на кнопку «Макросы» (или комбинацию клавиш ALT+F8). Появится окно со списком записанных макросов и кнопками для управления ими.

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



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

1 Правильные имена в макросах

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

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

2 Используйте относительные (не абсолютные) адреса ячеек

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

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

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

По умолчанию в Excel включен режим «Абсолют», но вы можете изменить его, включив кнопку «Относительные ссылки» расположенную ниже под кнопкой «Запись макроса» на панели инструментов вкладки «Разработчик»:

3 Всегда начинайте запись с курсором в A1

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

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

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

4 Всегда перемещаться с клавиш направления в момент записи макроса

Используйте кнопки со стрелками для управления курсором (Ctrl + Up, и т.п.). Позиционируйте курсор, так чтобы вы могли добавить, изменить или удалить данные внутри таблицы по мере необходимости.

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

5 Создавайте макросы для конкретных небольших задач

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

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

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

Источник: https://exceltable.com/vba-macros/rabota-s-makrosami-v-excel

Работа с макросами в Excel

Макрос – это специальная программа, написанная на встроенном в Excel языке программирования Visual Basic for Application (VBA). Если вам часто приходится выполнять одни и те же операции в Эксель, то гораздо проще записать макрос или найти готовый код макроса, для выполнения определенных действий в Интернете.

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

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

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

В окне «Параметры Excel» перейдите на вкладку «Настройка ленты», теперь в правой части окна поставьте галочку напротив пункта «Разработчик» и нажмите «ОК».

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

Теперь разрешим использование всех макросов. Снова открываем «Файл» – «Параметры». Переходим на вкладку «Центр управления безопасностью», и в правой части окна кликаем по кнопочке «Параметры центра управления безопасностью».

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

Как вставить макрос в Excel

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

Вставим код макроса в модуль

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

Открываем редактор VBА: переходим на вкладку «Разработчик» и кликаем по кнопочке «Visual Basic»,   также можно воспользоваться комбинацией «Alt+F11».

Кликаем по выбранному пункту правой кнопкой мыши и выбираем из меню «Insert» – «Module».

В окне «Project» появилась новая папка «Modules», а в ней наш первый модуль с названием «Module1».

Справа появится поле для ввода VBA-кода. С помощью комбинации «Ctrl+V», вставляю в него код, который будет объединять несколько ячеек, без потери данных. Название макроса «MergeCell».

Следите за тем, куда Вы вставляете код, это будет написано или в заголовке редактора, или в заголовке окна для вставки кода. Мы вставляем код в модуль, соответственно надпись – «Modul1(Code)».

Сохраняем изменения, нажав комбинацию «Ctrl+S». Если Вы используете Excel 2007 и выше, появится окно сохранения документа. В нем, в поле «Тип файла», выберите из выпадающего списка «Книга Excel с поддержкой макросов» и нажмите «Сохранить».

Вставим код макроса в рабочий лист

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

Открываем редактор VBA и в окошке «Project» выделяем нужный лист, если их в рабочей книге несколько, на котором должен работать макрос: «Лист1 (Лист1)». Кликаем по нему два раза мышкой.

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

Создадим пользовательскую функцию

Например, создадим функцию, которая будет рассчитывать НДС. В окне «Project» кликаем по рабочему листу правой кнопкой мыши и выбираем «Insert» – «Module».

В папке «Modules», появляется второй модуль с названием «Module2». Прописываем VBA-код   в соответствующее окно, и сохраняем его, нажав «Ctrl+S». Называться функция будет «NDS».

Как запустить макросы в Excel

Теперь рассмотрим, как выполняются все созданные ранее макросы в документе Excel.

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

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

Откроется диалоговое окно «Макрос». В списке, выбираем нужное имя макроса: «MergeCell», и жмем «Выполнить».

Макрос выполнился: нужные ячейки объединены, и текст в них сохранен.

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

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

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

Если Вы создали пользовательскую функцию, то вставлять в документ ее также надо как функцию. У меня такая функция должна рассчитывать НДС.

Откроется окно «Мастер функций». В поле «Категория» выбираем из выпадающего списка «Определенные пользователем», в поле «Выберите функцию» выбираем название из списка: «NDS». Нажимаем «ОК».

Затем выделяю ячейку, для которой нужно посчитать НДС, и нажимаю «ОК».

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

(1

Источник: http://comp-profi.com/rabota-s-makrosami-v-excel/