VBA за Excel 2016 е бърз, но не винаги е достатъчно бърз. (Компютърните програми никога не са достатъчно бързи.) Продължете да четете, за да откриете някои примери за програмиране, които можете да използвате, за да ускорите своите макроси.
Изключване на актуализирането на екрана
Когато изпълнявате макрос, можете да седнете и да гледате всички действия на екрана, които се случват в макроса. Въпреки че това може да бъде поучително, след като накарате макроса да работи правилно, това често е досадно и може да забави значително производителността на вашия макрос. За щастие можете да деактивирате актуализирането на екрана, което обикновено се случва, когато изпълнявате макрос. За да изключите актуализирането на екрана, използвайте следното изявление:
Application.ScreenUpdating = False
Ако искате потребителят да види какво се случва във всеки момент по време на макроса, използвайте следното изявление, за да включите отново актуализирането на екрана:
Application.ScreenUpdating = Вярно
За да демонстрирате разликата в скоростта, изпълнете този прост макрос, който запълва диапазон с числа:
Sub FillRange()
Dim r колкото дълго, c толкова дълго
Затъмнено число толкова дълго
Число = 0
За r = 1 до 50
За c = 1 до 50
Число = Число + 1
Клетки (r, c). Изберете
Клетки (r, c). Стойност = Число
Следващо c
Следваща r
Край под
Виждате, че всяка клетка е избрана и стойността се въвежда в клетките. Сега поставете следния оператор в началото на процедурата и го изпълнете отново:
Application.ScreenUpdating = False
Диапазонът се запълва много по-бързо и не виждате резултата, докато макросът не приключи с изпълнението и актуализирането на екрана не е (автоматично) зададено на True.
Когато отстранявате грешки в кода, изпълнението на програмата понякога завършва някъде по средата, без да сте включили отново обновяването на екрана. Това понякога кара прозорецът на приложението на Excel да не реагира напълно. Изходът от това замразено състояние е прост: Върнете се към VBE и изпълнете следния оператор в прозореца Immediate:
Application.ScreenUpdating = Вярно
Изключване на автоматичното изчисление
Ако имате работен лист с много сложни формули, може да откриете, че можете да ускорите значително нещата, като зададете режима на изчисление на ръчен, докато вашият макрос се изпълнява. Когато макросът приключи, задайте режима на изчисление обратно на автоматичен.
Следното изявление задава режима на изчисление в Excel на ръчен:
Application.Calculation = xlCalculationManual
Изпълнете следващия оператор, за да настроите режима на изчисление на автоматичен:
Application.Calculation = xlCalculationAutomatic
Ако вашият код използва клетки с резултати от формула, изключването на изчислението означава, че клетките няма да бъдат преизчислени, освен ако изрично не кажете на Excel да го направи!
Премахване на тези досадни предупредителни съобщения
Както знаете, макросът може автоматично да изпълнява поредица от действия. В много случаи можете да стартирате макрос и след това да излезете в стаята за почивка, докато Excel върши своето. Някои операции на Excel обаче показват съобщения, които изискват човешки отговор. Тези типове съобщения означават, че не можете да оставяте Excel без надзор, докато изпълнява вашия макрос - освен ако не знаете тайния трик.
Можете да инструктирате Excel да не показва тези типове сигнали, докато изпълнявате макрос.
Тайният трик за избягване на тези предупредителни съобщения е вмъкването на следния VBA оператор във вашия макрос:
Application.DisplayAlerts = False
Excel изпълнява операцията по подразбиране за тези типове съобщения. В случай на изтриване на лист, операцията по подразбиране е Изтриване. Ако не сте сигурни каква е операцията по подразбиране, извършете тест, за да видите какво се случва.
Когато процедурата приключи, Excel автоматично нулира свойството DisplayAlerts на True. Ако трябва да включите отново сигналите, преди процедурата да приключи, използвайте това изявление:
Application.DisplayAlerts = Вярно
Опростяване на препратките към обекти
Както вероятно вече знаете, препратките към обекти могат да станат много дълги. Например, напълно квалифицирана препратка към обект Range може да изглежда така:
Работни книги(“MyBook.xlsx”).Работни листове(“Sheet1”) _
.Range(„Лихва процент“)
Ако вашият макрос често използва този диапазон, може да искате да създадете обектна променлива с помощта на командата Set. Например, следният израз присвоява този обект Range на обектна променлива с име Rate:
Задаване на процент = Работни книги(“MyBook.xlsx”) _
.Worksheets(“Sheet1”).Обхват(“InterestRate”)
След като дефинирате тази променлива на обекта, можете да използвате променливата Rate вместо дългата препратка. Например, можете да промените стойността на клетката с име InterestRate:
Стойност = .085
Това е много по-лесно за въвеждане, отколкото следното изявление:
Работни книги(“MyBook.xlsx”).Работни листове(“Sheet1”). _
Диапазон („лихвен процент“) = .085
В допълнение към опростяването на вашето кодиране, използването на обектни променливи ускорява значително вашите макроси.
Деклариране на типове променливи
Обикновено не е нужно да се притеснявате за типа данни, които присвоявате на променлива. Excel обработва всички подробности вместо вас зад кулисите. Например, ако имате променлива с име MyVar, можете да присвоите номер от произволен тип на тази променлива. Можете дори да му присвоите текстов низ по-късно в процедурата.
Ако искате вашите процедури да се изпълняват възможно най-бързо, кажете на Excel какъв тип данни ще бъдат присвоени на всяка от вашите променливи. Това е известно като деклариране на типа на променлива.
Като цяло трябва да използвате типа данни, който изисква най-малък брой байтове, но все пак може да обработва всички данни, приписани към него. Когато VBA работи с данни, скоростта на изпълнение зависи от броя на байтовете, с които VBA разполага. С други думи, колкото по-малко байтове използва данните, толкова по-бързо VBA може да осъществява достъп и да манипулира данните. Изключение от това е типът данни Integer. Ако скоростта е критична, използвайте вместо това тип данни Long.
Ако използвате обектна променлива, можете да декларирате променливата като конкретен тип обект. Ето един пример:
Степента на затъмняване като диапазон
Задаване на процент = Работни книги(“MyBook.xlsx”) _
.Worksheets(“Sheet1”).Обхват(“InterestRate”)
Използване на структурата With-End With
Трябва ли да зададете редица свойства за обект? Вашият код работи по-бързо, ако използвате структурата With-End With. Допълнително предимство е, че кодът ви може да бъде по-лесен за четене.
Следният код не използва With-End With:
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = Вярно
Избор.Ориентация = 0
Selection.ShrinkToFit = False
Selection.MergeCells = False
Ето същия код, пренаписан да използва With-End With:
С селекция
.Хоризонтално подравняване = xlCenter
.Вертикално подравняване = xlCenter
.WrapText = Вярно
.Ориентация = 0
.ShrinkToFit = Невярно
.MergeCells = False
Край с
Когато използвате With-End With, уверете се, че всяко изявление започва с точка.