Как отменить действие макроса excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 | ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Отмена, возврат и повтор действий пользователя при работе в Excel
Вы можете отменить почти все команды, выполненные в Excel, с помощью команды Отменить, расположенной на панели быстрого доступа. Панель быстрого доступа, как правило, отображается слева от строки заголовка Excel. Вы также можете нажимать Ctrl+Z для отмены действий.
Выберите Отменить, после того как по ошибке выполнили какую-нибудь команду, и эта команда будет отменена, как будто вы ее вообще никогда и не выполняли. Вы можете отменить результат последних 100 выполненных команд, нажимая кнопку Отменить много раз.
Однако некоторые действия вы не сможете отменить. Все команды, выполняемые с помощью кнопки Файл, например сохранение книги, нельзя отменить. Если вы нажмете кнопку по стрелкой, расположенную справа от кнопки Отменить, то увидите список последних команд, которые можно отменить (рис. 6.1). Можно выделить сразу набор таких команд, чтобы отменить несколько действий одним щелчком. Па рисунке выделены семь действий.
Рис 6.1. Кнопка Отменить открывает список последних действий, которые можно отменить
Имейте в виду, что вы не можете отменить эффект выполнения макроса. На самом деле запуск макроса, изменяющего лист, стирает всю информацию относительно отмены действий. Этого ощутимого недостатка Excel нет в Microsoft Word.
Возврат действий
Кнопка Вернуть (расположена справа от кнопки Отменить на панели быстрого доступа), по существу, отменяет команду Отменить. Если вы отменили слишком много действий, то можете нажать кнопку Вернуть (либо нажмите Ctrl+Y или F4) для возврата команд, которые были отменены.
Повтор действий
Вы можете повторять многие операции, нажимая Ctrl+Y или F4. Эта команда просто повторяет последнее действие. Однако если последней операцией была операция Отменить, то результат ее действия отменяется (как описано в предыдущем разделе).
Повторение команд может быть полезно в плане экономии времени. Приведу пример. Вы могли применить множество настроек форматирования (задать размер шрифта, полужирное начертание, цвет фона и рамки) для ячейки с помощью диалогового окна Формат ячеек. После закрытия диалогового окна можно легко установить аналогичные настройки форматирования для других ячеек или диапазонов, нажимая Ctrl+Y. Или нам может понадобиться вставить пустые строки в определенных местах листа. Для этого выполните команду Главная → Ячейки → Вставить → Вставить строки на лист один раз, затем установите курсор в ячейку, в которую нужно вставить следующую строку, и нажмите Ctrl+Y, чтобы повторить команду вставки строки.
В Excel также есть кнопка Повторить, но обычно она недоступна. Несмотря на это, добавим ее на панель быстрого доступа.
- Щелкните правой кнопкой мыши на панели быстрого доступа и в появившемся контекстном меню выберите Настройка панели быстрого доступа. В результате откроется диалоговое окно Параметры Excel на вкладке Панель быстрого доступа.
- В раскрывающемся списке Выбрать команды из выберите пункт Часто используемые команды.
- В списке команд выберите Повторить.
- Нажмите кнопку Добавить, чтобы добавить выбранную команду на панель быстрого доступа.
- Нажмите ОК, чтобы закрыть диалоговое окно Параметры Excel.
Вы можете спросить, зачем вообще добавлять кнопку Повторить па панель быстрого доступа, если можно пользоваться сочетанием клавиш Ctrl+Y. Во-первых, при наведении указателя мыши на эту кнопку Excel отобразит описание повторяемой операции (рис. 6.2). Во-вторых, кнопка Повторить становится неактивной, если вы не можете повторить последнюю команду. — своеобразный визуальный сигнал, который поможет избежать ненужных попыток повторить то, что просто не может быть повторено.
Рис. 6.2. Всплывающая подсказка кнопки Повторить, добавленной на панель быстрого доступа, описывает повторяемые операции (если таковые имеются)
К сожалению, функция повторения операции не всегда надежна. В некоторых случаях вы можете обнаружить, что нажатие Ctrl+Y не дает никакого эффекта (хотя должно). В других ситуациях нажатие Ctrl+Y может повторить команду, которую вы выполняли перед предыдущей командой.
Как отменить действия макроса
Многие из тех, кто программирует в VBA знают, что после действий макроса пропадает возможность отмены действий. И если с отменой тех действий, которые были совершены до выполнения макроса совершенно точно можно распрощаться(невозможно будет это сделать), то отменить действия макроса возможно. И рано или поздно каждый программирующий в VBA задается вопросом: как можно отменить действия, совершенные макросом? Для начала надо понять, в каких ситуациях это нам надо. Например был выполнен код, который испортил или удалил данные в файле, но эти данные еще нужны. Самое простое, что можно сделать это закрыть файл без сохранения и открыть заново. Все данные будут на месте(если, конечно, в коде не было строки, сохраняющей файл). Второй способ: это перед выполнением макроса делать резервную копию файла — тогда Ваши исходные данные всегда будут целы.
Но как же сделать отмену действий макроса через стандартную кнопку на панели или сочетанием клавиш Ctrl + Z и можно ли? Ответ — можно. Но сразу вопрос: а насколько это нужно? В каких ситуациях это может пригодиться? Я навскидку сразу не сказал бы, если бы не являлся разработчиком программ и надстроек в среде Microsoft Excel. Именно в надстройках отмена действий наиболее востребована, на мой взгляд. Например надстройка объединяет ячейки. Объединили случайно и. В стандартной ситуации после такого макроса нельзя отменить действия. Закрывать файл без сохранения? Как-то некрасиво получается, если продукт является коммерческим. И тогда приходится извращаться и пытаться сделать возможным отмену действий макроса. В моей надстройке MulTEx в некоторых командах отмена действий команд как раз и применяется. Наиболее распространенное решение по отмене действий макроса заключается в запоминании свойств изменяемых ячеек:
'Создаем свой пользовательский тип данных Type SaveRange vFormula As Variant sAddr As String lColor As Long lColorIndex As Long End Type 'Переменные для запоминания данных Public wbWBook As Excel.Workbook Public wsSh As Excel.Worksheet Public vOldVals() As SaveRange '--------------------------------------------------------------------------------------- ' Procedure : Fill_Numbers ' Purpose : Основная процедура. Это тот код, который вносит изменения на лист ' и действия которого нам необходимо отменить ' Процедура заполняет выделенные ячейки номерами ' и изменяет цвет заливки '--------------------------------------------------------------------------------------- Sub Fill_Numbers() Dim rCell As Range, li As Long ' Сначала запоминаем значения выделенных ячеек на листе ReDim vOldVals(1 To Selection.Count) 'Запоминаем активную книгу 'это на случай, если отмена действий будет производиться из другой книги Set wbWBook = ActiveWorkbook 'Запоминаем активный лист 'на случай, если отмена действий будет производиться из другого листа Set wsSh = ActiveSheet 'Запоминаем значения(заносим в массив) li = 1 For Each rCell In Selection 'запоминаем адрес ячейки vOldVals(li).sAddr = rCell.Address 'запоминаем формулу(если нет формулы - значение) vOldVals(li).vFormula = rCell.Formula 'запоминаем цвет заливки ячейки vOldVals(li).lColor = rCell.Interior.Color 'запоминаем индекс цвета заливки(чтобы на заливать бесцветные ячейки) vOldVals(li).lColorIndex = rCell.Interior.ColorIndex li = li + 1 Next rCell '====================================== 'Выполняем основные действия(собственно тот код, который надо будет отменить) li = 1 For Each rCell In Selection rCell = li rCell.Interior.ColorIndex = li li = li + 1 Next rCell '====================================== 'Назначаем стандартному вызову отмены действий выполнение нашего макроса возвращения значений Application.OnUndo "Отменить заполнение ячеек номерами", "Restore_Vals" End Sub '--------------------------------------------------------------------------------------- ' Procedure : Restore_Vals ' Purpose : Процедура отмены действия(возврат значений) '--------------------------------------------------------------------------------------- Sub Restore_Vals() Dim li As Long 'В случае непредвиденной ошибки переходим на метку 'и показываем сообщение об ошибке On Error GoTo Erreble 'Активируем книгу, в которой были сделаны изменения wbWBook.Activate 'Активируем лист, в котором были сделаны изменения wsSh.Activate 'Возвращаем значения For li = 1 To UBound(vOldVals) Range(vOldVals(li).sAddr).Formula = vOldVals(li).vFormula 'если заливка была безцветная, то ColorIndex = xlNone 'значит выставляем безцветность If Not vOldVals(li).lColorIndex = xlNone Then Range(vOldVals(li).sAddr).Interior.Color = vOldVals(li).lColor Else 'если цвет был - возвращаем его Range(vOldVals(li).sAddr).Interior.ColorIndex = xlNone End If Next li Exit Sub 'Показываем сообщение о невозможности отмены действия Erreble: MsgBox "Нельзя отменить действие!", vbCritical, "Error" End Sub
‘Создаем свой пользовательский тип данных Type SaveRange vFormula As Variant sAddr As String lColor As Long lColorIndex As Long End Type ‘Переменные для запоминания данных Public wbWBook As Excel.Workbook Public wsSh As Excel.Worksheet Public vOldVals() As SaveRange ‘————————————————————————————— ‘ Procedure : Fill_Numbers ‘ Purpose : Основная процедура. Это тот код, который вносит изменения на лист ‘ и действия которого нам необходимо отменить ‘ Процедура заполняет выделенные ячейки номерами ‘ и изменяет цвет заливки ‘————————————————————————————— Sub Fill_Numbers() Dim rCell As Range, li As Long ‘ Сначала запоминаем значения выделенных ячеек на листе ReDim vOldVals(1 To Selection.Count) ‘Запоминаем активную книгу ‘это на случай, если отмена действий будет производиться из другой книги Set wbWBook = ActiveWorkbook ‘Запоминаем активный лист ‘на случай, если отмена действий будет производиться из другого листа Set wsSh = ActiveSheet ‘Запоминаем значения(заносим в массив) li = 1 For Each rCell In Selection ‘запоминаем адрес ячейки vOldVals(li).sAddr = rCell.Address ‘запоминаем формулу(если нет формулы — значение) vOldVals(li).vFormula = rCell.Formula ‘запоминаем цвет заливки ячейки vOldVals(li).lColor = rCell.Interior.Color ‘запоминаем индекс цвета заливки(чтобы на заливать бесцветные ячейки) vOldVals(li).lColorIndex = rCell.Interior.ColorIndex li = li + 1 Next rCell ‘====================================== ‘Выполняем основные действия(собственно тот код, который надо будет отменить) li = 1 For Each rCell In Selection rCell = li rCell.Interior.ColorIndex = li li = li + 1 Next rCell ‘====================================== ‘Назначаем стандартному вызову отмены действий выполнение нашего макроса возвращения значений Application.OnUndo «Отменить заполнение ячеек номерами», «Restore_Vals» End Sub ‘————————————————————————————— ‘ Procedure : Restore_Vals ‘ Purpose : Процедура отмены действия(возврат значений) ‘————————————————————————————— Sub Restore_Vals() Dim li As Long ‘В случае непредвиденной ошибки переходим на метку ‘и показываем сообщение об ошибке On Error GoTo Erreble ‘Активируем книгу, в которой были сделаны изменения wbWBook.Activate ‘Активируем лист, в котором были сделаны изменения wsSh.Activate ‘Возвращаем значения For li = 1 To UBound(vOldVals) Range(vOldVals(li).sAddr).Formula = vOldVals(li).vFormula ‘если заливка была безцветная, то ColorIndex = xlNone ‘значит выставляем безцветность If Not vOldVals(li).lColorIndex = xlNone Then Range(vOldVals(li).sAddr).Interior.Color = vOldVals(li).lColor Else ‘если цвет был — возвращаем его Range(vOldVals(li).sAddr).Interior.ColorIndex = xlNone End If Next li Exit Sub ‘Показываем сообщение о невозможности отмены действия Erreble: MsgBox «Нельзя отменить действие!», vbCritical, «Error» End Sub
Комментарии к коду я старался сделать максимально подробными, поэтому думаю, что больше нечего разъяснять. К тому же по древней традиции я приложил к статье пример с данным кодом 🙂 Единственное, что могу добавить: пользовательский тип SaveRange может быть дополнен еще какими-либо переменными, помимо vFormula , sAddr и lColor . Например цвет границ ячейки, цвет шрифта и т.д. Все зависит от того, какие изменения Вы будете делать кодом и что захотите затем вернуть.
Отменить действия макроса.xls (62,0 KiB, 3 459 скачиваний)
Код, приведенный выше, несомненно хорош, но если кол-во изменяемых ячеек достаточно велико, то код будет очень замедлять работу. Поэтому если есть возможность добавлять/удалять листы в книгах, то можно схитрить: сделать резервную копию листа, лист сделать очень скрытым и как только потребуется отмена действия — вернуть этот лист, удалив исходный(с уже испорченными данными):
'Переменные для запоминания данных Public wbWBook As Workbook Public wsSh As Worksheet, wsActSh As Worksheet, sSh_Name As String, lShPoz As Long '--------------------------------------------------------------------------------------- ' Procedure : Fill_Numbers ' Purpose : Основная процедура. Это тот код, который вносит изменения на лист ' и действия которого нам необходимо отменить ' Процедура заполняет выделенные ячейки номерами ' и изменяет цвет заливки '--------------------------------------------------------------------------------------- Sub Fill_Numbers() Dim rCell As Range, li As Long 'Запоминаем активную книгу 'это на случай, если отмена действий будет производиться из другой книги Set wbWBook = ActiveWorkbook 'Запоминаем активный лист 'на случай, если отмена действий будет производиться из другого листа Set wsActSh = ActiveSheet lShPoz = wsActSh.Index sSh_Name = wsActSh.Name Application.ScreenUpdating = 0 wsActSh.Copy , wbWBook.Sheets(wbWBook.Sheets.Count) Set wsSh = wbWBook.Sheets(wbWBook.Sheets.Count) wsSh.Visible = xlVeryHidden wsActSh.Activate Application.ScreenUpdating = 1 '====================================== 'Выполняем основные действия(собственно тот код, который надо будет отменить) li = 1 For Each rCell In Selection rCell = li rCell.Interior.ColorIndex = li li = li + 1 Next rCell '====================================== 'Назначаем стандартному вызову отмены действий выполнение нашего макроса возвращения значений Application.OnUndo "Отменить заполнение ячеек номерами", "Restore_Vals" End Sub '--------------------------------------------------------------------------------------- ' Procedure : Restore_Vals ' Purpose : Процедура отмены действия(возврат значений) '--------------------------------------------------------------------------------------- Sub Restore_Vals() 'В случае непредвиденной ошибки переходим на метку 'и показываем сообщение об ошибке On Error GoTo Erreble Application.ScreenUpdating = 0 'Активируем книгу, в которой были сделаны изменения wbWBook.Activate 'делаем видимым резервный лист wsSh.Visible = -1 'Удаляем исходный лист, данные в котором уже изменены Application.DisplayAlerts = 0 wsActSh.Delete Application.DisplayAlerts = 1 'назначаем резервному листу имя исходного wsSh.Name = sSh_Name wsSh.Move wbWBook.Sheets(lShPoz) 'Активируем резервный лист wsSh.Activate Application.ScreenUpdating = 0 Exit Sub 'Показываем сообщение о невозможности отмены действия Erreble: MsgBox "Нельзя отменить действие!", vbCritical, "www.excel-vba.ru" End Sub
‘Переменные для запоминания данных Public wbWBook As Workbook Public wsSh As Worksheet, wsActSh As Worksheet, sSh_Name As String, lShPoz As Long ‘————————————————————————————— ‘ Procedure : Fill_Numbers ‘ Purpose : Основная процедура. Это тот код, который вносит изменения на лист ‘ и действия которого нам необходимо отменить ‘ Процедура заполняет выделенные ячейки номерами ‘ и изменяет цвет заливки ‘————————————————————————————— Sub Fill_Numbers() Dim rCell As Range, li As Long ‘Запоминаем активную книгу ‘это на случай, если отмена действий будет производиться из другой книги Set wbWBook = ActiveWorkbook ‘Запоминаем активный лист ‘на случай, если отмена действий будет производиться из другого листа Set wsActSh = ActiveSheet lShPoz = wsActSh.Index sSh_Name = wsActSh.Name Application.ScreenUpdating = 0 wsActSh.Copy , wbWBook.Sheets(wbWBook.Sheets.Count) Set wsSh = wbWBook.Sheets(wbWBook.Sheets.Count) wsSh.Visible = xlVeryHidden wsActSh.Activate Application.ScreenUpdating = 1 ‘====================================== ‘Выполняем основные действия(собственно тот код, который надо будет отменить) li = 1 For Each rCell In Selection rCell = li rCell.Interior.ColorIndex = li li = li + 1 Next rCell ‘====================================== ‘Назначаем стандартному вызову отмены действий выполнение нашего макроса возвращения значений Application.OnUndo «Отменить заполнение ячеек номерами», «Restore_Vals» End Sub ‘————————————————————————————— ‘ Procedure : Restore_Vals ‘ Purpose : Процедура отмены действия(возврат значений) ‘————————————————————————————— Sub Restore_Vals() ‘В случае непредвиденной ошибки переходим на метку ‘и показываем сообщение об ошибке On Error GoTo Erreble Application.ScreenUpdating = 0 ‘Активируем книгу, в которой были сделаны изменения wbWBook.Activate ‘делаем видимым резервный лист wsSh.Visible = -1 ‘Удаляем исходный лист, данные в котором уже изменены Application.DisplayAlerts = 0 wsActSh.Delete Application.DisplayAlerts = 1 ‘назначаем резервному листу имя исходного wsSh.Name = sSh_Name wsSh.Move wbWBook.Sheets(lShPoz) ‘Активируем резервный лист wsSh.Activate Application.ScreenUpdating = 0 Exit Sub ‘Показываем сообщение о невозможности отмены действия Erreble: MsgBox «Нельзя отменить действие!», vbCritical, «www.excel-vba.ru» End Sub
Tips_Restore_Macro_HiddenSh.xls (45,0 KiB, 2 423 скачиваний)
Конечно, в этом приеме тоже есть недостаток — если на этот лист ссылаются формулы из других листов есть большой шанс получить в этих формулах ошибку #ССЫЛКА! (#REF!) , т.к. исходный лист удаляется.
В этом случае можно из резервного листа копировать все ячейки и вставлять на рабочий лист. Да и вообще можно много чего придумать — вплоть до сохранения и последующего извлечения резервных копий файлов. Все как всегда зависит от задач и ситуации.
И самая большая ложка дегтя к обоим кодам: VBA не позволяет таким образом делать МНОГОКРАТНЫЕ отмены действий(многократное Ctrl + Z ), что делает бесполезными попытки запомнить пошагово несколько разных изменений макросами. Отменить можно будет все равно только последнее запомненное действие.
Как вернуть действие макроса в Excel?
Допустим, я создал макрос который сортирует столбец с Ф.И.О. студентов по алфавиту. Макрос выполняет это действие, а теперь я хочу вернуть столбец в первоначальный вид, и нажимаю на кнопку(привязанную к макросу) но ничего не происходит.
Дополнен 13 лет назад
это я просто привел пример
Дополнен 13 лет назад
я понимаю что отсортировать можно и без макроса
Дополнен 13 лет назад
мой макрос подвязан к кнопке! когда жмешь на кнопку-выполняется макрос, при повторном нажатии-макрос должен отменится!
Лучший ответ
Штатные средства не возвращают состояние таблицы до выполнения макроса, потому спасение утопающих — дело рук самих утопающих — и перед выполнением макроса сохранять таблицу и тогда можно будет ее восстановить 🙂
если не жалко дискового пространства, то в каждый макрос вставлять вызов вспомогательного макроса по сохранению копии таблицы в файл типо: откатХХХХХ. xls
Остальные ответы
Зачем вообще для этого макрос.
🙂 Для сортировки макросы не нужны, выбираем строки по которым нужно отсортировать и нажимаем «Сортировать».
Что бы вернуть действие макроса нажимаем «Макросы» — отменить действие макросов. Если старая версия офиса то просто удаляем макрос.
Ctrl+Z — Это отмена последнего действия.
Или переписывать макрос, чтобы он проверял, а не отсортирована ли уже колонка?
Если вообще для макросов, то такого средства не существует
Для каждого конкретного макроса придется писать свой макрос отката