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

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

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

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

Для этого потребовалось написать 2 коротеньких алгоритма (макроса):

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

Sub noll()
On Error Resume Next
ActiveSheet.Cells(2, 2).Value = «»
End Sub

Этот макрос следует записать в поле раздела «module» — в «контейнер» модуля.

Второй макрос — активирует первый при клике по ячейке.

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

Private Sub Worksheet_SelectionChange (ByVal Target As Range)
Dim u As String
On Error Resume Next
If Not Intersect(Target, Range(«B1»)) Is Nothing Then
Call noll
End If
End Sub

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

• Кликнуть двойным щелчком по названию листа.
• После двойного клика будет открыто поле для внесения кода аналогичное полю модуля (module).

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
Dim u As String
On Error Resume Next
If Not Intersect(Target, Range(«B1»)) Is Nothing Then
Call noll
End If
End Sub

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

Вопросы и пожелания можете оставлять в комментариях или на форуме нашего сайта.
Видео о работе в excel можно найти на нашем youtube канале по ссылке:

Канал «RuExcel»

Удачи в изучении программы Ecxel.

Запуск макроса кликом по ячейке или при изменении значения ячейки: 2 комментария

Уважаемый Автор! А не мо6ли бы подсказать как выполнить походее аналогичное, что было у вас сейчас, но по значению или ошибке в ячейке чтобы макрос запускался бы сам, вместо измения? Без щелчка по ячейке то есть.
Например, если в ячейке А1 ошибка» #ЗНАЧ» — то автоматически без участия оператора запускается макрос под именем «Макрос2»

И если после максимум двух запусков макроса2 в ячейке А1 по прежнему отображена ошибка «#ЗНАЧ» то уже не запускаем больше и можем вывести msgbox «Запуск макроса не повлиял на ошибку!»:)

Добрый день. Можно запускать макрос по изменению листа private Sub Worksheet_Change(ByVal Target As Range)

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

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

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

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

пробовал вот такой код:

код почему-то не срабатывает. предложите, пожалуйста, рабочий вариант или объясните, почему не работает мой (файл во вложении).

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

пробовал вот такой код:

код почему-то не срабатывает. предложите, пожалуйста, рабочий вариант или объясните, почему не работает мой (файл во вложении).

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

пробовал вот такой код:

код почему-то не срабатывает. предложите, пожалуйста, рабочий вариант или объясните, почему не работает мой (файл во вложении).

спасибо! Автор — Kimezz
Дата добавления — 18.05.2016 в 11:58

А у меня реагирует. Вы в моем файле смотрите или в своем? Смотрите в моем.
Поставьте в Е59 единицу.
Кстати, Вы макросы разрешили при открытии файла?

О! пока писал, Вы уже поправили свой пост выше. Тогда добавка

У Вас макрос PIRR_Search находится именно в модуле с названием «Module1»?
Макрос Private Sub Workbook_SheetChange находится именно в модуле КНИГИ?

А у меня реагирует. Вы в моем файле смотрите или в своем? Смотрите в моем.
Поставьте в Е59 единицу.
Кстати, Вы макросы разрешили при открытии файла?

О! пока писал, Вы уже поправили свой пост выше. Тогда добавка

У Вас макрос PIRR_Search находится именно в модуле с названием «Module1»?
Макрос Private Sub Workbook_SheetChange находится именно в модуле КНИГИ? _Boroda_

Скажи мне, кудесник, любимец ба’гов.
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Сообщение А у меня реагирует. Вы в моем файле смотрите или в своем? Смотрите в моем.
Поставьте в Е59 единицу.
Кстати, Вы макросы разрешили при открытии файла?

О! пока писал, Вы уже поправили свой пост выше. Тогда добавка

У Вас макрос PIRR_Search находится именно в модуле с названием «Module1»?
Макрос Private Sub Workbook_SheetChange находится именно в модуле КНИГИ? Автор — _Boroda_
Дата добавления — 18.05.2016 в 12:22

_Boroda_, да, я все проверил, все по местам. MsgBox успешно запускается, большое спасибо.

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

в приложенном файле теперь есть код PIRR_Search, он довольно простой. с подобной проблемой сталкиваюсь впервые.

может ли это быть связано с тем, что вызываемый макрос PIRR_Search влияет на целевую ячейку, стараясь обратно приравнять её нулю? если да, то как лучше обойти эту нестыковку?

ещё раз спасибо!

_Boroda_, да, я все проверил, все по местам. MsgBox успешно запускается, большое спасибо.

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

в приложенном файле теперь есть код PIRR_Search, он довольно простой. с подобной проблемой сталкиваюсь впервые.

может ли это быть связано с тем, что вызываемый макрос PIRR_Search влияет на целевую ячейку, стараясь обратно приравнять её нулю? если да, то как лучше обойти эту нестыковку?

ещё раз спасибо! Kimezz

Сообщение _Boroda_, да, я все проверил, все по местам. MsgBox успешно запускается, большое спасибо.

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

в приложенном файле теперь есть код PIRR_Search, он довольно простой. с подобной проблемой сталкиваюсь впервые.

может ли это быть связано с тем, что вызываемый макрос PIRR_Search влияет на целевую ячейку, стараясь обратно приравнять её нулю? если да, то как лучше обойти эту нестыковку?

ещё раз спасибо! Автор — Kimezz
Дата добавления — 18.05.2016 в 12:47

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Но это уже лирическое отступление. Нам еще осталось совершить одно действие – изменить первоначальное выражение, которое касалось ячейки D2, на это: ActiveCell.FormulaR1C1 = «=RC[-2]*R[1]C[-2]*R[2]C[-2]»

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

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

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

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

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

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

1. Перейти во вкладку «Разработчик» и нажать на кнопку «Visual Basic». Она находится в самой левой части панели инструментов в этой вкладке.
2. Далее появится окно, которое мы уже знаем по предыдущему разделу.
3. Весь код пишется здесь.

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

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

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

Заключение

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

автоматически выполнять макрос Excel при изменении ячейки

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

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

в котором «H5» отслеживается ли конкретная ячейка и Macro это имя макроса.

Существует ли способ лучше?

Удовлетворяет ли UDF RunMacroWhenValueChanges в FormulaDesk вашим требованиям? Formuladesk.com — Gareth Hayter

5 ответы

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

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

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

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

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

Создан 30 июля ’18, 14:07

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

Для всех, кто пришел сюда из поиска Google, убедитесь, что вы вставили этот код в таблицу в vba, а не в модуль, как я. смотреть на stackoverflow.com/questions/15337008/… — ветчина

Application.ActiveSheet.Range («H5»). ==> target.parent.range («H5») еще безопаснее — пьер

@WillEdiger Всякий раз, когда вы явно не указываете ссылку на лист, Excel предполагает, что ActiveSheet и всякий раз, когда вы явно не указываете, что вы работаете с Excel, Excel предполагает Application . — Скотт Маркус

Обратите внимание, что в модуле кода рабочего листа (где Worksheet_Change событие должно быть локализовано), неквалифицированный Range делает не по умолчанию ActiveSheet но вместо этого ссылается на лист, содержащий код. Таким образом, код в этом ответе фактически совпадает с кодом в вопросе. (Примечание: еще в 2009 году, когда был написан этот ответ, он май были разными, но я почти уверен, что это не так.) — ЙоуE3K

Справиться с Worksheet_Change событие или Workbook_SheetChange мероприятие.

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

Создан 30 июля ’18, 14:07

Спасибо, это работает. Я проверяю диапазон, скажем, 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:

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