Если вы хотите, чтобы ваш код макроса VBA работал быстро, то необходимо отключить некоторые функции VBA Excel в начале кода. После выполнения макроса снова включаем все функции.
Для этого мы можно использовать следующий код:
' Процедура : TurnOffFunctionality ' Источник : www.planacademy.ru ' Назначение: Отключает автоматические вычисления, обработку событий и обновление экрана Public Sub TurnOffFunctionality() ' Все расчеты переводим в ручной режим Application.Calculation = xlCalculationManual ' Отключаем статусную строку Application.DisplayStatusBar = False ' Отключаем события Application.EnableEvents = False ' Отключаем показ разбиения листа на печатные страницы If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If ' Больше не обновляем страницы после каждого действия Application.ScreenUpdating = False ' Отключаем сообщения Excel Application.DisplayAlerts = False End Sub ' Процедура : TurnOnFunctionality ' Источник : www.planacademy.ru ' Purpose : Включает автоматические вычисления, обработку событий и обновление экрана Public Sub TurnOnFunctionality() Application.Calculation = xlCalculationAutomatic Application.DisplayStatusBar = True Application.EnableEvents = True If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = True End If Application.ScreenUpdating = True Application.DisplayAlerts = True End Sub
Использовать эти процедуры можно следующим образом:
Sub Main() ' Выключить вначале TurnOffFunctionality ' Ваш основной код здесь ' Включить в конце TurnOnFunctionality End Sub
Иногда, когда выполняется макрос, процедура не доходит до строки включения функций “TurnOnFunctionality”. Это может произойти из-за ошибки или из принудительной остановки кода в определенный момент. Если это произойдет, вы можете снова включить все функции, выбрав процедуру TurnOnFunctionality в окне радактирования кода и нажав клавишу F5.
Никогда не используем функцию Select
При копировании данных в Excel VBA не используйте метод Select-никогда! Большая ошибка, которую делают новички VBA, – это думают, что им нужно выбрать ячейку или диапазон, прежде чем ее скопируют.
Например:
shRead.Activate shRead.Range("A1").Select shWrite.Activate Selection.Copy Activate.Range("H1")
Помните об этих двух важных вещах, прежде чем используется VBA для копирования данных:
- Вам не нужно выбирать ячейку или диапазон ячеек.
- Вам не нужно выбирать или активировать рабочий лист.
Часто можно увидеть, что Select используется во многих примерах в интернете. Но вам не нужно использовать его для копирования ячеек – никогда!
Общие рекомендации
Оптимизация
1) Желательно избегать формул массивов (по возможности)
2) Избегать волатильных формул (по возможности) (условное форматирование, как пример волатильности)
3) Использовать структурированные таблицы и именованные диапазоны
4) Заменять неиспользуемые формулы значениями
5) Хранить все данные для расчета на одном листе (по возможности)
6) Избегать в формулах ссылки на целые столбцы
7) использовать =IFERROR вместо IF + ISERR/ISERROR
8) использовать MAX(A1;0) вместо IF(A1>0;A1;0)
9) использовать INDEX+MATCH вместо VLOOKUP
10) использовать — для конвертации лог.значение в 0/1
11) использовать A1*0.1 чем A1/10
VBA
12) Использовать оператор if else, вместо IIF
13) Использовать Long т.е. Long лучше Вуte, Integer, конечно, variant
14) Применять if else, чем switsh; shoose
15) Лучше проверять строку так len(s)=0, чем s = “”
16) Лучше инициализировать строку так s =vbnullstring, чем s =””
17) Если возможно писать так s = “АВ”, чем s =”А” & “В”
18) Вставлять строку в текст так Мid$(а,3,4)=”like”. чем s = left$(8,2) & “like” & mid(з,7)
19) Сравнивать строки так if strcomp(s1,s2,vbtextcompare) =0, чем if ucase(s1)=ucase(s2)
20) Лучше использовать $-функцию, т.е. left$ лучше left.
21) Цикл for …next. работает быстрее do…lоор
22) Точнее объявлять объекты: as commandbutton лучше as control, еще лучше as object
23) Цикл for Еасh..next лучше, чем цикл for…next семейств объектов
24) Цикл for …next лучше, чем цикл fог Еасh…next: для массивов.
25) Использовать оператор With при обращении к элементам сходной иерархической модели
Для общих (расшаренных) книг
26) Удалять пользовательские представления Вид-Представления-Удалить
27) Снять пометки с Параметров печати и Фильтров , так Рецензирование-Доступ к книге-Параметры печати и Фильтры
28) Периодически отключать общий доступ, сохранять книгу, отключать общий доступ и снова сохранять
29) Удостовериться, что книга открывается в режиме просмотра Вид-Обычный (для того, чтобы избежать обращения к сетевому принтеру с соответствующими временными последствиями)
30) Перелить одну книгу в другую путем копирования не листа, а содержимого в новую книгу со вставкой сначала значения, потом формулы, потом форматы и макросы через импорт экспорт перенести в новую книгу
31 ) Удалить избыточное форматирование