#excel #vba
#превосходить #vba
Вопрос:
Я пишу код для книги Excel, предоставленной клиентом.
Цель этого макроса-загрузить данные для клиента в лист. Когда это происходит, запускается прослушиватель в одной из загруженных ячеек, и он вызывает соответствующую подпрограмму, связанную с информацией об этом клиенте.
Код работает точно так, как ожидалось, и завершается через несколько секунд. Однако клиент желает, чтобы мы последовательно выполнили эту задачу примерно на 15 000 клиентах.
Когда мы пытаемся повторить макрос примерно для сотни клиентов, макрос завершается в течение нескольких минут. Однако, когда мы увеличиваем число клиентов примерно до тысячи, время для завершения запуска резко увеличивается.
Я уже несколько дней пытаюсь оптимизировать саму подпрограмму, но замедление возникает только тогда, когда мы вызываем подпрограмму более тысячи раз.
Почему повторные вызовы подпрограммы могут привести к замедлению выполнения кода и есть ли способ, которым это можно смягчить?
Sub Looprun() Dim n As Long Dim lastRow As Long Dim SecondLastrow As Long Dim Start As Long Dim Finish As Long Application.Calculation = xlManual Application.ScreenUpdating = False 'Set starting timestamp Range("RunStart").Value = Now() Start = Sheets("Reserve Summary").Range("L12").Value Finish = Sheets("Reserve Summary").Range("L13").Value lastRow = Cells(Sheets("Reserve Summary").Rows.Count, 2).End(xlUp).Row - 1 For n = Start To Finish Sheets("Calculation").Cells(3, 3).Value = n ' Cell C3 has a listener attached to it; see below. The customer's other information is loaded in via formulas that are keyed to cell C3. Sheets("Reserve Summary").Cells(n 2, 3).Value = Sheets("Calculation").Range("C4").Value Sheets("Reserve Summary").Cells(n 2, 5).Value = Sheets("Calculation").Range("Q25").Value Sheets("Reserve Summary").Cells(n 2, 7).Value = Sheets("Calculation").Range("Q22").Value Next Range("RunEnd").Value = Now() Application.Calculation = xlAutomatic Application.Calculate Application.ScreenUpdating = True End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim company As String If Target.Address = "$C$3" Then Worksheets("Calculation").Calculate company = Sheets("Calculation").Cells(3, 11).Value If LCase(company) = "A" Then Call SubroutineA Exit Sub End If If LCase(company) = "B" Then Call SubroutineB Exit Sub End If Call DefaultSubroutine End If End Sub Комментарии:
1. используйте массивы вариантов и ограничьте количество ссылок на диапазоны рабочих листов. Каждый раз, когда vba ссылается на диапазон рабочих листов, для этого ему приходится замедляться. массовая загрузка и массовое присвоение значений там, где количество ссылок ограничено, позволяет vba работать с собственной скоростью.
2. Вы захотите выполнить вычисления в скрипте, а не на рабочем листе, опять же это медленно.
3. Довольно трудно сказать, почему код вызывает
SubroutineAиSubroutineBработает медленно, не видя этих подпрограмм.4. @JohnColeman Я не решаюсь помещать здесь фактические подпрограммы, так как это работа с клиентами. Внешний цикл и прослушиватель достаточно универсальны, чтобы не быть проблемой, но внутри процедуры это совсем другая история. Все три подпрограммы были протестированы индивидуально и завершены в среднем примерно за 1-3 секунды. Только когда я вызываю внешний цикл и запускаю его через несколько тысяч экземпляров, vba начинает заметно замедляться.
5. @ScottCraner К сожалению, это прерогатива клиента. Я смягчил это, отключив автоматический расчет и выполнив ручной вызов листов(«Расчет»). Рассчитайте в конце подпрограммы. Учитывая объем самого рабочего листа и временные ограничения проекта, перенос расчетов на листе в модуль нецелесообразен. Что касается использования вариантных массивов, я недавно узнал об этом и нахожусь в процессе интеграции этого в подпрограммы. Однако проблема все еще сохраняется, и только тогда, когда я делаю большое количество последовательных вызовов подпрограмм.