после выполнения кода используемая память увеличивается примерно на 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 пишет: По ощущениям, после Range.Delete - в памяти хранится копия листа, содержащая лист "до удаления".
Неверные у Вас ощущения. Если удалять макросом, то в памяти не хранится лист "до удаления" - можете сами проверить простым способом: 1. Запустить новый процесс Excel. 2. Вставить в ячейку A1 активного листа что-нибудь. 3. Выполнить процедуру (или в Ctrl+G): Rows(1).Delete.
И Вы увидете, что значок "Отменить" не активен. Ежели удалять строки через интерфейс Excel'я, то тут да - Отменить заработает. И можно ещё выполнить такую процедуру (тоже можно в Ctrl+G): ActiveSheet.UsedRange.
Суть в том, что именно после .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 - ни как не могу разобраться.
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With WsR1.Range("A2:BG" & WsR1.AutoFilter.Range.Rows.Count).Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
на
Код
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
Год назад столкнулся с подобным. Требовалось создать 30 копий листа (2500 строк 6 столбцов форматированного и раскрашенного текста), найти на каждом по условию строку i и удалить все строки от i до конца данных. Копировалось нормально, а при удалении уже на четвертом листе Excel зависал наглухо. Оказалось, в паре строк была применена заливка ко всей строке. После удаления этой заливки макрос отработал нормально. Так что поглядите размер UsedRange.
Код не менял, но добавил строки 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
Решение теперь есть, всем спасибо! Но хотелось бы понять почему такое происходит?
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 пишет: Эта строка обновляет используемый Экселем диапазон. Можно определить по полосам прокрутки.
Однако сам размер диапозона не менется, точнее меняется но в меньшую сторону.
Потом где-то в другой теме (в интересных вопросах?) спросил Владимира (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 пишет: Для 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 - не процедура, неверно.
Вы пишете: Ну, давайте разберёмся. Запускаем 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
посмотрел файл в этом посте. Не удивлен, что что-то не работает или тормозит. Это ж ппц Предлагаю Вам сначала привести код в нормальный вид, а потом искать причину, почему не работает.
Чебурашка стал символом олимпийских игр. А чего достиг ты? Тишина - самый громкий звук
Вот и пытаюсь , ни когда VB и другие языки не изучал, да и литературку не читал, по этому и пытаюсь постепенно оптимизировать то, что написал чуть ранее.
Всем спасибо за ответы. Хотя, если честно так и не понял причину увеличения памяти при удалении(а увеличивалась она как раз на размер загруженного в память файла )и почему не выгружалась из памяти при закрытии файла.
Спасибо, это я пробывал и это в данной процедуре уместно, но к примеру есть ситуации когда необходимо именно удалить строку, в таком случае заменить на .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."
Не всегда usedrange начинается с начала листа. Т.е. при UsedRange.Rows(j + 1 & ":" & j + 1).Delete может удалиться не то, что ожидаете, нужно скорректировать с учётом UsedRange.Row.