Оптимизация сценария VBA и Excel, занимающаяся 700 000 строк. Оптимизация vba

Как ускорить и оптимизировать код VBA

Хитрости » 20 Январь 2016 Дмитрий 19553 просмотров

Рано или поздно у пишущих на Visual Basic for Applications возникает проблема - код хоть и облегчает жизнь и делает все автоматически, но очень долго. В этой статье я решил собрать несколько простых рекомендаций, которые помогут ускорить работу кода VBA, при этом в некоторых случаях весьма внушительно - в десятки, а то и больше, раз. Основной упор в статье сделан на начинающих, поэтому в начале статьи приводятся самые простые методы оптимизации. Более "глубокие" решения по оптимизации кода приведены в конце статьи, т.к. для применения данных решений необходим достаточный опыт работы в VB и сходу такие методы оптимизации кому-то могут быть непонятны.

Если в коде есть много всяких Activate и Select, тем более в циклах - следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate - зачем нужны и нужны ли?

Обязательно на время выполнения кода отключить:
- автоматический пересчет формул. Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода - это может дико тормозить код, если формул много:
  Application.Calculation = xlCalculationManual
  
  Application.Calculation = xlCalculationManual
- обновление экрана, чтобы действия по изменению значений ячеек и пр. не мелькали. Зачем это надо? Т.к. все эти действия обращаются к графическому процессору и заставляют его трудиться для перерисовки экрана - это может значительно тормозить код:
  Application.ScreenUpdating = False
  
  Application.ScreenUpdating = False
- На всякий случай отключаем отслеживание событий. Нужно для того, чтобы Excel не выполнял никаких событийных процедур, которые могут быть в листе, в котором производятся изменения. Как правило это события изменения ячеек, активации листов и пр.:
  
  Application.EnableEvents = False
  
  Application.EnableEvents = False
- Если книга выводилась на печать или выводится на печать в процессе выполнения кода, то лучше убрать разбиение на печатные страницы.
  ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
  
  ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
  
  если печать производится внутри кода, то эту строку желательно вставить сразу после строки, выводящей лист на печать(при условии, что печать не происходит в цикле. В этом случае - по завершению цикла печати).
- На всякий случай можно отключить отображение информации в строке статуса Excel(в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы - иногда может все же ускорить работу кода:

www.excel-vba.ru

optimization - Оптимизация текстового поиска VBA

Я создал код VBA для текстового анализа, но во время выполнения проблемы возникла проблема. Я только что нашел в Google совет по использованию встроенной функции excel, но это не улучшило время выполнения.

Вот проблема, по которой я использую VBA. У меня есть список из ~ 30k ячеек, содержащих текст (в среднем одно или два предложения) и список из 1k ключевых слов, все из которых имеют числовую оценку. Для каждой из ячеек 30k я хочу посмотреть, какое из ключевых слов содержит ячейка, и вычислить сумму баллов найденных ключевых слов.

Вот мой способ приблизиться к проблеме в двух словах прямо сейчас:

Петля на 30k текстовых ячейках
Петля по ключевым словам
Проверьте, находится ли ключевое слово в текстовой ячейке, если да, добавьте оценку ключевого слова

Я также попытался использовать встроенную функцию поиска:

Петля по ключевым словам
Поиск ключевых слов на всем листе, содержащем текстовые ячейки 30k

Когда ключевое слово найдено, добавьте оценку в соответствующую ячейку.

Не было значительных изменений во времени выполнения.

Ниже вы можете найти мой код для первого подхода:

'Loop on all the 30k text cells For i = 2 To last_textcell 'loop on the number of different category of scores, setting intial scores to zero. For k = 1 To nb_score - 1 Score(k) = 0 Next k j = 2 'loop on the 1k keywords Do While j < last_keywords !search if the keyword is in the text cell If UCase(Sheets("DATA").Range("V" & i).Value) Like "*" & UCase(Sheets("Keywords").Range("A" & j).Value) & "*" Then 'if the keyword is found, add the score of the keyword to the previous score For l = 1 To nb_score - 1 Score(l) = Score(l) + Sheets("Keywords").Range("B" & j).Offset(0, l - 1).Value Next l End If j = j + 1 Loop 'paste the score For k = 1 To nb_categ - 1 Sheets("DATA").Range("CO" & i).Offset(0, k - 1).Value = Score(k) Next k Next i

У вас есть какие-либо советы о том, как улучшить производительность?

Большое спасибо!

задан J. Andrew 11 июля '16 в 11:15 источник поделиться

qaru.site

excel - Оптимизация VBA макросов

Есть несколько правил для VBA с тем, что вы можете сделать ваш код быстрее.

Правило № 1. Не копировать и вставлять

Копии и вставка (или PasteSpecial) работают медленно. Это примерно в 25 раз быстрее использовать следующие для копирования и вставки значений.

Range("A1:Z100").value = Range("A101:Z200").value

Если вы делаете это, ваш код, вероятно, будет работать. Возможно, проблема с Mamory, если вы делаете это во многих рядах.

Правило № 2. Расчет

Обычно Excel будет пересчитывать ячейку или диапазон ячеек, если прецеденты этой ячейки или диапазона были изменены. Это может привести к слишком частому пересчету рабочей книги, что замедлит работу. Вы можете предотвратить Excel от перерасчета книги с помощью оператора:

Application.Calculation = xlCalculationManual

В конце кода, вы можете установить режим расчета обратно в автоматический режим с утверждением:

Application.Calculation = xlCalculationAutomatic

Помните, что хотя , что когда режим вычисления равен xlCalculationManual, Excel не обновляет значения в ячейках. Если ваш макрос полагается на обновленное значение ячейки, вы должны принудительно выполнить событие Calculate с помощью метода .Calculate, например Worksheets(1).Calculate.

Правило № 3. ScreenUpdating

Другая проблема скорости с VBA - каждый раз, когда VBA записывает данные на рабочий лист, он обновляет отображаемое на экране изображение. Обновление изображения является значительным перетащить на производительность. Следующая команда отключает обновления экрана.

Application.ScreenUpdating = FALSE

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

Application.ScreenUpdating = TRUE

Правило № 4 Игнорировать события

Если у вас есть событие Worksheet_Change реализованный для Лист1 вашей книги. Каждый раз, когда ячейка или диапазон изменяются на Листе 1, запускается событие Worksheet_Change. Поэтому, если у вас есть стандартный макрос, который манипулирует несколькими ячейками в Sheet1, каждый раз, когда ячейка на этом листе изменяется, ваш макрос должен приостановиться, пока выполняется событие Worksheet_Change. Вы можете себе представить, как это поведение замедлит ваш макрос.

Application.EnableEvents = False

В конце кода, вы можете установить режим EnableEvents обратно Правда с утверждением:

Application.EnableEvents = True

Правило № 5 С утверждением

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

С утверждением, используемый в следующем примере говорит Excel, чтобы применить все изменения форматирования в одно время:

With Range("A1").Font .Bold = True .Italic = True .Underline = xlUnderlineStyleSingle End With

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

stackoverrun.com

vba - Оптимизация макроса VBA

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

Правило №1. Не копировать и вставлять

Функции Copy и Paste (или PasteSpecial) выполняются медленно. Это примерно в 25 раз быстрее использовать следующие для копирования и вставки значений.

Range("A1:Z100").value = Range("A101:Z200").value

Правило № 2. расчет

Обычно Excel будет пересчитывать ячейку или диапазон ячеек, если эти ячейки или прецеденты диапазона изменились. Это может привести к слишком частому пересчету рабочей книги, что замедлит работу. Вы можете запретить Excel пересчитывать книгу с помощью инструкции:

Application.Calculation = xlCalculationManual

В конце кода вы можете настроить режим расчета на автоматический с помощью оператора:

Application.Calculation = xlCalculationAutomatic

Помните, однако, что когда режим расчета - xlCalculationManual, Excel не обновляет значения в ячейках. Если ваш макрос полагается на обновленное значение ячейки, вы должны принудительно выполнить событие Calculate с помощью метода.Calculate, такого как Worksheets(1).Calculate.

Правило № 3. ScreenUpdating

Другая проблема скорости с VBA - каждый раз, когда VBA записывает данные на рабочий лист, он обновляет отображаемое изображение. Обновление изображения является значительным перетащить на производительность. Следующая команда отключает обновления экрана.

Application.ScreenUpdating = FALSE

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

Application.ScreenUpdating = TRUE

Правило №4 Игнорировать события

Если у вас есть событие Worksheet_Change, реализованное для Sheet1 вашей книги. Каждый раз, когда ячейка или диапазон изменяются на Листе 1, запускается событие Worksheet_Change. Поэтому, если у вас есть стандартный макрос, который манипулирует несколькими ячейками в Sheet1, каждый раз, когда ячейка на этом листе изменяется, ваш макрос должен приостановиться, пока выполняется событие Worksheet_Change. Вы можете себе представить, как это поведение замедлит ваш макрос.

Application.EnableEvents = False

В конце вашего кода вы можете установить для режима EnableEvents значение True с помощью инструкции:

Application.EnableEvents = True

Правило № 5 С выражением

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

Оператор With, используемый в следующем примере, сообщает Excel применять все изменения форматирования за один раз:

With Range("A1").Font .Bold = True .Italic = True .Underline = xlUnderlineStyleSingle End With

Вступление в привычку чередовать действия с операторами С не только ускорит работу макросов, но и упростит чтение вашего макрокода.

qaru.site

Оптимизация сценария VBA и Excel, занимающаяся 700 000 строк MS Excel онлайн

В настоящее время я работаю над скриптом, в котором есть один вложенный оператор IF. При запуске он может вычислить около 1,4 м IF.

Я провел тест с таймером (не слишком уверен в точности таймера в VBA) и хруст 1000 строк дает мне время 10 секунд. 10 * 700 = 7000 секунд, что составляет 1,94 часа.

Может ли кто-нибудь дать мне советы по оптимизации при работе с такими большими наборами данных?

Я немного старая школа, и поэтому «массивы» – ваш друг 🙂 У меня были подобные проблемы, когда я впервые занялся поиском довольно сложных электронных таблиц на работе, которые делали большое количество проверок. При работе с большими объемами данных перемещение между рабочей книгой и данными на листе не рекомендуется, так как каждое действие эффективно представляет собой операцию ввода-вывода (ввода / вывода), и они очень трудоемки. Эффективно читать все ваши данные в массиве, работать с данными массива, а затем записывать его обратно в лист в конце, это фактически 2 ввода-вывода вместо 700 000, если вы каждый раз читаете данные листа , В качестве приблизительного примера я сократил наше предыдущее время проверки с 25 минут до 4 секунд, используя этот подход.

Sub ValidateSheet() Dim DataRange As String Dim SheetArray As Variant Dim StartCol As String Dim EndCol As String Dim StartRow As Long ' long to cope with over 32k records Dim lastrow As Long Dim WorksheetToRead As String Dim ArrayLoopCounter As Long Dim Start, Finish, TotalTime Start = Timer 'I use variables for the data range simply to allow it to be changed easily. My real code is actually paramatised so a single reusable procedure 'is used to populate all my arrays 'find how many rows WorksheetToRead = "Data" StartCol = "A" EndCol = "Z" StartRow = 1 lastrow = Sheets(WorksheetToRead).Cells(Rows.Count, "A").End(xlUp).Row 'set the range to be read into the array DataRange = StartCol & Trim(Str(StartRow)) & ":" & EndCol & Trim(Str(StartRow - 1 + lastrow)) SheetArray = Worksheets(WorksheetToRead).Range(DataRange).Value ' read all the values at once from the Excel grid, put into an array 'Loop around the data For ArrayLoopCounter = LBound(SheetArray, 1) To UBound(SheetArray, 1) If SheetArray(ArrayLoopCounter, 10) <> 0 Then '10 is column J 'Compare D with J If SheetArray(ArrayLoopCounter, 4) > SheetArray(ArrayLoopCounter, 10) Then '10 is column J SheetArray(ArrayLoopCounter, 11) = SheetArray(ArrayLoopCounter, 10) 'set col K = Col J Else SheetArray(ArrayLoopCounter, 11) = SheetArray(ArrayLoopCounter, 4) 'set col K = Col D End If Else SheetArray(ArrayLoopCounter, 11) = SheetArray(ArrayLoopCounter, 4) 'set col K = Col D End If Next ArrayLoopCounter 'Write the updated array back to the sheet Worksheets(WorksheetToRead).Range(DataRange) = SheetArray Finish = Timer TotalTime = Finish - Start MsgBox TotalTime End Sub

Поэтому я принял во внимание использование Марком Мура массивов и обнаружил, что использование функции массива, а не копирование и вставка простой функции в диапазоне намного быстрее. На моей машине процедура Mark выполняется через 2.2 секунды, а другая – через 1,4 секунды.

Sub FormulaArray() Dim iUsedRows As Long, rCell As Range, StartTimer As Double, Duration As Double StartTimer = Timer iUsedRows = ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Rows.Count, 1).Row With Range(Cells(1, 11), Cells(iUsedRows, 11)) .FormulaArray = "=IF(J:J<>0,IF(D:D>J:J,J:J,D:D),D:D)" .Copy .PasteSpecial xlPasteValues End With Duration = StartTimer - Timer MsgBox Format(Duration, "#0.0000") & " seconds to run" End Sub

Я не могу проверить это прямо сейчас, но я верю, что если вы напишете функцию для замены ваших вложенных операторов IF, добавьте ее в Range («K2») с

Range("K2").Formula = ...

затем скопируйте его в ячейки (lastrow, «K»), скопируйте все функции и вставьте как значения, это будет намного быстрее.

Конечно, используя

Application.Calculation = xlCalculationManual

а также

Application.Calculation = xlCalculationAutomatic

как предложил враг, вместе с

Application.screenupdate = false

Могу сделать это немного быстрее, но я думаю, что функция-copy-paste будет иметь самое большое различие.

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

Надеюсь, это поможет!

EDIT: Вот обновленный код

ВНИМАНИЕ: Я еще не смог проверить этот код. Я сделаю это завтра и при необходимости пересмотрю.

Sub FunctionCopyPaste() Dim iLastRow as Integer With Worksheets("Data") iLastRow = .UsedRange.Cells(.UsedRange.Rows.Count,1).Row .Range("K2").Formula = "=IF(J2<>0,IF(D2>J2,J2,D2),D2)" .Range("K2").Copy Range(Cells(2,4), Cells(iLastRow,1).Row,4)) End With With Range(Cells(2,4), Cells(iLastRow,4)) .Copy .PasteSpecial xlPasteValues End With End Sub

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

Я немного изменил ваш код. Основное изменение – для каждого D в листах. В противном случае я использовал Cells (row, col) вместо Range. Не то чтобы я ожидал этого изменения, чтобы сэкономить время, я просто подумал, что вам может понравиться видеть другой способ определения ячеек, а не конкатенировать буквы и цифры. Примечание: с ячейками вы можете использовать все переменные и числа без букв. Я просто использовал письма, чтобы показать вам сходство.

Кроме того, поскольку у вас есть переменная + 1 в каждой строке, почему бы и не начать с строки 2, оставьте несколько (+ 1) и оттуда?

ООН-ПРОВЕРЕНО

Sub itS1Capped() Dim Start, Finish, TotalTime 'What are you declaring these variables as? Dim c, d, j, lastRow Start = Timer 'find how many rows lastRow = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).row 'loop through all rows For c = 2 To lastRow 'c = IT S0 Uncapped (OLD d) j = Sheets("Data").Cells(c, "J").Value 'IT Cap = Cells(c, 10) If j <> 0 Then If c > j Then Sheets("Data").Cells(c, "K").Value = j 'IT S1 Capped = j Else Sheets("Data").Cells(c, "K").Value = c 'IT S1 Capped = c End If Else Sheets("Data").Cells(c, "K").Value = c 'IT S1 Capped = c End If Next c Finish = Timer TotalTime = Finish - Start MsgBox TotalTime End Sub

изменить: заменить d на c

Вы пытались отключить автоматический пересчет, прежде чем запускать скрипт?

Application.Calculation = xlCalculationManual

А потом верните его, когда закончите?

Application.Calculation = xlCalculationAutomatic

Это обычно ускоряет обработку множества строк, предполагая, что вы не меняете то, что требует пересчета, прежде чем работать над следующими (или последующими) строками.

excel.bilee.com

Оптимизация быстродействия на VBA

Отличная статья на Хабре про то, на что надо обращать внимание, чтобы писать быстрые программы на скриптовых языках, таких как VBScript и VBA.

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

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

По моему скромному опыту, для написания быстрых программ на VBA нужно следующее:

1. Знать объектную модель Excel 2. Размазывать вычислительные задачи по программе, чтобы время отклика для пользователя всегда было небольшим. Не надо все пихать в один модуль: проверки и часть вычислений почти всегда можно сделать заранее. 3. Для хранения и извлечения данных использовать что-нибудь на SQL. Для небольших объемов и нагрузок вполне покатит тот же Access. Несмотря на его недостатки, обработка и извлечение данных SQL-запросами к базе данных Access все равно работает в десятки раз быстрее работы с листом Excel. Ну и, опять же, связь таблиц между собой, ограничения, представления, всякая прочая радость. 4. Если вам надо в проге постоянно использовать что-либо, что требует низкоуровневого управления памятью и возможности сосчитать количество необходимых операций процессора, то путь один - DLL. Например, у вас в проге может использоваться шифрование данных. Соответствующую функцию можно писать на VBA, но работать она будет со скоростью спящей черепахи. Гораздо проще взять в Интернете готовую DLL, либо взять код на C++ (или чем-то другом, позволяющем оптимизировать такие алгоритмы) и откомпилировать. Благо, никаких проблем с подключением DLL к надстройкам Excel нет.

В общем, надо знать свой инструмент, не допускать очевидных ляпов и не пытаться применять тонкую настройку микроскопа к кувалде.

www.matamune.ru

vba - Оптимизация макросов VBA с использованием Solver в Excel, не возвращающая оптимальные переменные

Я не уверен, что вам нужно сделать это в VBA, так как то, что вы ищете, именно то, что должен сделать Solver - изменить набор параметров, чтобы что-то еще было максимизировано/минимизировано!

Поэтому все, что вам нужно сделать, это вставить формулу =ABS(J25-K25) в другую ячейку. Эта ячейка отобразит дельта между вашим экспериментальным значением и теоретическим значением. Теперь настройте свой Solver так, чтобы он минимизировал эту ячейку, изменив три параметра - и все готово! (Обратите внимание, что вы можете предоставить более одной ячейки в поле "Поменяя переменные ячейки"!)

Если вы хотите придерживаться своего подхода, вот синтаксический правильный код. Обратите внимание, что я не тестировал его, но только исправил ошибки, которые я смог обнаружить, просмотрев код. Это, надеюсь, будет хорошей отправной точкой. На самом деле, глядя на этот подход, я уверен, что вы получите неправильный результат, потому что каждый запуск оптимизирует только одну переменную - и вы никогда не будете смотреть на какие-либо эффекты, возникающие в результате комбинации двух или трех параметров !

В любом случае, здесь ваш код:

Sub RunSolver() Dim j As Integer, i As Integer Application.ScreenUpdating = False SolverReset For j = 1 To 100 Application.Statusbar = j & "/100" If Range("$J$25") > Range("$K$25") Then For i = 4 To 6 SolverOk SetCell:=Range("$J$25"), MaxMinVal:=2, ValueOf:=0, ByChange:=Range("$C$" & i), Engine:= _ 1, EngineDesc:="GRG Nonlinear" SolverOptions MaxTime:=0, Iterations:=1000000, Precision:=0.000001, Convergence _ :=0.00001, StepThru:=False, Scaling:=True, AssumeNonNeg:=True, Derivatives:=1 SolverOptions PopulationSize:=100, RandomSeed:=0, MutationRate:=0.075, Multistart _ :=False, RequireBounds:=True, MaxSubproblems:=0, MaxIntegerSols:=0, _ IntTolerance:=1, SolveWithout:=False, MaxTimeNoImp:=30 SolverSolve (True) SolverReset Next i End If Next j Application.StatusBar = False Application.ScreenUpdating = True End Sub

qaru.site