Самый быстрый способ отметить изменения, которые делает макрос

Некоторые предпосылки:

У меня есть надстройка Excel, которая выполняет 17 подпрограмм и задается вопросом, какой будет самый простой, эффективный и быстрый способ

  1. Записывать конкретные изменения, сделанные макросом
  2. Отобразите список всех изменений (в новой электронной таблице) и почему он изменил (или пометил / выделил) каждую ячейку
  3. Укажите адрес ячейки каждой ошибки в качестве кликабельной ссылки, которая приведет пользователя к конкретной ячейке на другом листе (в той же книге)

То, что я собрал до сих пор:

Я прочитал несколько способов, как это сделать, особенно здесь , здесь , здесь , и, что самое главное, здесь . Они предлагают такие методы, как

  • Сбрасывая UsedRange в массив до запуска всех подсетей, затем сбрасывая UsedRange во второй массив после завершения всех UsedRange . Сравните два массива и найдите различия таким образом.
  • Использование события worksheet_change для регистрации изменений.
  • Последняя ссылка для сайта Marktastic обеспечивает наиболее убедительный метод, сочетающий два предыдущих метода. Однако даже при его методе проверки массива каждый раз, когда срабатывает событие workheet_change, требуется 0,6 секунды, что похоже на то, что это будет связано с большим количеством накладных расходов.

Несколько конкретных проблем, с которыми я сталкиваюсь, включают:

  • Работа с большими файлами (15000+ строк, 150 столбцов)
  • Некоторые из моих подписчиков вносят изменения, которые не нужно замечать, например, только переупорядочение значений в определенных ячейках, а не изменение их в противном случае
  • Мой макрос построен для быстрой работы (<1 минута) и завершения сканирования наших больших файлов, поэтому я предполагаю, что это устранит использование события Worksheet_Change а также сразу же будет немедленно отправлено на отдельный лист после каждой ошибки. Даже использование обоих методов в одном, как говорит Марк, кажется, что это вызовет слишком много времени.
  • Сравнивая два массива, он также будет довольно медленным для файлов, размер которых я работаю, поскольку это означало бы итерацию через 15000 (строк) x 150 (столбцы) = 2 250 000 ячеек
  • Поскольку я делаю все изменения через макрос, событие worksheet_change не будет срабатывать.

Таким образом, это в основном устраняет все варианты, с которыми я столкнулся. Единственный другой вариант, о котором я могу думать, – это просто иметь один массив, использовать его для записи ячейки / местоположения и ошибки в местоположении по мере выполнения кода, а затем сбрасывать весь массив на новый лист после завершения подписки. Будет ли это действительно моим лучшим вариантом?

Итак, учитывая все факты и исследования выше

Теперь, по моему вопросу:

  • Какой был бы самый эффективный способ в моей ситуации записать изменения и показать, какие изменения были внесены в файл после завершения моего макроса? Я перечислил это или нет, не стесняйтесь делиться методом, который, по вашему мнению, лучше всего подходит для этой ситуации.
  • Можете ли вы представить простой пример кода, как реализовать предложенный метод?

Вот что я решил пойти. Используя этот метод, я вызываю sub каждый раз, когда хочу отметить изменение и передать необходимые переменные, которые мне нужны. В этом случае Cell , Reason , Status . Затем вызванный sub хранит его в конце открытого Array , в этом случае ChangeLog . После того, как я PrintLog запись всех изменений, мне нужно, чтобы я вызвал PrintLog чтобы вывести список изменений на новый лист, который я переименовал Change Log . Добавленное мной дополнение – это готовые формулы HYPERLINK я создаю, сохраняя значения внутри массива. После того, как массив вставлен на рабочий лист, ссылки на ячейки уже являются кликабельными ссылками, которые приводят пользователя к адресу на другом листе.

 Public ChangeLog() As String Sub Test() Log ActiveSheet.Range("A2"), "Test1", "Changed" Log ActiveSheet.Range("B2"), "Test2", "Needs Attention" Log ActiveSheet.Range("C2"), "Test3", "Changed" PrintLog End Sub Sub Log(Cell As Range, Reason As String, Optional Status As String) On Error Resume Next If (Not Not ChangeLog) = 0 Then ReDim ChangeLog(0 To 2, 0 To 1) ChangeLog(0, 0) = "Cells": ChangeLog(1, 0) = "Changes Made": ChangeLog(2, 0) = "Status" ChangeLog(0, 1) = "=Hyperlink(" & """#'" & ActiveSheet.Name & "'!" & Cell.Address(False, False) & """,""" & Cell.Address(False, False) & """)" ChangeLog(1, 1) = Reason ChangeLog(2, 1) = Status Else ReDim Preserve ChangeLog(0 To 2, 0 To UBound(ChangeLog, 2) + 1) ChangeLog(0, UBound(ChangeLog, 2)) = "=Hyperlink(" & """#'" & ActiveSheet.Name & "'!" & Cell.Address(False, False) & """,""" & Cell.Address(False, False) & """)" ChangeLog(1, UBound(ChangeLog, 2)) = Reason ChangeLog(2, UBound(ChangeLog, 2)) = Status End If On Error GoTo 0 End Sub Sub PrintLog() Dim currentSheet As Integer: currentSheet = ActiveSheet.Index For s = 1 To ActiveWorkbook.Worksheets.Count If Worksheets(s).Name = "Change Log" Then Application.DisplayAlerts = False Worksheets(s).Delete Application.DisplayAlerts = True Exit For End If Next Dim WS As Worksheet: Set WS = Sheets.Add(After:=Worksheets(ActiveSheet.Index)) WS.Name = "Change Log" WS.Tab.Color = vbYellow WS.Range("A1").Resize(UBound(ChangeLog, 2) + 1, 3) = WorksheetFunction.Transpose(ChangeLog) Erase ChangeLog Worksheets(currentSheet).Activate End Sub 

Изменить предварительный просмотр журнала:
Журнал изменений

  • Совместное использование выбора нескольких ячеек Excel в книгах в режиме реального времени
  • Excel вложен, если массив
  • Объединение двумерных (двухмерных) массивов
  • VBA Соответствие дубликатов результатов в массиве и их запись в отсутствующих ячейках
  • сохраняя значение от имени переменной «QW1I5K20» в элементе массива Q с использованием R
  • Использование VBA для чтения строки .csv в многомерном массиве
  • извлекать определяемые пользователем размеры из массива с несколькими размерами
  • Excel VBA: копирование 2D-таблицы в массив, а затем отправка ее в ячейки других листов
  • Наиболее эффективный способ синтаксического анализа в массиве VBA
  • Excel VBA - заполнение многоколонного пользовательского формуляра из массива. Нет данных, когда массив имеет только 1 элемент
  • Excel VBA - передать строку значений ячеек массиву и затем вставить этот массив в относительную ссылку на ячейки
  • Давайте будем гением компьютера.