Доброго времени суток, прошу помощи в оптимизации макросов. Есть расчетный файл, в него ежедневно добавляются обновленные исходные данные, по этим данным производится расчет, результаты сохраняются в отдельный файл, исходные данные и расчеты из расчетного файла удаляются (на следующий день все тоже самое повторяется). Данные удаляются именно очисткой содержимого ( и формата ячеек), а не путем удаления ячейки совсем. Чистятся все строки кроме заголовка и первой строки с формулами и исходными данными.
Изначально, расчет производился по одной большой и сложной формуле, она охватывала не все случаи, усложнять формулу было с каждым разом все сложнее. Было принято решение определять какой именно случай представлен в исходных данных (каждая строка отдельно) и в зависимости от случая применять конкретную формулу для этого случая, а не одну большую универсальную.
Изначально, расчет производился по формуле, которая "растягивалась" на весь массив данных макросом:
Код
Sub Было_расчет()
Range("B2").AutoFill Destination:=Range("B2").Resize(Range("A4").End(xlDown).Row - Range("B2").Row + 1) 'растянуть формулу
Calculate
End Sub
после того как формулу разделили на несколько более простых такой вариант уже не подходил, на этом форуме добрый человек подобрал решение:
Код
Sub Стало_расчет() 'ВПР основных формул
Dim lastRow As Long 'пременная для определения нижней границы диапазона
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'последняя строка по столбцу 1 (A)
Application.ReferenceStyle = xlR1C1 'стиль ссылок R1C1
FillFormulasRange Range(Cells(2, 2), Cells(lastRow, 2)), Range(Cells(2, 16), Cells(lastRow, 16)), Sheets("Стало").Range("T2:U6") 'подстановка формул по условию
Calculate
Application.ReferenceStyle = xlA1 'стиль ссылок А1
End Sub
Private Sub FillFormulasRange(rTarget As Range, rCondition As Range, rDictionary As Range) 'вспомогательное для ВПРа основных формул
Dim dic As Object
Set dic = GetDic(rDictionary)
Dim aTrg As Variant
Dim aCon As Variant
aCon = rCondition.Value
ReDim aTrg(1 To UBound(aCon, 1), 1 To 1)
Dim yc As Long
For yc = 1 To UBound(aCon, 1)
If dic.Exists(aCon(yc, 1)) Then
aTrg(yc, 1) = dic.Item(aCon(yc, 1))
End If
Next
rTarget.Cells(1, 1).Resize(UBound(aTrg, 1), 1).FormulaR1C1 = aTrg
End Sub
Private Function GetDic(rr As Range) As Object 'вспомогательное для ВПРа основных формул
Dim arr As Variant
arr = rr.Columns(1).Resize(, 2).FormulaR1C1
Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Dim ya As Long
For ya = 1 To UBound(arr, 1)
dic.Item(arr(ya, 1)) = arr(ya, 2)
Next
Set GetDic = dic
End Function
Все работает, основной расчет стал значительно точнее. НО, после того как расчет произведен, расчетный файл нужно почистить, изначально использовался макрос:
Код
Sub Было_очистка()
Rows("3:250000").Select 'выбрать строку
Selection.Clear 'очистить
End Sub
данный макрос стал в разы дольше выполнять свою задачу, обычно диапазон в 200 тысяч строк он чистил за считанные секунды, но после обновления макроса для расчета время выполнения очистки увеличилось до десятков минут. Методом тыка макрос был изменен:
Код
Sub Стало_очистка()
Rows("3:250000").Select 'выбрать строку
Selection.ClearContents 'очистить
Selection.ClearFormats 'очистить
End Sub
Если сначала почистить содержимое ячейки а затем её формат - процесс протекает быстрее, но все равно занимает несколько минут. Основное время затрачивается именно на очистку столбца в который макросом были подставлены формулы для расчета, проверял неоднократно. Если перевести формулы в значения, процесс протекает быстрее, но на то чтобы "зазначить" формулы, тоже тратится время.
Внимание вопрос: можно ли каким то образом ускорить очистку ячеек? главное - не удалять ячейки/строки насовсем, а очистить их содержимое. как вариант зазначить диапазон перед очисткой, или вообще приравнять каждую ячейку к 0, а потом очистить диапазон. главное чтобы это было быстро
Уже есть, для примера часть макроса вставил, потому этого в примере нет. пересчет формул и обновление экрана было отключено изначально, после обновления макроса никуда не делось (все что было изменено есть в примере) макрос по очистке стал работать в разы дольше, вот и хотел узнать у более опытных пользователей, с чем связано и как это решить строки не обязательно чистить целиком, просто сам факт - изменилось совсем немногое, а время ожидания возросло в разы...
Конкретно для макроса по очистке на первый взгляд ничего не изменилось, он как выполнялся в самом конце работы, так и выполняется, как чистил 100 столбцов, так и чистит. Во втором столбце как была формула, так и осталась формула. Единственное изменение - была одна большая формула, условно на 100 символов (в каждой ячейке столбца) , а теперь в каждой ячейке более короткая формула, символов на 30-50 (но в разных строках формула может отличаться от предыдущей).
не подскажешь, а пожно ли одной строкой указать несколько диапазонов с разрывом? например с ячейки А2 до столбца С, с ячейки F2 до столбца М, с ячейки Р2 до столбца Z. и все также последняя строка диапазонов не известна (End(xlUp).Row + 1) ?
одной строкой указать несколько диапазонов с разрывом
Или ещё вариант: создать имя листа (Ctrl-F3), допустим "Очистка": "=Стало!$A:$C;Стало!$F:$M". Это разгрузит код VBA от жёстко заданных диапазонов (ну, кроме условия, что строку №1 не трогаем).
Код
With Sheets("Стало")
Set r = Range(Replace(Intersect([Очистка], .UsedRange).Address, "$1:", "$2:"))
Debug.Print r.Address
End With
[Очистка] - диапазон "$A:$C,$F:$M", Intersect([Очистка], .UsedRange).Address = "$A$1:$C$41,$F$1:$M$41". Как результат r.Address = "$A$2:$C$41,$F$2:$M$41".
за совет спасибо, но не все так просто. Шаблон то как раз создан (не совсем так, как Вы имели в виду, но он есть). Даже называется "Шаблон")) вся загвоздка в том, что в этом шаблоне около 20 вкладок, штук 100 различных макросов, от самых простых до тех что посложнее, формул несколько сотен.... часть исходных данных постоянна (иногда дополняется), часть данных регулярно меняется (в нескольких вкладках сразу), периодически меняются условия - добавляются столбцы, редактируются формулы..... Шаблон каждую неделю как то изменяется, дополняется, совершенствуется. на "пустую книгу" его не поменять Причем процесс доработки шаблона часто происходит непосредственно во время расчетов, для того и придумана процедура очистки, чтобы уже на новые условия был готов шаблон
написал: With Rows("3:250000") 'выбрать строку .ClearContents 'очистить .ClearFormats 'очистить End With
60 - 70 тысяч строк очищает за несколько десятков секунд (для меня вполне нормальный результат), 100 - 120 тысяч строк чистит более 10 минут. Попробовал в несколько этапов - в макросе прописал чтобы сначала чистились первые 40 тысяч строк, затем еще 40 тысяч, и все что останется (так же 40 тысяч). В результате получил то же самое время (чуть больше 10 минут), но на 4 секунды больше( С чем связана такая большая разница выполнения очистки? (понятное дело объем в 2 раза больше - времени на очистку нужно больше, но не в 10 ведь раз, тем более даже при очистке частями результат не меняется). Можно ли каким то образом оптимизировать процесс очистки для больших объемов?
Спасибо тебе! добрый человек) действительно, в модуле книги был макрос, который запрещает удалять строки, ради теста удалил его и 120 тысяч строк почистились за за 1 минуту. для меня было не очевидно, что на 70 тысячах строк макрос не мешает, а на 120 замедляет процесс в 10 раз
mitya528 написал: действительно, в модуле книги был макрос, который запрещает удалять строки, ради теста удалил его и 120 тысяч строк почистились за за 1 минуту.