Страницы: 1
RSS
VBA. Событие изменения конкретных строк
 
Здравствуйте.

Имеется необходимость отследить строки, в которых изменились ячейки.
Собственно, простейший макрос:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub

Если мы по одной ячейке изменяем, все корректно. Если работаем с диапазоном – не очень.

Пример: пустой лист.
Код
Range(“A1”)=1
Range(“A3”)=1

Как видно, в ячейке A2 значений нет. Если выделить диапазон A1:A3 нажать Delete, то Excel считает, что изменения произошли во всем диапазоне, хотя по факту только в 1 и 3 строке.

Как отследить значения именно измененных строк? (т.е. если не было значений и удалились – это уже не изменение, а если были и удалились/поменялись – уже изменение).

Спасибо.
Изменено: Diana Tailor - 07.12.2018 12:16:39
 
гугол подсказывает...
Код
Dim vOldVal 'Must be at top of module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    vOldVal = Target
End Sub
второй вариант, там же предложенный, использование отмены предыдущего действия, так же справедлив и в некоторых случаях может быть производительнее.
Изменено: DenSyo - 07.12.2018 09:32:50
 
DenSyo, спасибо. Пытаюсь разобраться
 
Diana Tailor, доброго утра  :)
Цитата
Diana Tailor: отследить строки, в которых изменились ячейки
а для чего? Если для отката, то есть хороший способ: задублировать лист, на котором макрос выполняет изменения и, при отмене, просто удалить его, а при подтверждении удалить исходник
Изменено: Jack Famous - 07.12.2018 10:25:39
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Jack Famous, здрасьте :)
Ситуация такая: имеем в загруженном интерфейсе лист Excel. Путем скрытия/открытия блокировки/разблокировки столбцов заносим туда разные данные.
От этих данных зависят другие данные, т.е. мы занесли какие-то значения, поменяли, и нужно пересчитать зависящие от измененных ячеек данные.
Так вот, чтобы из 1006 строк не пересчитывать все подряд, потребовалось пересчитать только те строки, в которых были произведены изменения.

Как-то так :)

Насколько я понимаю, нужно считать диапазон до изменения в массив, потом после изменения в массив. Потом сравнить массивы и, следовательно, получить измененные строки.
Вот ищу алгоритмы теперь как быстро сравнить два массива.
 
Цитата
Diana Tailor: пересчитать только те строки, в которых были произведены изменения
почитайте про Application.Calculate, Application.CalculateFull и Application.CalculateFullRebuild (обычно мне хватает первого). Если он пересчитывает очень долго, то стоит пересмотреть структуру организации данных и связей между ними.
Цитата
Diana Tailor: сравнить массивы
…можно очень быстро. Создавайте отдельную тему с примером и поможем  ;) Опять же, если Application.Calculate справится, то и сравнивать ничего не придётся.
Изменено: Jack Famous - 07.12.2018 10:58:57
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Calculate - это круто, конечно, но снова-таки, учитывая размерность моих данных - Range("A7:SW1006") тут любой calculate  загнется, поэтому расчеты происходят не автоматически, а в VBA по нажатию кнопки )

По поводу массивов - посмотрю, вроде у Димы Щербакова на сайте видела или у Игоря неплохие решения, вот ищу :)
 
Jack Famous, Calculate можно применять к конкретному диапазону. Пример из справки:
Код
Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
склоняюсь к варианту с undo/redo, если уж продолжать в этом направлении. заполнять массив на каждый клик слишком уж бессмысленно...
 
DenSyo, спасибо за комментарий.
Теперь буду искать материалы в этом направлении :)
 
Цитата
Diana Tailor: расчеты происходят не автоматически, а в VBA по нажатию кнопки
это, конечно правильно, но
Цитата
размерность моих данных - Range("A7:SW1006")
— это перебор  :D
Цитата
вот ищу
вангую, создав тему, результат будет лучше))
Цитата
JayBhagavan: Calculate можно применять к конкретному диапазону
да - я в курсе, спасибо  ;)
Изменено: Jack Famous - 07.12.2018 11:24:08
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
 
Цитата
Diana Tailor написал:
Range("A7:SW1006")
конечно все зависит от того что делается, но объем данных не такой и большой.
#2 не прокатит в случае выделения нескольких диапазонов.
По вопросам из тем форума, личку не читаю.
 
Нашла пример, разбираю :)
https://www.extendoffice.com/ru/documents/excel/3961-excel-track-changes-without-sharing-workbook.ht...
 
Если не получится отточить быстродейственный макрос, попрошу модератора перенести тему в форум основной :)
 
Diana Tailor, напишите, что вам нужно в конечном итоге, возможно вам посоветуют другой подход к решению вашего вопроса. Хорошо бы приложить и файл пример, что есть/что нужно получить.
«Бритва Оккама» или «Принцип Калашникова»?
 
Собственно, пример во вложении :)
 
Суть такова: в диапазоне (только он намного больше) изменяем ячейки, в этих же строках от измененных ячеек будут произведены дальнейшие вычисления. Вот и приходится искать строки, в которых были изменены ячейки, и в них уже принудительно в vba производить расчет
 
У меня в Логпрог данная задача остро стоит и решена следующим образом:
Листы, на которых идет контроль измененных строк содержат обработчик:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
Call hi(Target)
End Sub

Таких листов у меня несколько. Номер активного контролируется глобальной переменной ge18.
Существует процедура hi, которая заполняет глобальную переменною - массив ge102(ge18,ххх),содержающю номера для ххх строк листа ge18, в которых произошли изменения.
число ххх помещенных в массив элементов вписывается в ge102(ge18,0)
Код наверняка можно оптимизировать. Мне некогда. Меня устраивает, что он рабочий и с удалением строк справляется корректно.
Код
Sub hi(Target As Range)
'проставляет номера строк, в которых были сделаны изменения данных в массив ge102
'эта подпрограмма отрабатывается до selectionCange, поэтому vc1 содержит именно строку, которая модифицировалась, а не строку, в которой уже находится фокус ввода.
Static hi01 As Long 'номер ВЕРХНЕЙ строки, в которой произошло изменение.
Static hi02 As Long 'Число строк, которые изменялись одновременно.
Static hi03 As Long 'уже вписанное в ge102 до этого число строк.
Static hi04 As Long 'текущий номер индекса в ge102
Static hi05 As Long 'номер элемента в  ge102
'hi06 -loop
Static hi07 As Long 'число вписанных элементов в ge102
Dim hi08 As Long 'число элементов hi102 обработано
Dim hi09 As Long 'номер строки, вписываемый в ge102
Dim hi10 'элемент target
Dim hi11 As String 'address hi10

If Target.Columns.Count = Columns.Count Then Exit Sub
If TypeName(Selection) = "Range" And Selection.Columns.Count = Columns.Count Then Exit Sub
'If Selection.EntireColumn.Count  ge158 Then 'http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=100712&TITLE_SEO=100712-otslezhivanie-komandy-udaleniya-strok-iz-worksheetchange&MID=832563#message832563 Target.Address  Target.EntireRow.Address Then ' обходим случай  MsgBox "Удаляются строки, игнорируем обработку"
  hi07 = 0
  hi03 = ge102(ge18, 0)
  For Each hi10 In Target
    hi11 = hi10.Address
    hi09 = Range(hi11).Row
    If hi09 > ge126 - 1 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 If
End Sub
Изменено: Neufazendnik - 07.12.2018 12:37:35
 
Цитата
Diana Tailor написал:
Вот и приходится искать строки, в которых были изменены ячейки,
Diana Tailor, Excel сам помечает какие ячейки надо пересчитать.
Для ИНФО см. Пересчет в Excel
Цитата
Вычисление листов в Excel можно рассматривать как процесс из трех этапов:
1.Создание дерева зависимостей
2.Создание цепочки вычислений
3.Пересчет ячеек

Если вы используете VBA, возможно проставить программно ячейки для пересчета, для этого вам пригодится связка Range.Dirty -  Range.Calculate :
Цитата
Начиная с Microsoft Excel 2002, объект Range в Microsoft Visual Basic для приложений (VBA) поддерживает метод Range.Dirty, который отмечает ячейки как требующие подсчета. Когда он используется совместно с методом Range.Calculate, он включает принудительный пересчет ячеек в заданном диапазоне. Это удобно при выполнении ограниченного вычисления в макросе, где установлен ручной режим подсчета (для избежания избытка вычисляемых ячеек, не относящихся к функции макроса). Методы подсчета диапазонов недоступны через API C ( ... к моему сожалению.)

Если вы считаете все в VBA, а данные храните на листах Excel, думаю здесь в любом случае нужно идти через Worksheet_Change.
Далее, уже сверять изменились ли данные - было/стало и делать пересчет по изменениям. Пока не совсем ясно. как изменяется ваш большой массив, пользователь вставляет данные сразу массивом? Как с ним работает пользователь? зачем пользователю тысячи строк и множество столбцов для изменения? Если вставляется массив, обрабатывайте сразу массивом изменения.
«Бритва Оккама» или «Принцип Калашникова»?
 
Как много сегодня нового узнала :)

bedvit, Neufazendnik спасибо, что помогаете!
Цитата
Пока не совсем ясно. как изменяется ваш большой массив, пользователь вставляет данные сразу массивом? Как с ним работает пользователь?

Приложила более подробный пример.
Вводим данные путем поячейного ввода, как в толбец, так и в строку, либо путем копирования диапазона из одной книги в другую.

Например, ввели/изменили значение в D7, возникла необходимость посмотреть, как изменились значения в C7, F7, G7. Нажали кнопочку, все посчиталось. Поэтому необходим весь гигантский диапазон "под рукой".

Или, пакетно ввели информацию в ячейки D7:E17, нажали рассчитать, посмотрели, что получилось везде.

После сохранения, происходит выгрузка информации со всего листа в базу данных (это уже отдельная история, пока у меня просто копируется лист в новый файл, сохраняется в отдельную книгу, так как до БД мне далековато на VBA).

Перерасчет ячеек идет построчно, т.е. если значения менялись только в одной строке, то перерасчет будет только в этой же строке...
Изменено: Diana Tailor - 07.12.2018 14:28:36 (не то прикрепила :))
 
Повторю
Цитата
БМВ написал:
Diana Tailor  написал:Range("A7:SW1006")конечно все зависит от того что делается, но объем данных не такой и большой.

Что там считается?
По вопросам из тем форума, личку не читаю.
 
БМВ, простите, как-то упустила Ваше сообщение :)

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

Очень много параметров рассчитывается в одной строке :)

Т.е. идет список точек опробования льда в столбце 1, а в других столбцах - параметры, соответствующие точке опробования.
 
На данный момент штудирую Range.Dirty, но, что-то мне подсказывает, придется обойтись свободным столбцом, в нем помечать, что в данной строке были изменения через Target, и, следовательно, пересчитать только эти строки. Более простого решения пока не нашла.
 
Diana Tailor, Я не верю что для расчета таблицы нужен VBA . Функции листа справятся и снимут головняк определения что считать а что нет.

Посмотрите на это и формул много и пересчитываются все и ничего.
По вопросам из тем форума, личку не читаю.
 
БМВ, я видела эту штуку :) мощно :)
я правильно понимаю алгоритм, что нужно будет прописать формулы в расчетных ячейках, протянуть их на все 1000 строк моих, поставить ручную калькуляшку, и калькулировать уже конкретный диапазон строк?
 
Цитата
Diana Tailor написал:
поставить ручную калькуляшку, и калькулировать уже конкретный диапазон строк?
Зачем? пусть автомат считает
По вопросам из тем форума, личку не читаю.
 
Diana Tailor, лучшим решением, если есть такая возможность, будет расчет в самом листе Excel. Во-первых - сам Excel позаботится о пересчете изменений. Во-вторых это будет происходить в многопоточном режиме, в отличии от VBA. Собственно,БМВ, пишет о том же.
«Бритва Оккама» или «Принцип Калашникова»?
 
БМВ, bedvit, спасибо!
Прописываю формулы, посмотрим, что получится :)
 
Выражаю слова благодарности всем, кто принял участие в обсуждении данной темы.
Также спасибо модераторам за терпение!
Перешла на Calculate в автоматическом режиме, работает значительно быстрее, чем в VBA.
 
Цитата
Diana Tailor написал:
работает значительно быстрее, чем в VBA.
А я говорил :-) .
Diana Tailor, часто макрушники забывают про то, что сперва был табличный процессор потом к нему появился макро язык , на котором писать было прям скажем не удобно. С появлением VBA стало намного легче, но не следует превращать Excel в набор таблиц с данными и оболочку для обработки из при помощи скриптов. Есть то что без VBA не сделать, и всему есть свое применение.
Успехов.
По вопросам из тем форума, личку не читаю.
Страницы: 1
Наверх