Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1 2 След.
RSS
Как очистить память после Rows.Delete, После выполнения Rows.delete, используемая Екселем память увеличивается примерно в два раз
 
Добрый День!

Столкнулся с такой проблемой.

после выполнения кода используемая память увеличивается примерно на 90мб.

Кол-во строк в диапозоне примерно 700.
WsR.Range("AD1:BG" & WsR.AutoFilter.Range.Rows.Count).Delete xlShiftToLeft

Или Вот такая строка, используется в цикле, и тоже добавляет +100мб в память, после окончания цикла
WsR.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp

WsR - это глобальный объект "Лист"
Проблема заключается в том, что листов в книге может быть много (до 160), данная оперецая производиться один раз на каждом листе, и каждый раз добавляет в память +90мб, что современем приводит к недостатку системных ресурсов.

Вот и возник вопрос, как очистить память?
По ощущениям, после Range.Delete - в памяти хранится копия листа, содержащая лист "до удаления".

При этом использование далее в коде set WSR=Nothing не приносит желаемого результата.
Теоретически должно помочь открытие и закрытие книги, которым принадлежат эти листы, но это совсем неподходит.

Если есть какие-нибудь соображения или решения, буду рад услышать.
Спасибо!
Изменено: SkyShark - 18 Янв 2013 11:09:42
 
Цитата
SkyShark пишет:
По ощущениям, после Range.Delete - в памяти хранится копия листа, содержащая лист "до удаления".

Неверные у Вас ощущения. Если удалять макросом, то в памяти не хранится лист "до удаления" - можете сами проверить простым способом:
1. Запустить новый процесс Excel.
2. Вставить в ячейку A1 активного листа что-нибудь.
3. Выполнить процедуру (или в Ctrl+G): Rows(1).Delete.

И Вы увидете, что значок "Отменить" не активен.
Ежели удалять строки через интерфейс Excel'я, то тут да - Отменить заработает.  :)  
И можно ещё выполнить такую процедуру (тоже можно в Ctrl+G): ActiveSheet.UsedRange.
Изменено: Johny - 18 Янв 2013 11:35:48
There is no knowledge that is not power
 
Приложите код целиком - возможно корень проблемы кроется совсем не в этих строках.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Johny,

Допустим, тут могу и ошибаться.

Суть в том, что именно после .Delete Shift:=xlUp увеличивается объем памяти + 90мб, это может и не было бы столь критичным, но когда 160 листов и каждый добавляет +90мб в память, это приводит к ошибке "Metod Range завершен не верно", если в этот момент переключиться на книгу и руками выделить и удалить этот диапозон, то появится сообщение о нехватке ресурсов для проведения операции.
 
Сторонний менеджер буфера обмена?
 
Нет ли кодов, срабатывающих при каком-либо событии на листе? Пробовали отключить отслеживание событий до выполнения кода: Application.Enableevents = False
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Цитата
The_Prist пишет:
Приложите код целиком - возможно корень проблемы кроется совсем не в этих строках.

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

Начало кода в модули BASIC - первый вызов модуля в котором происходит утечка памяти - строка 183, вторый вызов - строка 195. в дальнейшем ни чего подобного не происходит, но так как эти строки находяться в цикле, то утечка повторяется

Строки которые непостредственно влияют на увеличение памяти находятся в модуле Oformlenie - Выделил жирными коментариями из звездочек (*) в самом модуле Oformlenie


Hugo,
Нет, стороних быть не должно.

Цитата
The_Prist пишет:
Нет ли кодов, срабатывающих при каком-либо событии на листе? Пробовали отключить отслеживание событий до выполнения кода: Application.Enableevents = False

В листах нет ни макросов ни даже формул.

Отключаю, и не только это
Application.ScreenUpdating = False
Application.EnableCancelKey = xlDisabled
Application.ShowWindowsInTaskbar = False
Application.Calculation = xlCalculationManual
Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.DisplayCommentIndicator = xlNoIndicator



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

Почему происходит такая реакция с увеличением размера используемой памяти при Range.Delete и Rows.delete - ни как не могу разобраться.
Изменено: SkyShark - 18 Янв 2013 13:09:56
 
Избавляемся от лапши.
Заменяем
Это

на
Код
    Dim edges As Variant, edge As Variant
    
    edges = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
    
    For Each edge In edges
        With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(edge)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
There is no knowledge that is not power
 
Год назад столкнулся с подобным. Требовалось создать 30 копий листа (2500 строк 6 столбцов форматированного и раскрашенного текста), найти на каждом по условию строку i и удалить все строки от i до конца данных. Копировалось нормально, а при удалении уже на четвертом листе Excel зависал наглухо. Оказалось, в паре строк была применена заливка ко всей строке. После удаления этой заливки макрос отработал нормально. Так что поглядите размер UsedRange.
 
Johny,

Спасибо, учту в дальнейшем.

RAN,
Проверил, и вот результат

Код не менял, но добавил строки
Debug.Print WsR.UsedRange.Address
что бы посмотреть как меняется UsedRange до и после операций.
Так вот UsedRange соответсвует тому что и должно быть, при удаление уменьшается, при расширении увеличивается и строго в тех границах которые задаются кодом.

Но, что самое удивительное, покрайней мере для меня, добавление одной строки
Debug.Print WsR.UsedRange.Address
доставточно что бы память освобождалась! Проверил несколько раз, добавляя и удаляя строку при разных проходах кода результат ниже:

Используется памяти: Операция:
140036 кб До запуска модуля
239660 кб После окончания работы модуля
$A$1:$BG$780 Выполняем Debug.Print WsR.UsedRange.Address
141024 кб После Debug.Print WsR.UsedRange.Address


Решение теперь есть, всем спасибо!
Но хотелось бы понять почему такое происходит?
Изменено: SkyShark - 18 Янв 2013 15:26:56
 
Я встречал в коде такую строку:

ActiveSheet.UsedRange

Вот так просто, без ничего более.
И кажется ZVI объяснял это свойство - в деталях не помню...
 
Hugo, данная строка переопределяет диапазон пользователя. Ни для кого не секрет, что если диапазон пользователя будет равен А1:А10 и мы удалим последнюю ячейку, то Excel все равно будет считать, что последняя ячейка А10. Вот простой пример кода, который показывает наглядно описанное:
Код
Dim lLastCell As Long
lLastCell = Cells.SpecialCells(11).Row
Cells.SpecialCells(11).Delete
lLastCell = Cells.SpecialCells(11).Row

ActiveSheet.UsedRange
lLastCell = Cells.SpecialCells(11).Row
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
Сейчас посмотрел в Watches как меняется UsedRange, результат следующий:

До удаления - "$A$1:$BG$780"
После удаления - "$A$1:$AV$780" (вот тут сразу после удаления увеличивается объем занимаемой памяти)
К концу выполнения модуля - "$A$1:$BG$780"

То есть UsedRange опредиляется верно.

Однако только обращение из кода к UsedRange очищает память.

Цитата
Johny пишет:
Эта строка обновляет используемый Экселем диапазон. Можно определить по полосам
прокрутки.  
Однако сам размер диапозона не менется, точнее меняется но в меньшую сторону.
Изменено: SkyShark - 18 Янв 2013 15:56:02
 
Цитата
Я встречал в коде такую строку:
ActiveSheet.UsedRange
Эта строка обновляет используемый Экселем диапазон. Можно определить по полосам прокрутки.  :)  
И кстати, я уже писал про это, но никто, видимо, не читал:
Цитата
И можно ещё выполнить такую процедуру (тоже можно в Ctrl+G): ActiveSheet.UsedRange.
Изменено: Johny - 13 Мар 2013 18:19:59
There is no knowledge that is not power
 
Да примерно понятно, что делает. В общем. В деталях не понятно... :)
 
Какие детали? Я непонятно написал?
There is no knowledge that is not power
 
Цитата
...размер диапозона не менется, точнее меняется но в меньшую сторону.
Однако согласно свойству листа - UsedRange в моем случае не увеличивается, а уменьшается.
Цитата
До удаления - "$A$1:$BG$780"
После удаления - "$A$1:$AV$780"
 
Он подстраивается под текущий реальный диапазон.
В XML файле Экселевской книги есть такой елемент dimension.
Вот выдержка из спецификации ECMA-376:

There is no knowledge that is not power
 
Цитата
Hugo пишет:Я встречал в коде такую строку:
ActiveSheet.UsedRange
...ZVI объяснял это свойство - в деталях не помню...
Я об этом писал 12.05.2011:
http://www.planetaexcel.ru/forum.php?thread_id=27728

Потом где-то в другой теме (в интересных вопросах?) спросил Владимира (ZVI), почему ActiveSheet.UsedRange работает, а Me.UsedRange в коде листа - нет. Но он не ответил  :)
Наверно, и не стОит с этим заморачиваться. Me - несколько особый объект, так же как Selection и кое-что еще  ;)
 
Обычно для восстановления используемого диапазона я применяю такой код:

Код
' Восстановить UsedRange 
 With Rng.Parent.UsedRange: End With

' Или:
With Sheets("Лист1").UsedRange: End With

' И т.п.


Так как по справке UsedRange – это свойство (Property), то положено его либо считывать в любую, например, временную переменную, например, так:
Set x = Sheets("Лист1").UsedRange или s = Sheets("Лист1").UsedRange.Address и т.п.,
либо поступить, как указано в моем коде выше – загнать ссылку на UsedRange в стек памяти с помощью With и, ничего больше не делая, выгнать из стека. Это быстрая операция.

При обращении к свойству UsedRange, так же, как и при сохранении книги происходит восстановление используемого диапазона листа. Код для проверки этой функциональности я приводил в той теме «Сортировка цифр с буквами», про которую упомянул Алексей (Казанский):
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=8&TID=41590

Алексей, честно говоря, я не понял тогда, зачем использовать не всегда работающую недокументированную возможность обращения к UsedRange не как к свойству объекта Get, а как к процедуре Sub. Выигрыш-то такого варианта во времени по сравнению с временем восстановления UsedRange практически нулевой, к тому же работает не всегда. Недокументированное свойство ненадежно тем, что сегодня работает, а завтра в новой версии/обновлении может молча и исчезнуть.

Если разбираться ради интереса, то много придется написать, т.к. краткость не моя сестра.

Очевидно, что элементы коллекции Sheets, Worksheets, а также объект ActiveSheet по своему назначению могут и должны ссылаться на различные экземпляры объектов (классов листов), такое реализуется косвенной адресацией на объект с помощью индекса.

У Sheets, Worksheets есть свойство по умолчанию Item, аргумент которого представляет, по сути, индекс либо числовой, либо текстовый, например, Sheets.Item(1), Worksheets.Item("Лист1"). Так как  Item является свойством по умолчанию, то его можно  явно не указывать и записывать Sheets(1), Worksheets("Лист1").

Для ActiveSheet неявным индексом является индекс активного листа.
Для Sheets.Add индекс определяется аргументами [Before], [After] или индексом активного листа.

Для реализации косвенной адресации объекты Sheets.Item(IndexOrName) Worksheets.Item(IndexOrName) в реализации объявлены As Object.
Чтобы проверить это: наберите Sheets.Item(1), установите курсор на любой символ в Item и нажмите Shift-F2, внизу окна "Object Browser"  отобразится декларация:
Property Item(Index) As Object (read only)

As Object означает, в частности, что имеем дело с поздним связыванием, в отличии от раннего связывания при котором используется прямое обращение к объекту экземпляра класса.

При обращение к объекту не косвенно с помощью дополнительной переменной, а напрямую с помощью кодового имени Лист1 или Me работает другой интерфейс, который быстрее и позволяет реализовать события.

Надо полагать, что в интерфейсе позднего связывания UsedRange реализован как Sub, несмотря на то, что в TypeLib написано (корректно для раннего связывания), что это все же свойство – Property, а не процедура Sub.

Примеры кода для тестирования:

Код
' Косвеная индексация, все сработает, несмотря на то,
' что код написан вопреки справке
Sub Test1()
  
  ' Здесь Sheets.Item(IndexOrName) объявлен As Object
  Sheets(1).UsedRange
  Sheets("Лист1").UsedRange
  
  ' Здесь Worksheets.Item(IndexOrName) тоже объявлен As Object
  Worksheets(1).UsedRange
  Worksheets("Лист1").UsedRange

  ' ActiveSheet также объявлен As Object
  ActiveSheet.UsedRange
  
End Sub

' А прямая индексация выдаст то, что и ожидается - ошибку
Sub Test2()
  Лист1.UsedRange
End Sub

' Теперь покажем, как реализуется косвенная адресация (интерфейс позднего связывания)
' Код сработает аналогично Test1 вопреки справке
Sub Test3()
  
  Dim Sh As Object  ' но не As Worksheet
  
  Set Sh = Лист1
  
  ' Sh и Лист1 ссылаются на один и тот же экземпляр класса
  Debug.Print ObjPtr(Sh), "=", ObjPtr(Лист1)
  
  ' Но адреса переменных Sh и Лист1 разные,
  ' Переменная Sh имеет свой адрес, по которому находится значение адреса Лист1,
  ' это и есть косвенная адресация
  Debug.Print VarPtr(Sh), "<>", VarPtr(Лист1)
  
  ' Сработает аналогично Test1
  Sh.UsedRange
  
End Sub
Изменено: ZVI - 22 Янв 2013 10:55:51
Vladimir Zakharov
Microsoft MVP – Excel
 
Цитата
ZVI пишет:
Для ActiveSheet неявным индексом является индекс активного листа.
???
Ну, давайте разберёмся. Запускаем VBE и жмём F2. В поле "Classes" выделяем "ActiveSheet" и видим - "Property ActiveSheet As Object". ActiveSheet - это свойство объекта Application.
Вопрос - где здесь индекс???

Цитата
ZVI пишет:
Надо полагать, что в интерфейсе позднего связывания UsedRange реализован как Sub, несмотря на то, что в TypeLib написано (корректно для раннего связывания), что это все же свойство – Property, а не процедура Sub.
???
Property - не процедура??? Это что-то новенькое!
Возьмём класс и стандартный модуль.
Класс Class1:
Код
Private m_Name As String

Property Get Name() As String
    Name = m_Name
End Property

Property Let Name(arg As String)
    m_Name = arg
End Property


Стандартный модуль:
Код
Private m_Name As String

Function Get_Name() As String
    Get_Name = m_Name
End Function

Function Let_Name(arg As String)
    m_Name = arg
End F unction


Где отличия? Read-only свойство делается путём удаления процедуры Let_Name (в модуле) и Property Let (в классе).
Если копнуть ещё глубже, то возьмём VB.NET и создадим там такое же свойство Name:
Код
Private m_Name
Public Property Name() As String
   Get
      Return m_Name
   End Get
   Set(ByVal arg As String)
      m_Name = arg
   End Set
End Property

Компилятор транслирует Property в две процедуры: get_Name и set_Name. Поэтому Ваше, Владимир, утверждение, что Property - не процедура, неверно.
There is no knowledge that is not power
 
Ответ Johny

Добрый день,  Johny!

Цитата
Вы пишете:
Ну, давайте разберёмся. Запускаем VBE и жмём F2. В поле "Classes" выделяем "ActiveSheet" и видим - "Property ActiveSheet As Object". ActiveSheet - это свойство объекта Application. Вопрос - где здесь индекс???
Johny, свойство ActiveSheet есть не только у Application, но еще у Window и у Workbook. Везде объект ActiveSheet декларирован As Object и используется для косвенной адресации на разные экземпляры классов объектов листов. Объект ActiveSheet при одном и том же своем адресе VarPtr(ActiveSheet) может указывать на различные адреса листов ObjPtr(ActiveSheet).
Убедитесь в этом проверкой в Immediate этих адресов при активации различных листов:
?VarPtr(ActiveSheet)
?ObjPtr(ActiveSheet)
То есть, речь о том, что ActiveSheet реализован по принципу кода Test3

Что касается индекса, проверьте такое свойство:
?Activesheet.Index
Index здесь – свойство свойства ActiveSheet, которое я и обозвал неявным индексом, в том смысле, что он задается не явно агрументом, а методом Sheets(IndexOrName).Activate или .Select.


Цитата
Property - не процедура??? Это что-то новенькое!
Непонятно, где Вы нашли у меня утверждение, что свойство Property не является процедурой. Процедуры, как блок операторов, бывают разными, в VBA они могут быть: Sub, Function, Property, а в VB.NET: Sub, Function, Property, Operator, Get, Set.

Цитата
Возьмём класс и стандартный модуль.
Ваше сравнение Property с Function, по-моему, немного не по теме.
Здесь речь шла о том, что выполнить Property как Sub синтаксически нельзя.
Посмотрите пример кода ниже, чтобы уточнить, что имелось в виду:

Код модуля класса Class1
Код
' Код модуля класса Class1

Sub RestoreUsedRange()
   With ActiveSheet.UsedRange: End With
End Sub

Property Get UsedRange() As Object
  Set UsedRange = ActiveSheet.UsedRange
End Property

'Property Let UsedRange(Sh As Object)
'  Sh.Activate
'End Property


Код стандартного  модуля
Код
' Это не работает
Sub Test4()
  Dim x As New Class1
  x.UsedRange        ' <--такой синтаксис для Get/Let недопустим
End Sub

' А это работает
Sub Test5()
  Dim x As New Class1
  x.RestoreUsedRange ' <-- а для Sub это нормальный синтаксис
End Sub
Изменено: ZVI - 22 Янв 2013 14:28:45
Vladimir Zakharov
Microsoft MVP – Excel
 
посмотрел файл в этом посте. Не удивлен, что что-то не работает или тормозит. Это ж ппц  :D Предлагаю Вам сначала привести код в нормальный вид, а потом искать причину, почему не работает.
Чебурашка стал символом олимпийских игр. А чего достиг ты?
Тишина - самый громкий звук


https://github.com/nervgh
 
Вот и пытаюсь  :)  , ни когда VB и другие языки не изучал, да и литературку не читал, по этому и пытаюсь постепенно оптимизировать то, что написал чуть ранее.

Всем спасибо за ответы.
Хотя, если честно так и не понял причину увеличения памяти при удалении(а увеличивалась она как раз на размер загруженного в память файла )и почему не выгружалась из памяти при закрытии файла.  :)
Изменено: SkyShark - 13 Мар 2013 18:23:11
 
Цитата
SkyShark пишет:
так и не понял причину увеличения памяти
В процедуре WsRStartup попробуйте вместо:
WsR1.Range("AD1:BG" & c2).Delete xlShiftToLeft

Использовать:
WsR1.Range("AD1:BG" & c2).ClearContents
With WsR1.UsedRange: End With
Vladimir Zakharov
Microsoft MVP – Excel
 
Спасибо, это я пробывал и это в данной процедуре уместно, но к примеру есть ситуации когда необходимо именно удалить строку, в таком случае заменить на .ClearContents уже не получиться.

К примеру в том же модуле процедура DblLine

WsR.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp

тут требуется именно удаление.


Вопрос скорее сейчас заключается в следующем: Что может приводить к подобному увеличению памяти именно при удалении, причем видимо не в контескте моего кода, так как там "бордак", а в принципе?

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

К тому же еще интересный момент: строка
WsR.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp
находиться в цикле, но объем выростает только при первом обращении к данной строке кода и вырастает сразу на размер книги, следующий подобный прирост памяти будет только при обработке следующего листа.

Однако если приостановить выполнение макроса, вставить после
WsR.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp
строку к примеру
ur = WsR.UsedRange

и пошагово продолжить выполнение макроса, то наблюдается следующее:
после выполнение usedrange память очищается, но после следующего .Delete Shift:=xlUp
опять увеличивается.
 
SkyShark, общая причина проблем с памятью в данном случае в том, что Вы сначала устанавливаете форматы на столбцы целиком, а потом кромсаете ячейки с помощью Delete xlShiftToLeft. Форматы, установленные на весь столбец или строку, занимает мало памяти, но после такого удаления, сплошные ранее форматы столбцов фрагментируются на удаленных и смещенных влево ячейках: удаленные фрагменты теряют формат, в фрагменты форматов столбцов при этом занимают дополнительный отдельный объем памяти. UsedRange в таком случае расширяется и не восстанавливается в прежних размерах, так как Excel считает, что фрагментирование сделано сознательно.
Имейте также ввиду, что для строк все наоборот: удаление строк листа не приводит к фрагментированию форматов, установленных на столбцы листа, но приведет к фрагментированию форматов, установленных на строки листа.

То, что я Вам посоветовал с ClearContents, не разрушает непрерывности форматов и должно было помочь и при удалении строк на этом листе, если Вы где-то еще не разрываете форматы столбцов. Проверьте это.

Но общая рекомендация такая: вначале удалите все форматы с помощью WsR1.Cells.ClearFormats где вместо WsR1 подставьте объект обрабатываемого листа.
Затем выполните все необходимые преобразования данных, а в конце установите нужные форматы.
При преобразовании данных можете смело использовать Delete, но после удаления больших фрагментов ячеек и в конце обработки применяйте восстановление UsedRange с помощью:
With WsR1.UsedRange: End With

Что касается расширения используемого диапазона и, соответственно, задействованной памяти при даже простой очистке содержимого ячеек, то я это объяснял в теме "Сортировка цифр с буквами", на которою ссылался выше:
"Если где-нибудь за пределами используемых ячеек (UsedRange), например, запишем в ячейку что-нибудь, а затем очистим ее содержимое, то по Ctrl-End мы уже попадем в эту очищенную (новую последнюю) ячейку. Если сохранить книгу, то последняя ячейка возвращается на свое законное место, то же самое обычно происходит при обращении к UsedRange."
Изменено: ZVI - 23 Янв 2013 11:34:35
Vladimir Zakharov
Microsoft MVP – Excel
 
ZVI,

Спасибо, учту рекомендации и пересмотрю работу с форматами.
Отдельное спасибо за развернутые ответы!
 
Нашел еще один вариант решения:

В место строки
Код
WsR.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp

и последующих
Код
With WsR.UsedRange: End With
' или
ur=WsR.UsedRange


Можно использовать
Код
WsR.UsedRange.Rows(j + 1 & ":" & j + 1).Delete Shift:=xlUp
Изменено: SkyShark - 13 Мар 2013 10:10:47
 
Не всегда usedrange начинается с начала листа. Т.е. при UsedRange.Rows(j + 1 & ":" & j + 1).Delete  может удалиться не то, что ожидаете, нужно скорректировать с учётом UsedRange.Row.
Страницы: 1 2 След.
Читают тему (гостей: 1)