Как отменить действия макроса

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

Но как же сделать отмену действий макроса через стандартную кнопку на панели или сочетанием клавиш Ctrl+Z и можно ли? Ответ - можно. Но сразу вопрос: а насколько это нужно? В каких ситуациях это может пригодиться? Я навскидку сразу не сказал бы, если бы не являлся разработчиком программ и надстроек в среде Microsoft Excel. Именно в надстройках отмена действий наиболее востребована, на мой взгляд. Например надстройка объединяет ячейки. Объединили случайно и...В стандартной ситуации после такого макроса нельзя отменить действия. Закрывать файл без сохранения? Как-то некрасиво получается, если продукт является коммерческим. И тогда приходится извращаться и пытаться сделать возможным отмену действий макроса. В моей надстройке MulTEx в некоторых командах отмена действий команд как раз и применяется. Наиболее распространенное решение по отмене действий макроса заключается в запоминании свойств изменяемых ячеек:

'Создаем свой пользовательский тип данных Type SaveRange vFormula As Variant sAddr As String lColor As Long End Type 'Переменные для запоминания данных Public wbWBook As Workbook Public wsSh As 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 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 Range(vOldVals(li).sAddr).Interior.Color = vOldVals(li).lColor Next li Exit Sub 'Показываем сообщение о невозможности отмены действия Erreble: MsgBox "Нельзя отменить действие!", vbCritical, "www.excel-vba.ru" End Sub

Комментарии к коду я старался сделать максимально подробными, поэтому думаю, что больше нечего разъяснять. К тому же по древней традиции я приложил к статье пример с данным кодом :) Единственное, что могу добавить: пользовательский тип SaveRange может быть дополнен еще какими-либо переменными, помимо vFormula, sAddr и lColor. Например цвет границ ячейки, цвет шрифта и т.д. Все зависит от того, какие изменения Вы будете делать кодом и что захотите затем вернуть.

Скачать пример

  Tips_Restore_Macro.xls (48,5 KiB, 1 623 скачиваний)

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

'Переменные для запоминания данных 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, 1 235 скачиваний)

Конечно, в этом приеме тоже есть недостаток - если на этот лист ссылаются формулы из других листов есть большой шанс получить в этих формулах ошибку #ССЫЛКА!(#REF!), т.к. исходный лист удаляется.
В этом случае можно из резервного листа копировать все ячейки и вставлять на рабочий лист. Да и вообще можно много чего придумать - вплоть до сохранения и последующего извлечения резервных копий файлов. Все как всегда зависит от задач и ситуации.