Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Отслеживание команды удаления строк из Worksheet_Change
 
#1
29.01.2018 15:08:37
Добрый день.
Имеется обработчик изменений рабочего листа Worksheet_Change
Поскольку он может вызывать значительный объем вычислений и подвешивать интерфейс, крайне не желательна его излишняя стработка при простом удалении строк с листа.
Хочу либо блокировать Worksheet_Change при удалении строк, либо поставить обход кода внутри обработчика Worksheet_Change, если он вызван удалением строк.
Как реализовать?
P|S Удаление обычно произвожу через <alt> + <п> + <у> , предварительно выделив строки через <shift>+<spase>
 
 
 
#2
29.01.2018 15:12:22
Нет никакого признака, что удаляли именно строки. И что вообще удаляли, а не что-то другое с ними делали. Но можно проверить, что действие производилось с целой строкой. Например, так:
Код
If selection.columns.count = target.parent.columns.count then
'это выделена полностью строка
end if

или что-то вроде того
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#3
29.01.2018 15:13:53
Цитата
Neufazendnik написал: Имеется обработчик изменений рабочего листа Worksheet_Change
Это обработчик события изменения ДИАПАЗОНА. События удаления/вставки строк/столбцов не отслеживается. У Вас, скорее всего, срабатывает событие Worksheet_Calculate().
Отменить отслеживание событий можно командой
Код
Application.EnableEvents = False
Только не забывайте включать обратно ! (TRUE)
Изменено: Sanja - 29.01.2018 15:16:34
Согласие есть продукт при полном непротивлении сторон
 
 
 
#4
29.01.2018 16:29:47
Neufazendnik, в первом приближении. Код ложно определяет удаление строки, когда удаляется последнее значение из последней строки. При желании можете добавить проверку из #2
Код
'код листа >>>>
Option Explicit

Public lastUsedRow&

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim lur&
  lur = Me.UsedRange.Row + Me.UsedRange.Rows.Count - 1
  If lur < lastUsedRow Then
    MsgBox "удалено строк: " & lastUsedRow - lur
  End If
  lastUsedRow = lur
End Sub

'код модуля ЭтаКнига >>>>
Option Explicit

Private Sub Workbook_Open()
  Лист2.lastUsedRow = Лист2.UsedRange.Row + Лист2.UsedRange.Rows.Count - 1 'Лист2 - CodeName листа (не меняется при обычном переименовании)
End Sub
 
 
 
#5
29.01.2018 16:44:01
Цитата
Sanja написал: У Вас, скорее всего, срабатывает событие Worksheet_Calculate().
Однако, точка прерывания кода по F9 срабатывает не где-либо, а в коде Worksheet_Change
Как то управление там оказывается при удалении строк с листа!

Цитата
Sanja написал: Отменить отслеживание событий можно командой
Нельзя отменять. Смысл в том, чтобы обработка была. Но ее нужно обойти, если тупо часть данных убирается с листа без их правки.
 
 
 
#6
29.01.2018 16:54:31
Цитата
Neufazendnik написал: Нельзя отменять. Смысл в том, чтобы обработка была
Если прописать отключение обработки событий в нужном месте, то обработка будет, но только одного, нужного в данный момент события
Приложите Файл-пример. Как есть - Как надо. С вашими обработчиками событий
Согласие есть продукт при полном непротивлении сторон
 
 
 
#7
29.01.2018 17:00:32
Цитата
Neufazendnik написал: Однако, точка прерывания кода по F9 срабатывает не где-либо, а в коде Worksheet_Change
Возможно на Вашем листе есть 'летучая' формула (типа СЕГОДНЯ()), которая пересчитывается при каждом 'чихе' и она вызывает событие Change. Без файла можно еще долго теоретизировать
Согласие есть продукт при полном непротивлении сторон
 
 
 
#8
29.01.2018 17:09:04
Казанский, Не все так просто в нашем королевстве.
Книга представляет собой набор макросов для загрузки данных из другой книги и обратной выгрузки изменений. Данных грузиться может много разносторонних. Это выбирает пользователь. Каждая новая таблица с данными грузится на динамически создающийся кодом лист. Правильнее сказать не создающийся, а копирующийся из скрытой шаблонной заготовки со всеми обработчиками листа.
Таким  образом на момент открытия книги неизвестно ни число листов с данными (оно все время меняется динамически в работе) ни номер последней заполненной строки на каждом из них. По запросу макросы формируют очередной лист данных. Пользователь его модифицирует и по команде сохранения изменения перебрасываются в исходный файл, откуда они были изначально взяты. Поскольку объем данных отрисовки на отдельном листе может быть крайне велик (иные таблицы у меня даже не умещаются в 65000 строк и имеют более 100 колонок с данными и подгружаются на лист частями) то сохранять все подряд по времени слишком длительно. Особенно когда происходит промежуточное сохранение не больших правок. Для устранения это проблемы реализована подпраграмма, заполняющая массив номеров строк (ключевых индексов), в которых пользователь производил изменения данных. И по команде сохранения производится анализ изменений только в этих строках. Поскольку удаление строк с не нужной в данный момент частью данных при помощи фильтров - операция очень часто встречающаяся, то возникает проблема. Когда на листе несколько тысяч строк и удаляется тысяча не смежных в середине списка, Обработчик изменений встревает и  отправляет эксель в аут. Операция становится столь длительной, что приходится просто сбрасывать контекст и начинать заново.
 
 
 
#9
29.01.2018 17:21:56
Цитата
Sanja написал:
Приложите Файл-пример. Как есть - Как надо. С вашими обработчиками события.
Вот Вы мне делемму задали. Если я приложу тот файл, который рабочий у меня, то мало не покажется. Там два мегабайта кода, который дописывался 10 лет день ко дню. Уровень вложенности подпрограмм в работе достигает 7-10.
А простой пример уже написал в начале. Не знаю, обрамить его чтоли в Xls оболочку?
Не могу я в нужном месте включить, в нужном выключить. Потому что код начинает срабатывать как только пользователь шевелит данные на листе и мне нужно это шевеление все время контролировать. Запятую поставил в ячейке - нужно, чтобы код внес в отдельный массив номер строки, в которой внесена запятая.
Добавил какие-то днные в новую строчку - опять обработчик должен включиться и поместить в массив отметку номера строки, в которой были сделаны изменения.
А вот если пользователь тупо удалил несколько строк - в моей задаче это не считается изменением данных, поэтому нужно это событие игнорировать и не анализировать произошедшее вообще никак.
Изменено: Neufazendnik - 29.01.2018 17:23:08
 
 
 
#10
29.01.2018 17:24:14
Цитата
Neufazendnik написал: Если я приложу тот файл, который рабочий у меня...
простой пример уже написал в начале
Ни одно, ни другое неправильно. Читайте в правилах о ФАЙЛЕ-примере.
 
 
 
#11
29.01.2018 17:30:45
Цитата
Neufazendnik написал: Там два мегабайта кода, который дописывался 10 лет день ко дню. Уровень вложенности подпрограмм в работе достигает 7-10.
Обработчик изменений встревает и  отправляет эксель в аут
Офф. Думаю предел достигнут. Пора разгребать все эти 'вложенности', пересматривать логику, начинать использовать современные инструменты, возможно переводить все это в программы для работы с базами данных ни т.д. и т.п.
Согласие есть продукт при полном непротивлении сторон
 
 
 
#12
29.01.2018 18:09:58
Цитата
Sanja написал:
Офф. Думаю предел достигнут. Пора разгребать все эти 'вложенности'
Офф.
Цель сначала создавать, потом разгребать? У меня получилось нечто типа НАДэкселя. Я с одной стороны имею универсальную базу данных , которая живет полностью своей жизнью, и имеет свою архитектуру, как независимый программный продукт. С другой позволяет использовать всю мощность рабочих листов экселя, а она, как Мы знаем не хилая и ее воспроизводить в союбственном программном продукте жизни не хватит. Мне очень удобны механизмы фильтрации, многочиленные преобразования текста и формулы, которыми можно играть на лету в любой момент. Это крайне мощный инструмент. Перенести его на самостоятельную платформу действительно пора. Это будет отдельный продукт, но я неминуемо потеряю мощ экселя и пока этот минус столь велик, что я не вижу смысла переписывать код, как самостоятельный.
Изменено: Neufazendnik - 29.01.2018 18:15:25
 
 
 
#13
29.01.2018 18:13:34
Ну как минимум более комфортная работа с данными. Или Вас устраивает нынешняя ситуация, с 'тормозами', вылетом Excel? Дело, конечно, хозяйское...
Согласие есть продукт при полном непротивлении сторон
 
 
 
#14
29.01.2018 18:18:23
Цитата
Sanja написал:
Или Вас устраивает нынешняя ситуация, с 'тормозами', вылетом Excel?
Данная ситуация - приблизительно одна сотая из всех ситуаций, которые возникают. Примитивные варианты преодоления и обхода всегда можно найти. Как то например перед удалением строк тыкать по спец кнопке, завязанной на код с enableevents=false
Но хочется же сделать красиво, чтоб комар носа не подточил и пользоваться не тыкая лишних кнопок, если их можно не тыкать.
 
 
 
#15
29.01.2018 18:22:39
Цитата
vikttur написал:
Ни одно, ни другое неправильно. Читайте в правилах о ФАЙЛЕ-примере.
Подумаю. Если времени хватит набросать, то пострараюсь в ближайшее время что-нибудь сгондобить и прикрепить сюда.
 
 
 
#16
29.01.2018 18:23:48
Цитата
Neufazendnik написал: Но хочется же сделать красиво
Красиво - это когда все работает. Без этой 'одной сотой'. Да и вообще Вам 'шашечки' или ехать? Я Вас не в чем не убеждаю, так, высказал свое мнение. Вы же не одной строчки из этих мегабайт СВОЕГО кода не показали. Чем Вам помочь? Про отключение обработки событий Вам сказали  
Согласие есть продукт при полном непротивлении сторон
 
 
 
#17
29.01.2018 18:35:36
Цитата
Sanja написал:
Вы же не одной строчки из этих мегабайт СВОЕГО кода не показали
Const ge129 = 60000 'число строк активного контекста одновременно может быть изменено
Dim ge102(10, ge129) As Long 'номера для ge129 строк в контексте 10, в которых были изменения данных, и эти строки нужно сохранить при сохранении контекста.
' ge102(10,0) - число измененных строк актуально в 10 контексте на данный момент.
Public ge18 As Byte 'текущий уровень вложенности отрабатываемого контекста (заполняемого в данный момент типа) = номер активного листа (листы имеют иерархию следования)
'Контекст = самостоятельный лист с данными.
Код
Sub hi(Target As Range)
'проставляет номера строк, в которых были сделаны изменения данных в массив ge102
'эта подпрограмма отрабатывается до selectionCange, поэтому vc1 содержит именно строку, которая модифицировалась, а не строку, в которой уже находится фокус ввода.
Static hi03 As Long 'уже вписанное в ge102 до этого число строк.
Static hi04 As Long 'текущий номер индекса в ge102
Static hi05 As Long 'номер элемента в  ge102
'hi06 -loop
Static hi07 As Long 'число вписанных элементов в ge102
Dim hi09 As Long 'номер строки, вписываемый в ge102
Dim hi10 'элемент target
Dim hi11 As String 'address hi10

hi07 = 0
hi03 = ge102(ge18, 0)
For Each hi10 In Target
  hi11 = hi10.Address
  hi09 = Range(hi11).Row
  If hi09 > ge126 Then 'на строки заголовка и вышележащие не реагируем.
    hi04 = hi03 + hi07
    For hi05 = 1 To hi03 + hi07
      If ge102(ge18, hi05) = hi09 Then GoTo hi06
      Next
    hi04 = hi03 + hi07 + 1
    If hi04 > ge129 Then
      If ge102(ge18, 0) <> ge129 Then MsgBox ("Изменена информация одновременно более, чем в " & ge129 & " строках. Сохранение будет произведено только для первых " & ge129 & " изменений. Остальная модифицированная информация не сохранится.")
      ge102(ge18, 0) = ge129
Exit Sub
      End If
    hi07 = hi07 + 1
    ge102(ge18, hi04) = hi09
    End If
hi06:
  Next
ge102(ge18, 0) = hi03 + hi07
End Sub
 
 
 
#18
29.01.2018 19:30:36
Цитата
Neufazendnik написал: Примитивные варианты ... Как то например перед удалением строк тыкать по спец кнопке, завязанной на код с enableevents=false
Причем тут кнопка с вкл/выкл? Посмотрите в файле-примере пример работы событийной процедуры с отключением обработки и без нее.
Код
Private Sub Worksheet_Change(ByVal Target As Range)
Stop
If Not Intersect(Target, [A1]) Is Nothing And Target.Count = 1 Then
    Application.EnableEvents = False
    [B1] = Target + 10
End If
Application.EnableEvents = True
End Sub
Изменяйте значение в ячейке A1 и пройдите по-шагово по коду с отключенной 4-й строкой, а затем со включенной.
Согласие есть продукт при полном непротивлении сторон
 
 
 
#19
29.01.2018 19:40:54
Цитата
Neufazendnik написал:
Когда на листе несколько тысяч строк и удаляется тысяча не смежных в середине списка, Обработчик изменений встревает и  отправляет эксель в аут.
Быстрее скопировать-вставить отфильтрованные строки на новый лист (или диапазон ниже таблицы) и удалить исходный лист (или исходную таблицу).
 
 
 
#20
29.01.2018 20:41:23
Поставьте условие в начале. При вставке или удалении строк, у Вас не будет аутов. Зачем усложнять себе жизнь. Или определите допуск на обработку измененных ячеек. Когда вы вставляете или удаляете столбец то их больше 1000 000 потому и виснет, при строках зависит от ваших данных от 16000.
Вы должны отдавать себе отчет сколько ячеек меняются для Вашего события определите и поставьте этот порог.Может пользователь вообще меняет одну ячейку, или пару! Зачем по всем гонять.!
Код
if target.count>10000 then exit sub
Изменено: skais675 - 29.01.2018 20:42:49
Мой канал
 
 
 
#21
29.01.2018 22:17:00
Цитата
Sanja написал:
Изменяйте значение в ячейке A1 и пройдите по-шагово по коду с отключенной 4-й строкой, а затем со включенной.
Спасибо. Я Ваш пример посмотрел. И? Каким образом Вы предлагаете его применить в моем случае? У меня есть подобное управление в некоторых процедурах, работающих с DoEvents. В частности прерывание их выполнения путем внесения какого-либо содержимого в некоторую предустановленную ячейку. По сути своей это та же самая кнопка. Лишь реализация на ином урровне, но с точки зрения интерфейса - мыло мыльное.
Изменено: Neufazendnik - 29.01.2018 22:28:29
 
 
 
#22
29.01.2018 22:25:10
Цитата
Казанский написал:
Быстрее скопировать-вставить отфильтрованные строки на новый лист (или диапазон ниже таблицы) и удалить исходный лист (или исходную таблицу).
Ну так это один из вариантов, как из подобных проблем можно выходить да и выхожу порой, когда совсем тоскливые случаи. В принципе, задача действительно не на столько остра, чтобы парализовывала работу, но достает все равно, т.к по нескольку раз в день порой ее делать приходится. Задумался, как уж раз и навсегда заткнуть и это неудобство, коих в общем-то хватает и без него. Кстати, за идею с контролем числа строк спасибо. Я вот думаю, что ее можно применить следующим образом: включать функцию подсчета ключевых индексов, которыми снабжена каждая строка в скрытой технической графе. Если оно расходится с числом, которое было в процессе изначального автоматического наполнения листа или последнего сохранения данных, то значит пользователь что-то нахимичил с числом строк и нужно тупо абортировать исполнение пп в таком случае.
Изменено: Neufazendnik - 29.01.2018 22:26:01
 
 
 
#23
29.01.2018 23:05:10
Цитата
skais675 написал:
Поставьте условие в начале.
Ну, а как понять, много элементов в target возникло от элементарного сдвига половины списка или от скажем выделения какого-то столбца и изменения его через <ctrl>-<enter>?
В первом случае ожидание отработки ПП. бессмысленно. Во втором - необходимость, связанная с реальным изменением данных.
Цитата
skais675 написал:
Или определите допуск на обработку измененных ячеек.
Дак это уже сделано. Константа ge129 в моем коде заведует данным вопросом. Когда то ее значение было на уровне всего 500. Задолбада система предупреждениями. Не хватает мне этого никак. Со временем дотянул вот до 60 тыс. Уровень вроде несусветно большой. Но при работе с непрерывно расположенными данными перемены в таких больших массивах возможны всего в два клика. Например стыкую прайс поставщика с бывшими в базе ранее его версиями. У поставщика в прайсе около 12 тысяч строк. Предшествующие версии дают около 50 тысяч строк. Задача пометить свежие 12 тысяч текущей датой, чтобы видно было в общем списке из 50 тысяч, какие строки актуальны и относятся именно к последнему прайсу. Тривиальная по сути задача. А ресурсы на обработку этих операций выделяются уж не хилые.
Цитата
skais675 написал:
Вы должны отдавать себе отчет сколько ячеек меняются для Вашего события определите и поставьте этот порог.
Отдавать отчет практически не реализуемо. Код обработчика универсален и один для разных листов, заполненных совершенно разной информацией. На каком-то листе пользователь будет работать с таблицей из десяти строчек, а на каком-то все 65 тысяч будут заполнены. И во всех случаях отрабатывает один и тот же обработчик. Более того, отрабатывает он в накопительном режиме от команды сохранения изменений до следующей команды сохранения изменений. Человек может сидеть и часами править один и тот же лист, то в одном столбце, то в другом. Обработчик все это время заполняет и заполняет массив ge102 , вписывая в него номера строк, которые претерпели за все это время редакции изменения. Таким образом изменено может быть всего три строки, а может и тридцать три тысячи три. Переменная ge102(?,0) показывает число измененных строк, которое уже занесено на данный момент в массив ge102(?,?) Как только пользователь даст команду сохранения изменений, только после этого все накопленные данные данные можно будет сбросить простым сбросом счетчика ge102(?,0) элементов, помещенных в массив.
Изменено: Neufazendnik - 29.01.2018 23:26:42
 
 
 
#24
30.01.2018 00:08:04
Цитата
Neufazendnik написал: Хочу либо блокировать Worksheet_Change при удалении строк, либо поставить обход кода внутри обработчика Worksheet_Change, если он вызван удалением строк.
Не читал всё остальное в теме, но обнаружить удаление строк легко таким образом:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
  
  ' Проверить попытку удаления строк
  If Target.Address = Target.EntireRow.Address Then
    MsgBox "Удаляются строки, игнорируем обработку"
    Exit Sub
  End If
  
  ' Ниже - код обработки  остальных случаев
  ' ...
  
End Sub
Изменено: ZVI - 30.01.2018 01:46:22
 
 
 
#25
30.01.2018 00:31:56
На кнопку повесить макрос удаления выделенных строк с отключением событий и научить пользователей жать на эту кнопку?

Ну и о методах оптимизации стоит подумать:
- все операции с множеством ячеек через массивы/словари/коллекции
- если ведется автоматический лог операций в отдельной вкладке, то стоит ее при каждом запуске обрезать или скидывать в отдельный файл.
- ну и элементарные: отключения обновлений экрана, событий, калькуляций.
Изменено: Anchoret - 30.01.2018 00:40:20
 
 
 
#26
30.01.2018 07:40:39
Цитата
ZVI написал:
обнаружить удаление строк легко таким образом:
Супер! То, что нужно! Буду пробовать!
 
 
 
#27
30.01.2018 07:47:56
Цитата
Anchoret написал:
На кнопку повесить макрос удаления выделенных строк с отключением событий и научить пользователей жать на эту кнопку?
Спасибо! Конечно же это тоже вариант. Совсем не думал в это сторону, а ведь идея тоже хорошая! Но с появлением более изящного варианта
If Target.Address = Target.EntireRow.Address Then
   MsgBox "Удаляются строки, игнорируем обработку"
Думаю уже нужда делать отдельные процедуры отпадает. Попробую его откатать. Если возникнут непредвиденные проблемы, то вероятно пойду именно путем, предложенным Вами. Только не кнопка будет а сочетание горячих клавиш. Кнопкам для подобных тонкостей нет места на моих листах. Если каждую подобную функцию реализовывать отдельной кнопкой, то пол листа будет в кнопках.
 
 
 
#28
30.01.2018 07:51:21
Цитата
Anchoret написал:
Ну и о методах оптимизации стоит подумать:
- все операции с множеством ячеек через массивы/словари/коллекции
- если ведется автоматический лог операций в отдельной вкладке, то стоит ее при каждом запуске обрезать или скидывать в отдельный файл.
- ну и элементарные: отключения обновлений экрана, событий, калькуляций.
Все это естественно реализовано. К этому давно уже пришел. Иначеб эксель у меня из зависания не выходил бы вообще :)
 
 
 
#29
30.01.2018 08:29:56
ZVI,
If Target.Address <> Target.EntireRow.Address
Я плакаль...
Не работает. Может все дело в том, что удаляются строки не смежные? Код проваливается сквозь этот IF
Еще мысль в этом направлении - попробовать проверку на подсчет числа измененных столбцов. Если изменения по ширине всего рабочего листа, значит явно была операция со всей строкой. А кроме удаления строки других изменений всей строки в общем-то и не предусмотрено. Буду пробовать набросать рабочий код.
Попробовал...
Target.EntireColumn.Count - тоже не вариант. Засада.
А вот если Target заменить на Selection, то все супер! Ведь в момент, предшествующий срабатыванию события были выделены строки целиком.
Selection.EntireColumn.Count в моем случае возвращает 16384. По моему это решение вопроса. Буду обкатывать!
только не могу найти, какая предопределенная константа заведует за число доступных в книге столбцов? Есть же такая?
Изменено: Neufazendnik - 30.01.2018 08:45:56
 
 
 
#30
30.01.2018 08:49:18
Цитата
Neufazendnik написал:
А кроме удаления строки других изменений всей строки в общем-то и не предусмотрено.
А добавление строки?
Цитата
ZVI написал:
обнаружить удаление строк легко таким образом:
Привет Володя.
Код
MsgBox "Удаляются или ДОБАВЛЯЮТСЯ строки, игнорируем обработку"
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх