Многие из тех, кто программирует в VBA знают, что после действий макроса пропадает возможность отмены действий. И если с отменой тех действий, которые были совершены до выполнения макроса совершенно точно можно распрощаться(невозможно будет это сделать), то отменить действия макроса возможно. И рано или поздно каждый программирующий в VBA задается вопросом: как можно отменить действия, совершенные макросом? Для начала надо понять, в каких ситуациях это нам надо. Например был выполнен код, который испортил или удалил данные в файле, но эти данные еще нужны. Самое простое, что можно сделать это закрыть файл без сохранения и открыть заново. Все данные будут на месте(если, конечно, в коде не было строки, сохраняющей файл). Второй способ: это перед выполнением макроса делать резервную копию файла - тогда Ваши исходные данные всегда будут целы.
Но как же сделать отмену действий макроса через стандартную кнопку на панели или сочетанием клавиш
'Создаем свой пользовательский тип данных 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 может быть дополнен еще какими-либо переменными, помимо
Отменить действия макроса.xls (62,0 КиБ, 3 590 скачиваний)
Код, приведенный выше, несомненно хорош, но если кол-во изменяемых ячеек достаточно велико, то код будет очень замедлять работу. Поэтому если есть возможность добавлять/удалять листы в книгах, то можно схитрить: сделать резервную копию листа, лист сделать очень скрытым и как только потребуется отмена действия - вернуть этот лист, удалив исходный(с уже испорченными данными):
'Переменные для запоминания данных 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 КиБ, 2 481 скачиваний)
Конечно, в этом приеме тоже есть недостаток - если на этот лист ссылаются формулы из других листов есть большой шанс получить в этих формулах ошибку
В этом случае можно из резервного листа копировать все ячейки и вставлять на рабочий лист. Да и вообще можно много чего придумать - вплоть до сохранения и последующего извлечения резервных копий файлов. Все как всегда зависит от задач и ситуации.
И самая большая ложка дегтя к обоим кодам: VBA не позволяет таким образом делать МНОГОКРАТНЫЕ отмены действий(многократноеCtrl +Z ), что делает бесполезными попытки запомнить пошагово несколько разных изменений макросами. Отменить можно будет все равно только последнее запомненное действие.
Супер, спасибо большое за приведенный пример.
Первый метод хорош - достаточно вынести из метода Fill_Numbers() пример с изменением данных, и он превращается в почти полноценный метод для сохранения действий для выделенного диапазона, который можно будет вызывать в начале своего метода. К сожалению, строчку
Application.OnUndo
придется вызывать в конце каждого своего метода: написать универсальный метод не получилось - возможность использования делегатов для vba excel я не нашел (может, плохо искал).
А вообще, способ отличный - если хранить ссылку (или массив состояний) на предыдущие действия, то можно будет отменить не только последний макрос, но еще и предпоследний и так далее )
На мой взгляд нужно использовать первый метод, т.к. удаление листа попортит ссылки, а значит лучше вообще без отмены действий.
Нужно создавать скрытый лист для надстройки и на нём хранить диапозон, имя листа, имя книги и имя процедуры, делавшая изменения.
И когда нужно будет, отменять эти действия от конца, а в случае закрытия файла надстройки - очищать лист истории.
Сергей, не совсем понял Вашу мысль. Зачем создавать лист для хранения данных, если в первом методе для этого и так используется массив? К тому же на лист придется заносить так же и все значения диапазона, формулы, параметры ячеек, что весьма емко. А запись и извлечение значений с листа значительно замедлит действия кода, что на более-менее больших объемах будет более чем заметно. Но тут, в принципе, дело фантазии - я не претендовал на оригинальность и универсальность решения. Моей задачей было показать принцип и если Вы нашли решение лучше - это только плюс Вам.
Я сейчас пересмотрел код и столкнулся с такой проблемой. Почему не добавляется пункты по отмене действий макроса?
Вот код:
И вопрос нужно чтоб нижняя строка массива обрезалась при отмене действия, а второй вопрос в прошлом сообщении =)
По порядку:
1. Выложенный код подразумевает отмену ТОЛЬКО последнего макроса и лишь один пункт соответственно будет отображаться.
2. Как-то непонятно строка какого массива должна обрезаться.И какое отношение этот вопрос имеет к данной статье?
Ваш "макрос отмены" возвращает состояние листа, "запомненное" перед первым выполнением "макроса действия." При этом плодятся новые листы.
Делаю 2 "макроса действия", потом свое действие, потом опять "макрос действия". Далее жму отмену - исчезают и 3 действия "макроса действия", и мое действие.
Геннадий, а вопрос-то в чем? Выложенные коды работают так, как заявлено. Вашей проблемы не понимаю: какие листы плодятся, какие Ваши действия и действия макроса...
Вот только не понятно почему в Excel нельзя отменить действия макроса? Разработчики этого не предусмотрели?
Программировал в OpenOffice Calc (на StarBasic) и не смотря на то что это бесплатный продукт и довольно таки более скромный там есть возможность отмены любого действия макроса (где-то на 30 шагов).
Это очень удобно на этапе разработки - чтобы что-то подправить, оптимизировать, протестировать. Что ж мелкомягкие до сих пор этого не сделали?