Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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

Многие из тех, кто программирует в 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

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

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

  Отменить действия макроса.xls (62,0 KiB, 3 515 скачиваний)


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

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


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


И самая большая ложка дегтя к обоим кодам: VBA не позволяет таким образом делать МНОГОКРАТНЫЕ отмены действий(многократное Ctrl+Z), что делает бесполезными попытки запомнить пошагово несколько разных изменений макросами. Отменить можно будет все равно только последнее запомненное действие.


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 10 комментариев
  1. n1k1t0ss:

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

  2. n1k1t0ss:

    Application.OnUndo
    придется вызывать в конце каждого своего метода: написать универсальный метод не получилось - возможность использования делегатов для vba excel я не нашел (может, плохо искал).
    А вообще, способ отличный - если хранить ссылку (или массив состояний) на предыдущие действия, то можно будет отменить не только последний макрос, но еще и предпоследний и так далее )

  3. Сергей:

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

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

  4. Сергей, не совсем понял Вашу мысль. Зачем создавать лист для хранения данных, если в первом методе для этого и так используется массив? К тому же на лист придется заносить так же и все значения диапазона, формулы, параметры ячеек, что весьма емко. А запись и извлечение значений с листа значительно замедлит действия кода, что на более-менее больших объемах будет более чем заметно. Но тут, в принципе, дело фантазии - я не претендовал на оригинальность и универсальность решения. Моей задачей было показать принцип и если Вы нашли решение лучше - это только плюс Вам.

  5. Сергей:

    Я сейчас пересмотрел код и столкнулся с такой проблемой. Почему не добавляется пункты по отмене действий макроса?

  6. Сергей:

    Вот код:
    И вопрос нужно чтоб нижняя строка массива обрезалась при отмене действия, а второй вопрос в прошлом сообщении =)

    Type SaveRange
        Wbk As Workbook
        Sheet As Worksheet
        Address As String
        Formula As String
        SelectCount As Long
        Procedur As String
    End Type
    Public BackUP() As SaveRange
    Public ColVo As Long            '# действия
     
    Sub Fill_Numbers()
    Dim rcell As Range
    SaveME          'Сохраняем текущее значение
        For Each rcell In Selection
            rcell = rcell.Column * rcell.Row
        Next rcell
    End_SaveMe "ОТМЕНИТЬ="  'Устанавливаем отмену действия
    End Sub
     
    Sub SaveME()
    If ColVo = 0 Then ReDim BackUP(1)
    ColVo = UBound(BackUP) 'Запоминаем номер действия, который в последующем будет отменяться
    'MsgBox ColVo
    Dim rcell As Range
    With BackUP(ColVo)
        Set .Wbk = ActiveWorkbook
        Set .Sheet = ActiveWorkbook.ActiveSheet
        .Procedur = Procedur                   '
        .SelectCount = Selection.Count         'запоминаем кол-во ячеек, что-бы при отмене задать цикл для возврата формул
        .Address = Selection.Address           'адрес ячеек подвергаемых отмене процедуры
    End With
        For Each rcell In Selection
            BackUP(ColVo).Formula = BackUP(ColVo).Formula & "Ћ" & rcell.Formula
        Next rcell
    'With BackUP(ColVo)
    'MsgBox .Wbk.Name & Chr(13) & _
            .Sheet.Name & Chr(13) & _
            .Procedur & Chr(13) & _
            .SelectCount & Chr(13) & _
            .Address & Chr(13) & .Formula
    'End With
    End Sub
    Sub End_SaveMe(Optional Имя_отменяемой_процедуры As String)
    ReDim Preserve BackUP(ColVo + 1)
    Application.OnUndo Имя_отменяемой_процедуры & ColVo, "Restore_Vals"
    End Sub
    Sub Restore_Vals()
    'MsgBox ColVo
    Dim rcell As Range
    Dim x As Long
    'On Error Resume Next
    BackUP(ColVo).Wbk.Activate
    BackUP(ColVo).Sheet.Activate
    x = 1
        For Each rcell In Range(BackUP(ColVo).Address)
             rcell.Formula = Между_знаками(BackUP(ColVo).Formula, "Ћ", x)
        x = x + 1
        Next rcell
    ReDim Preserve BackUP(UBound(BackUP) - 1)
    ColVo = UBound(BackUP)    'Запоминаем номер действия, который в последующем будет отменяться
    End Sub
     
    Public Function Между_знаками(текст_который_нужно_очистеть As String, знак1 As String, Optional N_по_счету1 As Long, Optional знак2 As String, Optional N_по_счету2 As Long)
    Application.Volatile
    Dim bukva As String
    Dim x As Single
    Dim NA4_sbor As Boolean
    If N_по_счету1 = 0 Then NA4_sbor = True
    сч1 = 0
    If N_по_счету2 = 0 Then N_по_счету2 = 1
    сч2 = 0
    If знак2 = "" Then знак2 = знак1
     For x = 1 To Len(текст_который_нужно_очистеть) + 1
      If Mid(текст_который_нужно_очистеть, x, Len(знак1)) = знак1 Then сч1 = сч1 + 1: _
      If сч1 = N_по_счету1 Then NA4_sbor = True: x = x + Len(знак1) - 1: GoTo nnext
      If Mid(текст_который_нужно_очистеть, x, Len(знак2)) = знак2 And NA4_sbor = True Then _
      сч2 = сч2 + 1: If сч2 = N_по_счету2 Then Exit Function
      If NA4_sbor = True Then Между_знаками = Между_знаками & Mid(текст_который_нужно_очистеть, x, 1) Else GoTo nnext
    nnext:
     Next
    End Function
  7. По порядку:
    1. Выложенный код подразумевает отмену ТОЛЬКО последнего макроса и лишь один пункт соответственно будет отображаться.
    2. Как-то непонятно строка какого массива должна обрезаться.И какое отношение этот вопрос имеет к данной статье?

  8. Геннадий:

    Ваш "макрос отмены" возвращает состояние листа, "запомненное" перед первым выполнением "макроса действия." При этом плодятся новые листы.
    Делаю 2 "макроса действия", потом свое действие, потом опять "макрос действия". Далее жму отмену - исчезают и 3 действия "макроса действия", и мое действие.

  9. Геннадий, а вопрос-то в чем? Выложенные коды работают так, как заявлено. Вашей проблемы не понимаю: какие листы плодятся, какие Ваши действия и действия макроса...

  10. Игорь:

    Вот только не понятно почему в Excel нельзя отменить действия макроса? Разработчики этого не предусмотрели?
    Программировал в OpenOffice Calc (на StarBasic) и не смотря на то что это бесплатный продукт и довольно таки более скромный там есть возможность отмены любого действия макроса (где-то на 30 шагов).
    Это очень удобно на этапе разработки - чтобы что-то подправить, оптимизировать, протестировать. Что ж мелкомягкие до сих пор этого не сделали?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти