Блог → Оптимизация макросов VBA. Часть 1

Теме оптимизации приложений посвящаются статьи, сайты и даже более того - целые книги. Практически повсюду нетрудно найти советы, как ускорить программу, написанную на языке C, Delphi или Visual Basic, но как ни странно, подобных рекомендаций для среды разработки VBA (Visual Basic for Applications) практически нет. В этой заметке я постараюсь восполнить этот пробел и дать некоторые практические советы по ускорению и уменьшению ваших программ, написанных на Visual Basic for Applications в среде Microsoft Office. При этом ряд рекомендаций повторяет известные методы оптимизации VB-приложений, часть применима только к VBA, а некоторые применимы к обеим средам и являются собственными находками автора.

Итак, приступим! И начнём, как водится, с общих принципов. Прежде всего, пускай это и прозвучит кощунственно для программерской братии - не оптимизируйте код для быстродействия, когда это не нужно. Такая оптимизация обычно является пустой тратой времени! Постарайтесь, прежде всего, найти места, которые тормозят выполнение всей программы. Как правило, это несколько (10-20%) неверно выбранных инструкций, и если их найти и исправить, производительность повысится на 80-90% (так называемое "правило 20/80", которое применимо не только к программированию, но и ко многим другим отраслям).

Не следует также думать, будто перенеся код на C++ или на любой другой язык, вы значительно ускорите программу. Если ваш алгоритм неудачен или же его реализация небезупречна, он не станет лучше, если переписать его на другом языке. Скажу больше - грамотно построенная программа на VBA может работать значительно быстрее, чем творчество плохого C-программиста. Но остаётся один важный вопрос: где искать эти "узкие места". Ниже вы найдёте несколько общих советов.

Начните проверку с многократно повторяющихся циклов. По полученным мной экспериментальным данным IIf в три раза менее эффективен, чем обычный If: 44 секунды против 9 секунд при десяти миллионах прогонов на процессоре Pentium 166 (да-да, это было давно, но суть от этого не меняется). Что это означает для нас? Лишь тот факт, что на Р166 IIf выполняется 4,4 миллионных секунды, a If - одну миллионную.

Возникает вопрос - а насколько важна эта разница? Не особенно, если IIf написан пару раз вне цикла. И очень важна, если неэффективная операция повторяется 10 миллионов раз в цикле: разница во времени выполнения в 35 секунд весьма существенна! Поэтому подбор операторов вне цикла менее важен, чем многократно повторяющиеся, "критичные ко времени выполнения", как их часто называют, циклы. Очевидно, что именно здесь следует поработать над оптимизацией - это сразу же даст ощутимый прирост в скорости выполнения.

Порядок использования интерфейсов. Используйте интерфейсы, предоставляемые VBA, в таком порядке: встроенные функции, объектные библиотеки приложений, Win32 API, сложные алгоритмы, реализованные на VBA (например, алгоритм сжатия или парсер строковых выражений), и наконец - компоненты третьих фирм. Наиболее быстро работают встроенные функции, несколько медленнее - функции и методы объектных библиотек. Ну и, собственные или взятые со специализированных сайтов и из книг реализации алгоритмов на VBA обычно работают наиболее медленно. Однако же, и они предпочтительнее "быстрой и грязной" техники с использованием ActiveX, когда с помощью большого количества громоздких чужих компонентов программист пытается "соорудить" работающую программу за ночь.

Почему так, спросите вы? ActiveX нужно регистрировать в реестре. Нужно создавать инсталлятор и деинсталлятор, использовать RegSrv32, тогда как обычно макросы для Word и Excel распространяют в архиве. Некорректная деинсталляция ActiveX или её отсутствие вызывает ошибки в реестре и, соответственно, широкое использование ActiveX в перспективе ведет к замусориванию реестра. Иногда ActiveX требуют дополнительных runtime'ов VB, C++ и др. (зависит от того, на чём они были написаны). Если ActiveX не требуют runtime-библиотек, они почти всегда занимают весьма много места, так как эти библиотеки (VCL, MFC) уже содержатся внутри .ocx-файла. При этом многие ActiveX, размеров в две сотни килобайт, можно запросто заменить простым кодом, занимающим меньше десяти килобайт, и который, к тому же, можно как угодно изменить, исправить и переделать под свои нужды.

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

Пример 1. Сортировку в Word лучше выполнять не с помощью различных алгоритмов, предлагаемых сайтами и книгами по VB (и уж точно не стоит использовать метод пузырька), а вызовом метода Word-Basic.SortArray. Для массивов в Excel обычно следует пользоваться своей процедурой, реализующей сортировку блоками, быструю или пирамидальную сортировку. Сортируя ячейки Excel или абзацы Word, применяйте метод Selection.Sort, специально предназначенный для этого.

Пример 2. Необходимо подсчитать значение арифметического выражения, содержащегося в строке. Например, "12+12*(5-2)" должно в конечном счёте превратиться в 48. Тут можно посоветовать использовать (в порядке убывания предпочтений):
• метод Application.Evaluate в Excel, Eval в Access или малоизвестную встроенную команду Word Вычислить значение (Selection.Calculate, WordBasic.ToolsCalculate);
• парсер строковых выражений, написанный на VBA - медленный, но верный способ; готовый код можно найти на некоторых сайтах, посвященных VB и в коллекции исходных текстов VB Source Book;
• метод Eval() из Windows Scripting Host (не рекомендуется, так как недоступен начиная с Windows 95, однако если производительность является критичным фактором, используйте его вместо парсера на VBA).

Неплохо решение - сделать это через Selection.Calculate (создаём документ и печатаем в него выражение, которое затем можно будет подсчитать):

Documents.Add
With Selection

.TypeText "12 + 12 * (5 - 2)"
.HomeKey wdStory, wdExtend
MsgBox .Calculate
.Delete

End With
ActiveDocument.Close False


Команда WordBasic ToolsCalculate позволяет сделать то же самое ещё проще и быстрее, так как её параметром можно указать строковое выражение:

Debug.Print
WordBasic.ToolsCalculate("12 + 12 * (5 - 2)")


Пример 3. Допустим, что нам требуется проигрывать из VBA MP3-файлы. Как это сделать? Можно либо установить в систему кодек MP3 (а его нет по умолчанию даже в Windows ХР) и вызывать MCI через Win32 API, либо использовать ActiveX-компоненты третьих фирм, реализующие кодеки (вот это случай, когда они действительно нужны). Ещё один вариант - использовать кодек вроде Lame с открытыми исходными текстами, оформив его в виде внешней DLL.

Оценка производительности. Используйте тестовые процедуры с замером времени всякий раз, когда вы сомневаетесь в целесообразности использования той или иной программной конструкции. Так как тактовые генераторы работают несколько нестабильно, а в многозадачной операционной системе на скорость работы приложения влияет количество и активность других задач, проверяйте разные варианты при одних и тех же загруженных фоновых процессах, не переключайтесь между задачами и даже не двигайте курсор мыши во время измерения времени. Конечно же, следует отключить видеоплееры, MP3-проигрыватели, торренты и прочие ресурсоёмкие приложения, выполняющиеся в фоне. Запускайте тест несколько раз и усредняйте полученные результаты. Подберите число итераций (повторений) цикла таким, чтобы время выполнения на вашем компьютере составляло не менее 5-10 секунд, иначе результаты окажутся недостоверными (ваша программа может даже выдать отрицательное значение времени, если она будет выполняться настолько быстро, что это значение выйдет за рамки разрядности используемого типа данных). Вот типичный код:

Const С = 10000
x! = Timer
For i& = 1 to С
' Выполняем действие 1
Next i
x = Timer - x

y! = Timer
For i& = 1 to С
' Выполняем действие 2
Next i
y = Timer - y

MsgBox "x=" & x & vbCrLf &
"y=" & y & vbCrLf & _
"x/y=" & x/y & vbCrLf &
"y/x=" & y/x


Следите за освобождением ресурсов. Если вы оперируете значительными объемами данных, запустите утилиту, предоставляющую подробную информацию о свободном объёме памяти ( например FreeMemory или MemGlance), чтобы определить, не "забывает" ли ваша программа освобождать ресурсы после их использования. Типичные симптомы: не удаётся найти ошибок в алгоритме, а программа, тем не менее, работает медленно, причем при повторных запусках время работы увеличивается (это, пожалуй, самый настораживающий симптом, который почти стопроцентно свидетельствует о захвате ресурсов без последующего освобождения). Сравните, сколько памяти, пространства кучи, ресурсов GDI и ресурсов модуля user приложение занимает до и после выполнения медленной операции.