Перейти к содержимому

Использование функции VBA Evaluate вместо цикла

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: необходимо изменить все ячейки диапазона на основе расчета. Вы планируете в коде VBA запустить цикл и перебрать все ячейки:

For Each cell In Selection
    cell.Value = -1 * cell.Value
Next cell

Решение: функция Evaluate позволяет выполнить эту работу быстрее цикла. Замените приведенный выше фрагмент кода одной строкой:

Selection.Value = Evaluate(Selection.Address & "*-1")

Скачать заметку в формате Word или pdf, примеры в формате Excel (с кодом VBA)

Таймер (подробнее см. Использование таймера для сравнения производительности формул) показал, что цикл обработал 100 000 ячеек за 8,3 секунды. Формула Evaluate выполнила ту же работу за 0,09 секунды — на 99% быстрее!

Вы можете решить, что эта классная функция будет вам полезна, чтобы быстро преобразовать любой диапазон данных. К сожалению, большинство функций Excel не могут использоваться внутри функции Evaluate. Например, попытка представить текст в ячейках C2:C99 строчными буквами:

Range("C2:C99").Value = Evaluate("LOWER(C2:C99)") [1]

приведет к неудаче – изменения затронут только ячейку С2. Общее правило таково: если функция Excel обычно не работает с массивом, то и функция Evaluate не будет возвращать массив.

Однако, форумчанин PGC01 (на MrExcel.com) написал прекрасные рекомендации, демонстрирующие, как заставить Evaluate работать с массивом путем введения дополнительного фиктивного массива. Чтобы решить вышеуказанную проблему PGC01 предложил использовать следующее выражение:

Range("C2:C99").Value = Evaluate("IF(ROW(2:99),LOWER(C2:C99))")

Фрагмент ROW(2:99) возвращает числа от 2 до 99. Далее функция IF получает на входе эти числа и возвращает TRUE (IF всегда возвращает TRUE для чисел, отличных от 0). Таким образом, текст внутри функции говорит: «Вот 98 вертикальных значений TRUE. Для каждого из них переведи символы в строчную форму для соответствующих ячеек из диапазона С2:С99».

Дополнительные сведения: вы можете также использовать Evaluate для изменения горизонтального массива:

Range("A1:J1") = Evaluate("IF(ROW1:10),UPPER(A1:J1))")

Использование Evaluate в прямоугольном диапазоне немного сложнее. Вам нужно представить и вертикальный массив – ROW(1:10), и горизонтальный массив – COLUMN(A:J) или TRANSPOSE(ROW(1:10)). Следующий код использует две функции IF, первая из которых представляет собой вертикальный массив, а вторая – горизонтальный:

Range("A1:L23") = Evaluate("IF(ROW(1:23),IF(TRANSPOSE(ROW(1:12)),LOWER(A1:L23)))")

Можно обобщить этот код, чтобы он работал на любом диапазоне. Следующий код переводит все символы в верхний регистр во всех выделенных ячейках:

Sub RectangularUpper()
    ‘ Преобразует все ячейки в выделенном диапазоне в верхний регистр
    Dim rngRectangle As Range, rngRows As Range, rngColumns As Range
    Set rngRectangle = Selection
    ‘ Определяет вертикальный вектор массива
    Set rngRows = rngRectangle.Resize(, 1)
    ‘ Определяет горизонтальный вектор массива
    Set rngColumns = rngRectangle.Resize(1)
    rngRectangle = Evaluate("IF(ROW(" & rngRows.Address & "), _
        IF(COLUMN(" & rngColumns.Address &"),UPPER(" & rngRectangle.Address & ")))")
End Sub

Хотя приведенные здесь примеры имеют дело с изменением регистра текста, вы можете использовать эти принципы, чтобы выполнять расчеты в VBA с помощью Evaluate и большинством функций.

В приложенном Excel-файле вы найдете целый ряд макросов. Поизучайте, вам понравится!

Не обольщайтесь. Хотя этот метод и повышает скорость работы вашего кода, но также делает его более трудным для понимания другими людьми.

Резюме: использование функции VBA Evaluate позволяет существенно ускорить работу кода; за счет хитрого приема функция может использоваться и для массивов.

Источник.

[1] Напоминаю, что функции листов Excel для использования в коде VBA не русифицированы.

3 комментария для “Использование функции VBA Evaluate вместо цикла”

  1. Уведомление: 23 Ноября 2015 - Excel-дайджест - Excel для экономиста

  2. Спасибо. Для меня эти сведения оказались очень полезными.

  3. Спасибо, Сергей Викторович!

    Вы подарили нам map 🙂

    Жаль что в Evaluate не получилось вставить UDF 😉

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *