Как создавать макросы в excel. VBA Excel

Когда все готово, приступаем к записи.

Запуск макроса

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

Редактирование макроса

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

1. Снова щелкаем на кнопку «Макросы» . В открывшемся окне выбираем нужный и кликаем по кнопке «Изменить» .



2. Открывается «Microsoft Visual Basic» (VBE) – среда, где происходит их редактирование.



3. Запись каждого макроса начинается с команды Sub , а заканчивается командой End Sub . Сразу же после Sub указывается имя макроса. Оператор Range("…").Select указывает выбор ячейки. Например, при команде «Range(«C4»).Select» выбирается ячейка «C4» . Оператор ActiveCell.FormulaR1C1 используется для записи действий в формулах и других расчетов.
4. Попытаемся немного изменить макрос, дописав выражение:

   Range("C3").Select
   ActiveCell.FormulaR1C1 = "11"

5. Выражение ActiveCell.FormulaR1C1 = "=R[-3]C+R[-2]C+R[-1]C" заменим на ActiveCell.FormulaR1C1 = "= R[-4]C+R[-3]C+R[-2]C+R[-1]C" .



6. Закрываем редактор и запускаем макрос. Как видим, вследствие введенных нами изменений была добавлена дополнительная ячейка с данными. Она также была включена в расчет общей суммы.
7. В случае если макрос слишком большой, его выполнение может занять значительное время, но внесением ручного изменения в код мы можем ускорить процесс. Добавляем команду Application.ScreenUpdating = False . Она позволит сохранить вычислительные мощности, а значит, ускорить работу. Это достигается путем отказа от обновления экрана во время выполнения вычислительных действий. Чтобы возобновить обновление после выполнения макроса, в его конце пишем команду Application.ScreenUpdating = True .
8. Добавим также команду Application.Calculation = xlCalculationManual в начало кода, а в его конец дописываем Application.Calculation = xlCalculationAutomatic . Этим мы сначала отключаем автоматический пересчет результата после каждого изменения ячеек, а в конце макроса – включаем. Таким образом, Excel подсчитает результат только один раз, а не будет его постоянно пересчитывать, чем сэкономит время.



Вариант 2: Написание кода макроса с нуля

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




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

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

Написание макросов в Excel

Код макроса Excel написанный на языке Visual Basic for Application (VBA), а его выполняет инструмент приложения, к которому он присоединен. Большинство этих инструментов не доступно на уровне окна программы Excel. Как написать макрос.

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

Чтобы написать макрос:

Примечание. Если в главном меню отсутствует закладка «РАЗРАБОТЧИК», тогда ее необходимо активировать в настройках: «ФАЙЛ»-«Параметры»-«Настроить ленту». В правом списке «Основные вкладки:» активируйте галочкой опцию «Разработчик» и нажмите на кнопку ОК.



Возможности макросов в Excel

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

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

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

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

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

Макрос в «Эксель» — небольшая программка (скрипт) написанная на языке VBA (Visual Basic for Applications) разновидности языка Basic (Бейсик).

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

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

Как создать простой макрос.

Создавать макросы в Excel можно двумя способами:

• При помощи опции «Запись макроса»;
• Написать макрос на языке VBA в редакторе макросов и применить его к документу.

Самый простой способ «Запись макроса», его и рассмотрим.

Для начала необходимо включить вкладку «Разработчик», если она не включена.

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

После того, как включили панель разработчика можно начинать запись макроса.

Чтобы записать макрос, следует:

— при сохранении в «Эта книга» макрос будет работать только в текущем документе;

— при сохранении в «Личная книга» макрос будет работать во всех документах на Вашем компьютере.

1. Можно добавить описание макроса, оно поможет Вам вспомнить, какие действия совершает макрос.
2. Нажать «Ок».
3. Если вы не указали сочетание клавиш, запись начнется сразу после нажатия кнопки «Ок».
4. Когда идет запись, Вы должны совершать требуемую последовательность действий.
5. Когда закончите, нажимайте кнопку остановить запись.

Записанные макросы отображаются в книге макросов.

Чтобы их посмотреть следует нажать кнопку «макросы». В появившемся окне появится список макросов. Выберете нужный макрос и нажмите «Выполнить».

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

Попробуем отредактировать макрос.

Например, продолжить его еще на несколько ячеек.

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

Также как и в других приложениях Microsoft Office , есть такая интересная штука как макрос. Если в двух словах, то макрос – это программируемая последовательность действий. Создать его можно двумя способами:

1. С помощью соответствующего пункта меню;

2. Вручную.

Первый способ очень простой и не требует специальных навыков программирования. Но прежде чем приступать к созданию макроса, необходимо поковырять настройки безопасности. Заходим в Файл/Параметры/Центр управления безопасностью/Параметры центра управления безопасностью/Параметры макросов . Здесь необходимо выбрать Включить все макросы .

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

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

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

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

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

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

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

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

Макрос может быть создан двумя различными способами:

• Использование Macro Recorder: В этой процедуре Excel сохраняет каждое действие, которое будет частью макроса, а затем средство записи макросов преобразует эти действия в команды Visual Basic для приложений (VBA).
• Использование VBA: Если вы немного разбираетесь в программировании или даже владеете языком VBA, вы можете создавать свои макросы самостоятельно. Для этого просто используйте редактор VBA, присутствующий в последних версиях Microsoft Excel.

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

1) Показывать сообщение

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

MsgBox "текст сообщения"

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

2) Запустите макрос при открытии листа

В некоторых случаях макрос необходимо открывать вместе с рабочим листом. В этом случае просто смонтируйте макрос с параметром Auto_Open (). Проверьте пример

Sub Auto_Open ()
MsgBox "Чтобы узнать все о Excel, перейдите на страницу www.https: //luz.vc/"
End Sub

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

3) Рабочий лист с текущей датой и временем

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

Sub writeDataEora ()
Диапазон ("A1") = Теперь
End Sub

4) Выполните одно и то же действие для каждой выбранной ячейки.

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

Sub to doSpeedCell ()
Для каждой ячейки в Selection.Cells
Ячейка MsgBox
Следующяя
End Sub

5) То же самое на всех выделенных ячейках

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

Sub to doSomethingAllAsCells ()
Selection.Cells.Value = "Привет"
End Sub

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

6) Идентификация формул в каждой ячейке

Sub CheckFormula ()
Если Range ("A1") HasFormula = True, тогда
MsgBox «Есть Формула»
Еще
MsgBox "Не формула"
End If
End Sub

7) Изменить цвет ячейки при наведении мыши

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

Private Sub Worksheet_SelectionChange (ByVal Target As Range)

Димская домашняя линия как диапазон
Дальность
Dim Line2 как длинный

Cells.Interior.ColorIndex = xlNone

Line2 = Target.Row

Set Line Start = Range («A» и Line2, Target)

Закрашивает выделенную ячейку в столбце 5
Set Line = Range (ячейки (Target.Row, 1), ячейки (Target.Row, 5))

С линейкой
.Interior.ColorIndex = 12
Конец с

8) Изменение цвета внутри и шрифтов

Этот макрос изменяет цвета внутри и у источника ячеек в соответствии с буквой ячеек.

Sub Colorir_interior_letra ()
Для N = 1 до диапазона («O65536»). End (xlUp) .Row

Выберите диапазон шкалы («O» и N)
Случай "А"
Диапазон («O» и N) .Interior.ColorIndex = 3
Диапазон («O» и N) .Font.ColorIndex = 1

Случай "B"
Диапазон («O» и N) .Interior.ColorIndex = 4
Диапазон («O» и N) .Font.ColorIndex = 2

Случай "C"
Диапазон («O» и N) .Interior.ColorIndex = 5
Диапазон («O» и N) .Font.ColorIndex = 3

Случай "D"
Диапазон («O» и N) .Interior.ColorIndex = 7
Диапазон («O» и N) .Font.ColorIndex = 12

Case Else
Диапазон («O» и N) .Interior.ColorIndex = 6
Диапазон («O» и N) .Font.ColorIndex = 4
End Select

Следующий N

9) Говорящий макрос

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

Sub ExcelFalling ()

Диапазон («A1: A5»).