Остановить выполнение макроса VBA
Возможно ли как-то остановить выполнение макроса без завершения процесса, если он, допустим, зашел в бесконечный цикл и вызвал зависание всего Excel?
Отслеживать
16.6k 8 8 золотых знаков 26 26 серебряных знаков 56 56 бронзовых знаков
задан 12 сен 2017 в 20:02
717 11 11 серебряных знаков 31 31 бронзовый знак
Без кода как-то не очень ясна ситуация и условия в которых возникает бесконечный цикл или вы говорите о любых вариантах когда нужна «серебряная пуля»?
13 сен 2017 в 3:16
Можно в цикле использовать DoEvents, тогда думаю его можно остановить без особых усилий. Если конечно речь об этом.
13 сен 2017 в 3:19
Для принудительной ручной остановки VBA-кода используется сочетание клавиш Ctrl-Break. Однако если в коде нет DoEvents, можно весьма долго ждать, пока это сочетание пробьётся к дебаггеру.
13 сен 2017 в 4:38
При написании/отладке DoEvents. Но в рабочем коде не должно быть возможности зацикливания. Наgример, проверка значения переменной: If bf=True Then Exit For
13 сен 2017 в 11:29
1 ответ 1
Сортировка: Сброс на вариант по умолчанию
Один из вариантов контроля бесконечного цикла это контроль по времени. Т.е. задаем максимально допустимый интервал времени по истечение которого происходит выход из цикла. Например.
TimeInterval = 25 'секунд t1 = Timer ' задаем начальное время Do ' бесконечный цикл tElepsed = Timer - t1 If tElapsed > TimeInterval Then Exit Do End if 'тело цикла ' Loop Как прервать работу зацикленного макроса, если CTRL/Break не «того»?
Привет. Макрос (видимо) зациклился и колбасит уже час вместо положеных 10 минут. Ни на что не реагирует, собственно. Хотя параллельно могу открывать другие книги ексель для работы.
Я так понимаю, что теперь тольуко принудительное закрытие книги?
вопрос: можно ли в макрос засунуть какую-то инструкцию, которая будет заставлять макрос реагировать на контрол брейк? или как keit предупредить подобные подвисания?
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Как прервать работу макроса, если он не откликается на Ctrl+Break?
здравствуйте иногда после нажатия Run когда очень срочно нужно прервать работу макроса я делаю -.
Как сделать так, чтобы работу макроса нельзя было прервать с помощью Ctrl+Break и других комбинаций?
Добрый день, известно, что работу мкроса можно остановить, например, ctrl+break/pause. Возможно и.
Исчезла кнопка Пауза (Прервать все/ Ctrl+Break)
Уважаемые знатоки! Исчезла возможность, во время отладки поставить на паузу отладку. При нажатии.
6919 / 2829 / 543
Регистрация: 19.10.2012
Сообщений: 8,645
Сообщение от AAAAAAAmidi 
можно ли в макрос засунуть какую-то инструкцию, которая будет заставлять макрос реагировать
DoEvents
Это вообще полезно в длинных циклах.
Добавлено через 6 минут
Вот кстати нашёл в закромах:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
============================================================== имеется макрос (см ниже), который нормально работает, но остановить работу макроса получается только кнопкой Reset в редакторе VB или путем закрытия файла. Подскажите как его можно выключить? Const ИмяФайла7 = "C:\Alex.txt" Const ИмяФайла8 = "C:\Alex1.txt" Public РазмерФайла7 As Long, РазмерФайла8 As Long, ПоискИзмененийВременноОтключён As Boolean Const ВременнойИнтервалМеждуПроверками = 2 Public Sub СлежениеЗаФайлом() On Error Resume Next Do While True ' бесконечный цикл If Not ПоискИзмененийВременноОтключён Then If ПоискИзмененийВременноОтключён Then Exit Sub НовыйРазмерФайла7 = CreateObject("scripting.filesystemo bject").GetFile(ИмяФайла7).Size If НовыйРазмерФайла7 > РазмерФайла7 Then DoFile2 (ИмяФайла7): РазмерФайла7 = НовыйРазмерФайла7 НовыйРазмерФайла8 = CreateObject("scripting.filesystemo bject").GetFile(ИмяФайла8).Size If НовыйРазмерФайла8 > РазмерФайла8 Then DoFile1 (ИмяФайла8): РазмерФайла8 = НовыйРазмерФайла8 End If t = Timer: While t + ВременнойИнтервалМеждуПроверками > Timer: DoEvents: Wend Loop End Sub Я же вам уже писал, как это сделать: Код: Sub НазначьтеЭтотМакросНаКнопкуОстановки() ПоискИзмененийВременноОтключён = True End Sub ==============================================================
Как остановить бесконечный цикл vba
Сообщений: 257 Регистрация: 02.06.2013
28.10.2016 16:55:10
Добрый вечер!
Не могли бы Вы подсказать, где в коде кроется ошибка..
Необходимо, что при ответа Да, выходило сообщение (это работает). А при ответе «Нет» появлялся текст vbCritical, а затем вопрос повторялся заново, где снова будет предлагаться ответить Да или Нет. То есть цикл завершить можно чтоб было только при ответе Да.
В нынешнем коде при ответе «Нет» Excel виснит, только Ctrl+Alt+Delete помогает закрыть файл.
Цикл Do. Loop не совсем верно прописан по всей видимости.
Спасибо.
Sub Pub() Dim iPivasik As Integer iPivasik = MsgBox(". ", vbYesNo, ". ") Do If iPivasik = vbYes Then MsgBox (". ") Exit Do ElseIf iPivasik = vbNo Then MsgBox (". "), vbCritical End If Loop While 1 = 1 End Sub
Пользователь
Сообщений: 23755 Регистрация: 22.12.2012
28.10.2016 17:01:14
Так спрашивать нужно вОвремя и в правильном месте
Сами же сказали: «а затем вопрос повторялся заново» — и где это?
Передвиньте Do до вопроса.
Изменено: Hugo — 28.10.2016 17:05:26
Пользователь
Сообщений: 2366 Регистрация: 03.04.2015
Excel 2010 +PLEX +SaveToDB +PowerQuery
28.10.2016 17:13:38
| Цитата |
|---|
| stevie44 написал: В нынешнем коде при ответе «Нет» Excel виснит, |
попробуйте
Sub Pub() Dim iPivasik As Integer Do iPivasik = MsgBox(". ", vbYesNo, ". ") If iPivasik = vbYes Then MsgBox (". ") Exit Do ElseIf iPivasik = vbNo Then MsgBox (". "), vbCritical End If Loop While 1 = 1 End Sub
Неизлечимых болезней нет, есть неизлечимые люди.
Поиск на листе Excel
Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.
Поиск перебором значений
Довольно простой в реализации способ. Например, найти в колонке «A» ячейку, содержащую «123» можно примерно так:
Sheets("Данные").Select For y = 1 To Cells.SpecialCells(xlLastCell).Row If Cells(y, 1) = "123" Then Exit For End If Next y MsgBox "Нашел в строке: " + CStr(y)
Минусами этого так сказать «классического» способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую «123» достаточно такого кода:
Sheets("Данные").Select Set fcell = Columns("A:A").Find("123") If Not fcell Is Nothing Then MsgBox "Нашел в строке: " + CStr(fcell.Row) End If
Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист «Данные»;
2-я строка: Осуществляем поиск значения «123» в колонке «A», результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае — будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What — Строка с текстом, который ищем или любой другой тип данных Excel
After — Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn — Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt — Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder — Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection — Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase — Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte — Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat — Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать «далее») или FindPrevious (искать «назад»).
Примеры поиска функцией Find
Пример 1: Найти в диапазоне «A1:A50» все ячейки с текстом «asd» и поменять их все на «qwe»
With Worksheets(1).Range("A1:A50") Set c = .Find("asd", LookIn:=xlValues) Do While Not c Is Nothing c.Value = "qwe" Set c = .FindNext(c) Loop End With
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
With Worksheets(1).Range("A1:A50") Set c = .Find("asd", lookin:=xlValues) If Not c Is Nothing Then firstResult = c.Address Do c.Font.Bold = True Set c = .FindNext(c) If c Is Nothing Then Exit Do Loop While c.Address <> firstResult End If End With
В ниже следующем примере используется другой вариант продолжения поиска — с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
With Worksheets(1).Range("A1:A50") Set c = .Find("asd", lookin:=xlValues) If Not c Is Nothing Then firstResult = c.Address Do c.Font.Bold = True Set c = .Find("asd", After:=c, lookin:=xlValues) If c Is Nothing Then Exit Do Loop While c.Address <> firstResult End If End With
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом «курсив» и поменять их формат на обычный (не «курсив»)
lLastRow = Cells.SpecialCells(xlLastCell).Row lLastCol = Cells.SpecialCells(xlLastCell).Column Application.FindFormat.Font.Italic = True With Worksheets(1).Range(Cells(1, 1), Cells(lLastRow, lLastCol)) Set c = .Find("", SearchFormat:=True) Do While Not c Is Nothing c.Font.Italic = False Set c = .Find("", After:=c, SearchFormat:=True) Loop End With
Примечание: В данном примере намеренно не используется FindNext для поиска следующей ячейки, т.к. он не учитывает формат (статья об этом: https://support.microsoft.com/ru-ru/kb/282151)
Коротко опишу алгоритм поиска Примера 4. Первые две строки определяют последнюю строку (lLastRow) на листе и последний столбец (lLastCol). 3-я строка задает формат поиска, в данном случае, будем искать ячейки с шрифтом Italic. 4-я строка определяет область ячеек с которой будет работать программа (с ячейки A1 и до последней строки и последнего столбца). 5-я строка осуществляет поиск с использованием SearchFormat. 6-я строка — цикл пока результат поиска не будет пустым. 7-я строка — меняем шрифт на обычный (не курсив), 8-я строка продолжаем поиск после найденной ячейки.
Хочу обратить внимание на то, что в этом примере я не стал использовать «защиту от зацикливания», как в Примерах 2 и 3, т.к. шрифт меняется и после «прохождения» по всем ячейкам, больше не останется ни одной ячейки с курсивом.
Свойство FindFormat можно задавать разными способами, например, так:
With Application.FindFormat.Font .Name = "Arial" .FontStyle = "Regular" .Size = 10 End With
Поиск последней заполненной ячейки с помощью Find
Следующий пример — применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
Set c = Worksheets(1).UsedRange.Find("*", SearchDirection:=xlPrevious) If Not c Is Nothing Then lLastRow = c.Row: lLastCol = c.Column Else lLastRow = 1: lLastCol = 1 End If MsgBox "lLastRow=" & lLastRow & " lLastCol т", при этом после этого слова может следовать любой текст. With Worksheets(1).Cells Set c = .Find("т??т*", LookIn:=xlValues, LookAt:=xlWhole) If Not c Is Nothing Then firstResult = c.Address Do c.Font.Color = RGB(255, 0, 0) Set c = .FindNext(c) If c Is Nothing Then Exit Do Loop While c.Address <> firstResult End If End With
Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;
~ - для обозначения символов *, ? и ~. (т.е. чтобы искать в тексте вопросительный знак, нужно написать ~?, чтобы искать именно звездочку (*), нужно написать ~* и наконец, чтобы найти в тексте тильду, необходимо написать ~~)
Поиск в скрытых строках и столбцах
Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
- Тип данных Date в VBA представляется в виде #[месяц]/[день]/[год]#, соответственно, если необходимо найти фиксированную дату, например, 01 марта 2018 года, необходимо искать #3/1/2018#, а не "01.03.2018"
- В зависимости от формата ячеек, дата может выглядеть по-разному, поэтому, чтобы искать дату независимо от формата, поиск нужно делать не в значениях, а в формулах, т.е. использовать LookIn:=xlFormulas
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
d = Date Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole) If Not c Is Nothing Then MsgBox "Нашел" Else MsgBox "Не нашел" End If
Пример 8: Найти 1 марта 2018 г.
d = #3/1/2018# Set c = Cells.Find(d, LookIn:=xlFormulas, LookAt:=xlWhole) If Not c Is Nothing Then MsgBox "Нашел" Else MsgBox "Не нашел" End If
Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
d = #3/1/1900# Set c = Cells.Find(Format(d, "m\/d\/"), LookIn:=xlFormulas, LookAt:=xlPart) If Not c Is Nothing Then MsgBox "Нашел" Else MsgBox "Не нашел" End If
Поделиться в соцсети:
Книги по теме:
Посмотреть все книги по программированию
Комментарии к статье:
| 10.09.17 Дмитрий | Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо! |
| 23.11.17 Гость | Спасибо, хорошая статья. |
| 03.12.17 Владимир | Спасибо! Использую в своих проектах. |
| 07.12.17 Эд | Спасибо, очень пригодилась Ваша статья! |
| 19.01.18 Николай | .find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра. |
| 05.02.18 Владимир | Большое спасибо! Очень толково и понятно. |
| 11.03.18 Гость | Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать? |
| 26.03.18 Гость | Спасибо! Все бы так описывали! Все доступно и понятно)) |
| 23.05.18 Аркадий | В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках. |
| 21.07.18 Гость | Спасибо! Уже несколько недель искала подобное! |
| 25.07.18 Joann | Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)? |
| 15.08.18 Марат | Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент! |
| 28.08.18 Гость | Познавательно и подробно. Спасибо за статью. |
| 16.10.18 Гость | Хорошая статья. Спасибо |
| 29.10.18 Sega | Полезная статейка. Спасибо. |
| 14.11.18 Гость | Статья для начинающих, а тонкости поиска по дате нет ни одного примера |
| 02.02.19 Ибрагим | Чушь полная, плагиат! |
| 01.03.19 inexsu.wordpress.com | Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/ |
| 01.03.19 Администратор | Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку. |
| 07.04.19 Гость | Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find. |
| 07.04.19 Гость | Лучше использовать функцию ВПР |
| 21.05.19 Гость | Очень подробная статья. Отдельное спасибо за множество примеров. |
| 17.10.19 Михаил | Спасибо большое все понятно доступно и очень полезно |
| 31.10.19 Гость | Спасибо. |
| 01.11.19 Гость | ку |
| 01.11.19 Гость | 2 раза |
| 19.11.19 Kamol | Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456" |
| 25.12.19 Гость | Спасибо! |
| 05.03.20 Гость | Добрый день! Спасибо за код автору Вопрос |
Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
With Worksheets(1).Range("A1:A50")
Set c = .Find("asd", lookin:=xlValues)
If Not c Is Nothing Then
firstResult = c.Address
Do
c.Font.Bold = True
Set c = .FindNext(c)
' If c Is Nothing Then Exit Do
Loop While c.Address <> firstResult
End If
End With
With Worksheets(1).Range("A1:A50")
Set c = .Find("asd", lookin:=xlValues)
If Not c Is Nothing Then
firstResult = c.Address
Do
c.Font.Bold = True
Set c = .FindNext(c)
' If c Is Nothing Then Exit Do
Loop While c.Address <> firstResult
End If
End With
Проверка "If c Is Nothing Then Exit Do" лишняя, ничего не даёт, её следует убрать. Дело в том, что эта проверка всегда будет давать False, поскольку в блок If . End If верхнего уровня можно попасть только в том случае, если выполняется условие "Not c Is Nothing". Отсюда следует, что поиск ".FindNext(c)" всегда будет давать результат.""""