Метод Application.Evaluate используется в VBA Excel для получения значений или ссылок на объекты из строковых выражений. Краткая запись Evaluate.
Что такое метод Evaluate?
Проще говоря, метод Evaluate позволяет выполнить любую формулу, которую вы могли бы вписать в строку формул Excel, прямо из кода VBA. Функции рабочего листа, используемые в качестве аргумента Evaluate, должны быть в англоязычном варианте.
Метод Evaluate необходим, когда нужно вычислить формулу, сослаться на диапазон или выполнить математическое действие, которое проще описать текстовой строкой.
Синтаксис метода Evaluate
Стандартный синтаксис
|
1 |
Application.Evaluate("Выражение") |
Выражение — формула или имя объекта Microsoft Excel с длиной не более 255 символов.
Объект Application в коде VBA Excel можно не указывать.
Упрощённый синтаксис
У метода Evaluate есть более короткий и удобный синоним — квадратные скобки:
|
1 |
[Выражение] |
Оба варианта работают идентично, но использование квадратных скобок делает код более чистым и читаемым. Однако, синтаксис с квадратными скобками не допускает использование в аргументе переменных и математических выражений, переданных текстовой строкой.
Практические примеры с Evaluate
Давайте рассмотрим основные сценарии использования метода Application.Evaluate.
Вычисление математических выражений
Представьте, что вам нужно быстро вычислить результат сложного выражения, сформированного в виде текстовой строки, прямо в коде:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub Primer1() Dim a As String a = "45.7 * 12.3 + 15/3 * 6.8" 'Стандартный синтаксис MsgBox Evaluate("45.7 * 12.3 + 15/3 * 6.8") 'Результат: 596.11 MsgBox Evaluate(a) 'Результат: 596.11 'Синтаксис с квадратными скобками MsgBox ["45.7 * 12.3 + 15/3 * 6.8"] 'Результат: "45.7 * 12.3 + 15/3 * 6.8" MsgBox [a] 'Результат: "45.7 * 12.3 + 15/3 * 6.8" End Sub |
Как видим из примера, подтверждается информация о том, что синтаксис с квадратными скобками не работает с математическими выражениями, переданными в виде текстовой строки.
Использование встроенных функций Excel
Обычно встроенные функции Excel проще использовать с помощью метода WorksheetFunction:
|
1 2 3 4 5 6 7 |
Sub Primer2() Range("A1:A3") = 1: Range("B1:B3") = 2: Range("C1:C3") = 3 'Используем метод WorksheetFunction MsgBox WorksheetFunction.ArrayToText(Range("A1:C3"), 0) 'Результат: 1,2,3,1,2,3,1,2,3 'Используем метод Evaluate MsgBox Evaluate("=ArrayToText(" & "A1:C3" & ", 0)") 'Результат: 1,2,3,1,2,3,1,2,3 End Sub |
В функции WorksheetFunction.ArrayToText можно заменить Range("A1:C3") на [A1:C3]. Метод Evaluate рекомендуется использовать для функций листа Excel, не имеющих аналогов в VBA, вызывающихся методом WorksheetFunction.
Работа с диапазонами и ссылками
Одно из самых частых применений Evaluate в VBA Excel — создание диапазонов с помощью строковых выражений, манипуляция ими, в том числе с помощью формул.
Полная ссылка на ячейку
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub Primer3() 'Ссылка на ячейку в текущей книге Debug.Print Sheets("Лист2").Range("A14") Debug.Print Evaluate("Лист2!A14") Debug.Print [Лист2!A14] 'Ссылка на ячейку в другой книге Debug.Print Workbooks("Книга2.xlsm").Sheets("Лист2").Range("A14") Debug.Print Evaluate("'[Книга2.xlsm]Лист2'!A14") Debug.Print ['[Книга2.xlsm]Лист2'!A14] End Sub |
Присвоение диапазона переменной
|
1 2 3 4 5 6 7 8 9 |
Sub Primer4() Dim r As Range Set r = Range("A1:D4") Debug.Print r(10).Address 'Результат: $B$3 Set r = Evaluate("A1:D4") Debug.Print r(10).Address 'Результат: $B$3 Set r = [A1:D4] Debug.Print r(10).Address 'Результат: $B$3 End Sub |
Суммирование значений диапазона
|
1 2 3 4 5 6 |
Sub Primer5() [C1] = 5: [C2] = 2: [C3] = 7 Debug.Print WorksheetFunction.Sum(Range("C1:C3")) 'Результат: 14 Debug.Print Evaluate("Sum(C1:C3)") 'Результат: 14 Debug.Print [Sum(C1:C3)] 'Результат: 14 End Sub |
Динамическое построение формулы
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Sub Primer6() Dim a1 As String, a2 As String, summa As Double [F2] = 1.25: [F3] = 2.06: [F4] = 7.65: [F5] = 4.57: [F6] = 2.85 a1 = "F2" a2 = "F6" 'Динамическое построение и вычисление формулы summa = Evaluate("SUM(" & a1 & ":" & a2 & ")") MsgBox "Сумма диапазона " & a1 & ":" & a2 & " равна " & summa End Sub |
Использование с Evaluate переменных
В предыдущем примере уже использовались переменные для динамического построения формулы. Переменные можно применять только со стандартным синтаксисом метода Application.Evaluate:
|
1 2 3 4 5 6 7 8 9 |
Sub Primer7() Dim a As String, b As String, c As String [C1] = 5: [C2] = 4: [C3] = 7 a = "C1": b = "C2": c = "C3" Debug.Print Evaluate("PRODUCT(" & a & "," & b & "," & c & ")") 'Результат: 140 Debug.Print ["PRODUCT(" & a & "," & b & "," & c & ")"] 'Результат: Error 2015 End Sub |
Преимущества и недостатки метода Evaluate
Плюсы
- Лаконичность кода: синтаксис с квадратными скобками позволяет писать очень компактный код.
- Прямой доступ к функциям Excel:
Evaluateпозволяет использовать любую функцию из арсенала Excel без метода WorksheetFunction.
Минусы
- Проблемы с производительностью в циклах: если использовать
Evaluateдля обработки большого количества ячеек в цикле, это окажется значительно медленнее, чем прямое присвоение значений черезRange.Value. - Трудности с использованием переменных: для подстановки переменных в выражение нужно вручную формировать строку, применяя конкатенацию, что усложняет код.
Применение
Когда стоит использовать Evaluate?
- Для разовых вычислений сложных математических выражений или функций Excel.
- Для быстрого обращения к ячейкам и именованным диапазонам в простом, линейном коде.
- Когда лаконичность кода важнее, чем абсолютная производительность.
Когда от него лучше отказаться?
- При обработке больших массивов данных в циклах.
- В сложных проектах, где важна легкость отладки и поддержки кода.
- Когда выражение для вычисления динамически формируется с большим количеством переменных.
Метод Evaluate — это не главный инструмент в арсенале VBA-разработчика, а скорее специальный ключ, который открывает определенные замки.