Как сделать чтобы макрос в excel выполнялся автоматически при изменении значений ячеек

Обновлено: 05.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Как я могу автоматически запускать макрос Excel каждый раз, когда изменяется значение в определенной ячейке?

Прямо сейчас мой рабочий код:

где "H5" - конкретная отслеживаемая ячейка и Macro имя макроса.

Есть ли способ лучше?

Ваш код выглядит неплохо.

Однако будьте осторожны, потому что ваш вызов Range("H5") - это команда быстрого доступа Application.Range("H5") , которая эквивалентна Application.ActiveSheet.Range("H5") . Это может быть хорошо, если единственными изменениями являются изменения пользователя, что является наиболее типичным, но значения ячеек рабочего листа могут измениться, когда он не является активным, посредством программных изменений, например VBA.

Имея это в виду, я бы использовал Target.Worksheet.Range("H5") :

Или вы можете использовать Me.Range("H5") , если обработчик событий находится на кодовой странице для рассматриваемого рабочего листа (обычно это так):

Надеюсь это поможет.

что, если ячейка H5 изменена с другого листа, допустим, указанная sheet2 выше функция не работает. Помогите плз в этом. Application.ActiveSheet.Range ("H5"). ==> target.parent.range ("H5") еще безопаснее @WillEdiger Всякий раз, когда вы явно не указываете ссылку на лист, Excel предполагает, ActiveSheet и всякий раз, когда вы явно не указываете, что вы работаете с Excel, предполагает Excel Application . Следует отметить, что в модуле рабочего листа кода (который где Worksheet_Change событие должно быть расположено), неквалифицированный Range вовсе не по умолчанию , ActiveSheet но вместо этого относится к листу , содержащий код. Таким образом, код в этом ответе фактически совпадает с кодом в вопросе. (Примечание: еще в 2009 году, когда был написан этот ответ, он мог быть другим, но я почти уверен, что это не так.)

Обработайте Worksheet_Change событие или Workbook_SheetChange событие.

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

Спасибо, это работает. Я проверяю диапазон, скажем, с Target.Address = Range("H5").Address . Есть способ попроще? Альтернатива: Not (Intersect(Target, Range("H5")) Is Nothing) . Как бы вы это сделали? Первый комментарий ( Target.Address = Range("H5").Address ) не сработал бы, если бы ваша ячейка была только частью измененного диапазона. Второй комментарий по-прежнему страдает проблемами, описанными Майком Розенблюмом.

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

1) Откройте редактор VBA, в разделе VBA Project (YourWorkBookName.xlsm) откройте объект Microsoft Excel и выберите лист, к которому будет относиться событие изменения.

2) Вид кода по умолчанию - «Общее». В раскрывающемся списке вверху по центру выберите «Рабочий лист».

3) Private Sub Worksheet_SelectionChange уже там, как и должно быть, оставьте его в покое. Скопируйте / вставьте приведенный выше код Майка Розенблюма и измените ссылку .Range на ячейку, для которой вы наблюдаете изменение (B3, в моем случае). Однако пока не размещайте свой макрос (я убрал слово «Макрос» после «Тогда»):

или из раскрывающегося списка в левом верхнем углу выберите «Изменить» и в пространство между частным подпиской и конечной подпиской вставьте If Not Intersect(Target, Me.Range("H5")) Is Nothing Then

4) В строке после «Then» отключите события, чтобы при вызове макроса он не запускал события и не пытался запустить этот Worksheet_Change снова в бесконечном цикле, который приводит к сбою Excel и / или иным образом все портит:

5) Назовите свой макрос

6) Включите события, чтобы сработало следующее изменение (и любые / все другие события):

7) Завершите блок If и Sub:

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

Автоматический запуск макроса при изменении значений в ячейке

Весь MS Office, программирование на Visual Basic for Applications и MS VB
Здравствуйте.
Помогите с проблемой, плиз.
Есть макрос который сравнивает и форматирует ячейки таблицы.
Необходимо автоматический запуск данного макроса при изменении любой ячейки в таблице. Теперь, при изменении значения любой из ячеек диапазона "A1:F10" будет запущен макрос "MyMacro".
Будьте внимательны! Если Ваш макрос будет работать с ячейками таблицы, то в начале процедуры необходимо отключить обработчик событий командой Application.EnableEvents = False. По окончании процедуры, естественно, включить его Application.EnableEvents = True. SAS888 писал(а): В модуль требуемого листа поместите код: Теперь, при изменении значения любой из ячеек диапазона "A1:F10" будет запущен макрос "MyMacro".
Будьте внимательны! Если Ваш макрос будет работать с ячейками таблицы, то в начале процедуры необходимо отключить обработчик событий командой Application.EnableEvents = False. По окончании процедуры, естественно, включить его Application.EnableEvents = True.

я что-то так и не понял как делать, я уж и так и эдак вставлял не получается. Может кто-нибудь сможет видоизменить мой макрос, чтобы он запускался при внесении числа в ячейку, то есть если в одном листе excel-евского файла в какой-то ячейке ввести число например 543 то макрос переходит не визуально, а в памяти, на другой лист файла, а там таблица из нескольких столбцов с числами, так вот макрос находит в определенном солбце это (543) число, далее сравнивает рядом с ним стоящее в одной строке число с нулем, если оно меньше нуля то макрос возвращает в ту ячейку куда вводили число 543 значение того отрицательного числа которое стоит рядом в одной строке с числом 543. Ну а если число которое стоит в таблице рядом с 543 больше нуля то макрос ни чего не делает, а оператор вводит данные в другую ячейку. Ну в общем-то как сравнивать числа и выводить число в какую-то ячейку с этим я сам справлюсь, а вот как написать чтобы макрос начинал работу после введения данных в ячейку - вот это я не знаю. Кто знает подскажите пожалуйста?

Sub сравнение()
'
' сравнение Макрос
' Макрос записан 13.02.2010 (ккк)
'
' Сочетание клавиш: Ctrl+s
'
Dim a As Integer ' - это номер строки в таблице где происходит сравнение
Dim n As Integer ' - это переменная которой присваивается значение числа после того как его ввели
Dim x As Integer ' - это номер строки на которой находиться ячейка в которую вводиться число
Dim y As Integer ' - это номер столбца в котором находиться ячейка в которую вводиться число

a = 1 'задается номер строки с которой начинаем

' ВОТ ТУТ КАК РАЗ В МЕСТО ЭТИХ ТРЕХ СТРОЧЕК И ДОЛЖНА ПО ИДЕЕ БЫТЬ КОМАНДА
' КОТОРАЯ ЗАПУСКАЕТ МАКРОС ПОСЛЕ ВВОДА ЧИСЛА В ЯЧЕЙКУ
' Range("B2").Select
' ActiveCell.FormulaR1C1 = "543"
' n = введенному в ячейку числу

x = 2 ' для того чтобы макрос хоть как то работал задаем умышленно фиксированное значение
y = 2 ' для того чтобы макрос хоть как то работал задаем умышленно фиксированное значение
n = Sheets("лист-данные").Cells(x, y) 'присваиваем переменной n значение введенного в ячейку числа
2 If n = Sheets("лист-таблица").Cells(a, 1) Then GoTo 1 ' находим такое же число как оператор вводил но уже в эталонной таблице
a = a + 1
If a <= 50 Then GoTo 2
1 If Sheets("лист-таблица").Cells(a, 2) > 0 Then GoTo 3 ' Число найдено, а сдесь выясняем отрицательное ли число которое стоит рядом в эталонной таблице

Sheets("лист-данные").Cells(x, y) = Sheets("лист-таблица").Cells(a, 2) 'присваивается здачение отрицательного числа ячейке в которую изначально был ввод
Sheets("лист-данные").Select 'ну тут ниже меняетс цвет шриф для яркости
Cells(x, y).Select
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial Cyr"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 3
End With
pause (20 = cek) ' то же не знаю как писать паузу в работе, а она нужна чтобы оператор увидел результат
4 r = r + 1
If r < 50000000 Then GoTo 4 ' пришлось вместо паузы вот такой ерундой заниматься
Selection.ClearContents 'очистка ячейки куда был ввод
Selection.Font.ColorIndex = 1

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вот шаги по открытию редактора VB в Excel:

Перейдите на вкладку "Разработчик".
В группе "Код" нажмите кнопку "Visual Basic".

Вы также можете использовать комбинацию клавиш Alt + F11 и перейти в редактор кода VBA.

Рассмотрим сам редактор кода. Далее коротко опишем интерфейс редактора.

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

Когда мы записали макрос "ВводТекста", в редакторе VB произошли следующие вещи:

Был добавлен новый модуль.
Макрос был записан с именем, которое мы указали - "ВводТекста"
В окне кода добавлена новая процедура.

Поэтому, если вы дважды щелкните по модулю (в нашем случае модуль 1), появится окно кода, как показано ниже.

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

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

Код начинается с Sub, за которым следует имя макроса и пустые круглые скобки. Sub - сокращение для подпрограммы. Каждая подпрограмма (также называемая процедурой) в VBA начинается с Sub и заканчивается End Sub.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Расширение файлов Excel, которые содержат макросы

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

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

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

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