Имеется необходимость отследить строки, в которых изменились ячейки. Собственно, простейший макрос:
Код
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: отследить строки, в которых изменились ячейки
а для чего? Если для отката, то есть хороший способ: задублировать лист, на котором макрос выполняет изменения и, при отмене, просто удалить его, а при подтверждении удалить исходник
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, здрасьте Ситуация такая: имеем в загруженном интерфейсе лист Excel. Путем скрытия/открытия блокировки/разблокировки столбцов заносим туда разные данные. От этих данных зависят другие данные, т.е. мы занесли какие-то значения, поменяли, и нужно пересчитать зависящие от измененных ячеек данные. Так вот, чтобы из 1006 строк не пересчитывать все подряд, потребовалось пересчитать только те строки, в которых были произведены изменения.
Как-то так
Насколько я понимаю, нужно считать диапазон до изменения в массив, потом после изменения в массив. Потом сравнить массивы и, следовательно, получить измененные строки. Вот ищу алгоритмы теперь как быстро сравнить два массива.
Diana Tailor: пересчитать только те строки, в которых были произведены изменения
почитайте про Application.Calculate, Application.CalculateFull и Application.CalculateFullRebuild (обычно мне хватает первого). Если он пересчитывает очень долго, то стоит пересмотреть структуру организации данных и связей между ними.
…можно очень быстро. Создавайте отдельную тему с примером и поможем Опять же, если Application.Calculate справится, то и сравнивать ничего не придётся.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Calculate - это круто, конечно, но снова-таки, учитывая размерность моих данных - Range("A7:SW1006") тут любой calculate загнется, поэтому расчеты происходят не автоматически, а в VBA по нажатию кнопки )
По поводу массивов - посмотрю, вроде у Димы Щербакова на сайте видела или у Игоря неплохие решения, вот ищу
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
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
Вычисление листов в 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. Далее, уже сверять изменились ли данные - было/стало и делать пересчет по изменениям. Пока не совсем ясно. как изменяется ваш большой массив, пользователь вставляет данные сразу массивом? Как с ним работает пользователь? зачем пользователю тысячи строк и множество столбцов для изменения? Если вставляется массив, обрабатывайте сразу массивом изменения.
Пока не совсем ясно. как изменяется ваш большой массив, пользователь вставляет данные сразу массивом? Как с ним работает пользователь?
Приложила более подробный пример. Вводим данные путем поячейного ввода, как в толбец, так и в строку, либо путем копирования диапазона из одной книги в другую.
Например, ввели/изменили значение в D7, возникла необходимость посмотреть, как изменились значения в C7, F7, G7. Нажали кнопочку, все посчиталось. Поэтому необходим весь гигантский диапазон "под рукой".
Или, пакетно ввели информацию в ячейки D7:E17, нажали рассчитать, посмотрели, что получилось везде.
После сохранения, происходит выгрузка информации со всего листа в базу данных (это уже отдельная история, пока у меня просто копируется лист в новый файл, сохраняется в отдельную книгу, так как до БД мне далековато на VBA).
Перерасчет ячеек идет построчно, т.е. если значения менялись только в одной строке, то перерасчет будет только в этой же строке...
В ячейки, например, вводим разновидность льда, его температуру, массы пустого оборудования, массу оборудования, заполненного льдом, цвет льда и т.д. В других (заблокированных) ячейках уже вычисляются его характеристики: плотность, коэффициенты оттаивания и др. В зависимости от этих рассчитанных характеристик вычисляются другие - углы преломления, спектральные характеристики и т.д.
Очень много параметров рассчитывается в одной строке
Т.е. идет список точек опробования льда в столбце 1, а в других столбцах - параметры, соответствующие точке опробования.
На данный момент штудирую Range.Dirty, но, что-то мне подсказывает, придется обойтись свободным столбцом, в нем помечать, что в данной строке были изменения через Target, и, следовательно, пересчитать только эти строки. Более простого решения пока не нашла.
БМВ, я видела эту штуку мощно я правильно понимаю алгоритм, что нужно будет прописать формулы в расчетных ячейках, протянуть их на все 1000 строк моих, поставить ручную калькуляшку, и калькулировать уже конкретный диапазон строк?
Diana Tailor, лучшим решением, если есть такая возможность, будет расчет в самом листе Excel. Во-первых - сам Excel позаботится о пересчете изменений. Во-вторых это будет происходить в многопоточном режиме, в отличии от VBA. Собственно,БМВ, пишет о том же.
Выражаю слова благодарности всем, кто принял участие в обсуждении данной темы. Также спасибо модераторам за терпение! Перешла на Calculate в автоматическом режиме, работает значительно быстрее, чем в VBA.
Diana Tailor написал: работает значительно быстрее, чем в VBA.
А я говорил :-) . Diana Tailor, часто макрушники забывают про то, что сперва был табличный процессор потом к нему появился макро язык , на котором писать было прям скажем не удобно. С появлением VBA стало намного легче, но не следует превращать Excel в набор таблиц с данными и оболочку для обработки из при помощи скриптов. Есть то что без VBA не сделать, и всему есть свое применение. Успехов.