Този урок ще обсъди как да ускорите VBA макросите и други най -добри практики на VBA.
Настройки за ускоряване на VBA кода
По -долу ще намерите няколко съвета за ускоряване на вашия VBA код. Съветите са свободно организирани по важност.
Най -лесният начин да подобрите скоростта на вашия VBA код е като деактивирате ScreenUpdating и деактивирате автоматичните изчисления. Тези настройки трябва да бъдат деактивирани при всички големи процедури.
Деактивирайте актуализирането на екрана
По подразбиране Excel ще показва промени в работната книга (и) в реално време, докато работи кодът VBA. Това причинява значително забавяне на скоростта на обработка, тъй като Excel повечето интерпретира и показва промени за всеки ред код.
За да изключите актуализирането на екрана:
| 1 | Application.ScreenUpdating = False |
В края на вашия макрос трябва да включите отново актуализацията на екрана:
| 1 | Application.ScreenUpdating = Вярно |
Докато кодът ви работи, може да се наложи да „опресните“ екрана. Няма команда „опресняване“. Вместо това ще трябва да включите отново актуализирането на екрана и да го деактивирате отново.
Задайте изчисленията на Ръчно
Когато се променя стойност на клетка, Excel трябва да следва „дървото на изчисленията“, за да преизчисли всички зависими клетки. Освен това, винаги когато се променя формула, Excel ще трябва да актуализира „дървото за изчисления“ в допълнение към преизчисляването на всички зависими клетки. В зависимост от размера на вашата работна книга, тези преизчисления могат да накарат макросите ви да работят неоправдано бавно.
За да зададете изчисленията на Ръчно:
| 1 | Application.Calculation = xlManual |
За ръчно преизчисляване на цялата работна книга:
| 1 | Изчисли |
Обърнете внимание, че можете също да изчислите само лист, диапазон или отделна клетка, ако е необходимо за подобрена скорост.
За да възстановите автоматичните изчисления (в края на вашата процедура):
| 1 | Приложение.Изчисляване = xlАвтоматично |
Важно! Това е настройка на Excel. Ако не зададете отново изчисленията на автоматична, вашата работна книга няма да преизчисли, докато не я кажете.
Ще видите най -големите подобрения от горните настройки, но има няколко други настройки, които могат да направят разлика:
Деактивиране на събития
Събитията са „тригери“, които предизвикват специални процедури за събития да тичаш. Примерите включват: когато някоя клетка в работен лист се промени, когато се активира работен лист, когато се отвори работна книга, преди запазването на работна книга и т.н.
Деактивирането на събития може да причини незначителни подобрения на скоростта, когато се изпълняват всички макроси, но подобрението на скоростта може да бъде много по -голямо, ако вашата работна книга използва събития. А в някои случаи е необходимо деактивиране на събития, за да се избегне създаването на безкрайни цикли.
За да деактивирате събития:
| 1 | Application.EnableEvents = False |
За да включите събитията отново:
| 1 | Application.EnableEvents = Вярно |
Деактивирайте прекъсванията на страниците
Деактивирането на PageBreaks може да помогне в определени ситуации:
- Преди това сте задали свойство PageSetup за съответния работен лист и вашата процедура VBA променя свойствата на много редове или колони
- ИЛИ Вашата процедура VBA принуждава Excel да изчислява разделяне на страници (показване на визуализация на печат или промяна на всички свойства на PageSetup).
За да деактивирате PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
За да активирате отново прекъсванията на страниците:
| 1 | ActiveSheet.DisplayPageBreaks = Вярно |
Най -добри практики за подобряване на скоростта на VBA
Избягвайте активирането и избора
Когато записвате макрос, ще видите много методи за активиране и избор:
| 12345678 | Sub Slow_Example ()Листове („Лист2“). ИзберетеОбхват ("D9"). ИзберетеActiveCell.FormulaR1C1 = "пример"Обхват ("D12"). ИзберетеActiveCell.FormulaR1C1 = "демонстрация"Обхват ("D13"). ИзберетеEnd Sub |
Активирането и избирането на обекти обикновено не е необходимо, те добавят затруднения към кода ви и отнемат много време. Трябва да избягвате тези методи, когато е възможно.
Подобрен пример:
| 1234 | Sub Fast_Example ()Листове ("Лист2"). Диапазон ("D9"). Формула R1C1 = "пример"Листове ("Лист2"). Обхват ("D12"). Формула R1C1 = "демонстрация"End Sub |
Избягвайте копиране и поставяне
Копирането изисква значителна памет. За съжаление не можете да кажете на VBA да изчисти вътрешната памет. Вместо това Excel ще изчисти вътрешната си памет на (привидно) определени интервали. Така че, ако изпълнявате много операции за копиране и поставяне, рискувате да поставите твърде много памет, което може драстично да забави кода ви или дори да срине Excel.
Вместо да копирате и поставяте, помислете за задаване на стойностите на стойностите на клетките.
| 123456789 | Sub CopyPaste ()'По -бавноДиапазон ("a1: a1000"). Обхват на копиране ("b1: b1000")'По -бързоДиапазон ("b1: b1000"). Стойност = Обхват ("a1: a1000"). СтойностEnd Sub |
Използвайте цикли за всеки вместо цикли за цикли
При преминаване през обекти, цикълът For For е по -бърз от цикъла For For. Пример:
Това за цикъл:
| 123456 | Sub Loop1 ()dim i като RangeЗа i = 1 до 100Клетки (i, 1). Стойност = 1Следва iEnd Sub |
| 123456 | Sub Loop2 ()Затъмняване на клетката като обхватЗа всяка клетка в обхвата ("a1: a100")клетка. Стойност = 1Следващата клеткаEnd Sub |
Декларирайте променливи / Използвайте опцията явно
VBA не изисква да декларирате вашите променливи, освен ако не добавите Option Explicit в горната част на вашия модул:| 1 | Опция Изрично |
| 1234 | Sub OptionExplicit ()var1 = 10MsgBox varlEnd Sub |
Използвай с - Край с изявления
Ако препращате към едни и същи обекти многократно (напр. Диапазони, Работни листове, Работни книги), помислете за използването на инструкцията With. Той се обработва по -бързо, може да направи кода ви по -лесен за четене и опростява кода.С пример за изявление:| 12345678 | Под -по -бърз_пример ()С листове ("Sheet2").Range ("D9"). Формула R1C1 = "пример".Range ("D12"). Формула R1C1 = "демонстрация".Range ("D9"). Font.Bold = Вярно.Range ("D12"). Font.Bold = ВярноКрай сEnd Sub |
| 123456 | Sub Slow_Example ()Листове ("Лист2"). Диапазон ("D9"). Формула R1C1 = "пример"Листове ("Лист2"). Обхват ("D12"). Формула R1C1 = "демонстрация"Листове ("Sheet2"). Диапазон ("D9"). Font.Bold = ВярноЛистове ("Sheet2"). Диапазон ("D12"). Font.Bold = ВярноEnd Sub |
Разширени съвети за най -добри практики
Защитете само UserInterface
Добра практика е да защитите работните си листове от редактиране на незащитени клетки, за да предотвратите случайно повреждане на работната книга от крайния потребител (или вие!). Това обаче също така ще защити работния лист (и) от разрешаване на VBA да прави промени. Така че трябва да премахнете защитата и да защитите отново работните листове, което отнема много време, когато се прави на много листове.
| 12345 | Sub UnProtectSheet ()Листове („лист 1“). Премахнете защитата „парола“„Редактиране на лист 1Листове („лист 1“). Защита на „парола“End Sub |
Вместо това можете да защитите листове с настройка UserInterfaceOnly: = True. Това позволява на VBA да прави промени в листове, като същевременно ги защитава от потребителя.
| 1 | Листове („лист 1“). Защита на паролата: = "парола", UserInterFaceOnly: = Вярно |
Важно! UserInterFaceOnly се връща към False всеки път, когато работната книга се отвори. Така че, за да използвате тази страхотна функция, ще трябва да използвате събитията Workbook_Open или Auto_Open, за да зададете настройката при всяко отваряне на работната книга.
Поставете този код в модула на тази работна книга:
| 123456 | Private Sub Workbook_Open ()Затъмнете като работен листЗа всеки ws в работни листовеws.Protect Password: = "парола", UserInterFaceOnly: = ВярноСледващата wsEnd Sub |
или този код във всеки обикновен модул:
| 123456 | Private Sub Auto_Open ()Затъмнете като работен листЗа всеки ws в работни листовеws.Protect Password: = "парола", UserInterFaceOnly: = ВярноСледващата wsEnd Sub |
Използвайте масиви за редактиране на големи диапазони
Манипулирането на голям диапазон от клетки може да отнеме много време (напр. 100 000+). Вместо да преглеждате диапазони от клетки, да манипулирате всяка клетка, можете да заредите клетките в масив, да обработите всеки елемент в масива и след това да изведете масива обратно в оригиналните клетки. Зареждането на клетките в масиви за манипулиране може да бъде много по -бързо.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange ()Затъмняване на клетката като обхватDim tStart As DoubletStart = ТаймерЗа всяка клетка в обхвата ("A1: A100000")cell.Value = cell.Value * 100Следващата клеткаDebug.Print (Таймер - tStart) & "секунди"End SubSub LoopArray ()Dim arr като вариантЗатъмнете елемента като вариантDim tStart As DoubletStart = Таймерarr = Обхват ("A1: A100000"). СтойностЗа всеки артикул В обрitem = item * 100Следващият елементДиапазон ("A1: A100000"). Стойност = обрDebug.Print (Таймер - tStart) & "секунди"End Sub |
