Evaluate vba excel

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

Задача: необходимо изменить все ячейки диапазона на основе расчета. Вы планируете в коде 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 не русифицированы.

Ошибка Evaluate при вычислении массива

Приветствую Вас, знатоки Excel & VBA!
Я уже 3 дня путешествую по просторам инета, но, к сожалению, не могу найти ответ на свой вопрос. У меня есть функция, например =СТРОКА(ДВССЫЛ(«$A$1:$A$»&СУММ(СЧЁТ(ПОРТИНТ);1)))
ПОРТИНТ в свою очередь содержит набор других функций Excel и т.д., но это не важно. Вся проблема заключается в том, что если в VBE написать что-то вроде этого:
ArraySplitting = Evaluate(«ROW(INDIRECT(«»Result!$A$1:$A$»»&SUM(COUNT(ПОРТИНТ),1)))»)
то возникает ошибка #ССЫЛКА!
Забавно, но, если убрать внешний уровень — функцию «ROW», то в переменную ArraySplitting записывается массив, состоящий из значений диапазона Result!$A$1:$A$n
Но получить-то мне нужно не значения диапазона <7,1,18. n>, а массив с чёткой последовательностью <1, 2, 3, 4. n>, да и вообще, сделать так, что бы можно было работать с этими и подобными этим именованными функциями (т.е. именами, которые могут содержать функции, возвращающие массив значений, как в примере выше <1, 2, 3, 4. n>).

Есть только одно похожее на мою проблему упоминание, но применить это я не смог:
http://support.microsoft.com/KB/131384

Добавлено через 43 минуты
Я ещё немного поэксперементировал и понял, что ROW и INDIRECT прекрасно работают по отдельности, но вместе они вызывают ошибку. Согласно рекомендации по указанной мною ссылке нужно заменить как-то INDIRECT на Range, только вот не могу понять как это сделать.

Я понимаю разницу между Evaluate и [], но и первый вариант, и следующий ругаются на то, что я имя не существующее применяю. Видимо речь идёт о «Ar».

P.S.: Это всё не рабочий вариант, столько строк нужно для моего понимания происходящих в коде процессов (В VBA я новичок)

Ошибка цикла при вычислении максимального элемента массива
Помогите исправить. По идее программа должна вычислить макс элементы, вывести их,и если они.

Ошибка при дебаге: cannot evaluate expression because debugging information has been optimized away
Просьба подсказать, почему возникает такая ошибка: Происходит так: идёт дебаггинг, когда код.

Ошибка при вычислении
clear all close all clc Ea = 0.15; Eb = 0.15; Ee = 0.1; N = 70;

Ошибка при вычислении
Добрый день, подскажите есть ли функция в аксеес еслиошибка как у экселя?

Согласен, но это только если выбрана одна ячейка (или строка). Но если выбран диапазон, то эта формула выдаст номера строк для каждого элемента диапазона, т.е. результатом работы формулы =СТРОКА(ДВССЫЛ(«$A$1:$A$»&СУММ(СЧЁТ(ПОРТИНТ);1))) будет одномерный массив <1;2;3;4. n>где n — это значение, расчитанное по формуле СУММ(СЧЁТ(ПОРТИНТ);1).

Добавлено через 5 минут

Я считал, что если указываются квадратные скобки, то VBA сначала посмотрит не прописано ли такое имя где-то в теле, а только потом, если не найдёт это имя, полезет в Excel, в то время, как Evaluate работает только с Excelem.
Чтож, в силу своего бесповоротного начала в столь интересном деле, как автоматизация и программирование в VBA, я с вами не прощаюсь
Всем ОРГРОМЕННОЕ спасибо за участие! Казанский, Вам отдельное спасибо за столь подробное разъяснение и рабочий код

И ещё вопрос, возможно ли сделать так, что бы VBA так же «разукрашивал» отдельные части кода в строке, т.е. не только стандартные функции и операторы?

Решение

Квадратные скобки «раскрываются» на этапе компиляции. Если компилятор находит содержимое скобок в пространстве имен VBA — использует имя из VBA. Если не находит — вставляет вызов метода Application.Evaluate для всего выражения в скобках, не пытаясь разложить его на составляющие.

Добавлено через 2 минуты

Ребят, а можно ещё спросить? Я думал смогу сам разобраться, но не вышло. Вопрос может показаться не по теме, но на самом деле это продолжение. Дело в том, что если я берусь за что-то, то это что-то должно быть как «заводское». Я пытаюсь написать свой интерпритатор формул. Долго объяснять зачем это, но это нужно. А теперь вопрос:
Если в пользовательской формуле (UDF) аргумент в виде текстовой строки формулы имеет ошибку, например одну лишьную закрывающую или открывающую скобку, то должно всплывать сообщение о соответствующей ошибке. Это я сделал. но проблема в том, что исправление этой ошибки должно замещаться в аргументе исходной формулы. Я всё перепробывал (с мойе точки зрения). я даже написал Sub, котолрый вызывается из тела форсмулы, но и тот (Sub) ведёт себя странно, он может вернуть значение ячейки, но он ничего не может сделать ячейкой. однако, работая «сам по себе» этот Sub и переписывает и затирает предписанные ему ячейки. Возможно всё дело в том, что вызывается Sub из под Function и этому Sub приписываятся свойства Function, т.е. запрет на изменение объектов типа Range? В чём моя ошибка?

Добавлено через 1 час 14 минут
Прошу прощенья за допущенные ошибки при письме, писать пришлось очень быстро.

Excel and UDF Performance Stuff

Charles Williams on ‘Making Excel go Faster’

Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

Prompted by a comment from Sam on Match vs Find I thought I would take a look at Excel’s rather quirky Evaluate method with Excel 2010 to see how it performed.

The Evaluate method internally uses Excel’s formula parser and calculator, and this makes it surprisingly powerful. You can use it on virtually any kind of formula, range reference or Defined Name. But, as we will see, it does have a number of strange “quirks” that you have to navigate around.

Depending on the context Evaluate will either return an object (for example a Range) or values.

I will be using exactly the same test setup of 100000 rows of randomly generated XY pairs and timing routine as in Match vs Find, so you can directly compare the results.

Using the square brackets [ ] shortcut for Evaluate

Sam’s comment suggested using [COUNTIFS] to see how the timing compared with MATCH and FIND. Of course its not quite the same thing because the loop on Match and Find allows the VBA to do something for each XY pair found. Sam’s VBA looks like this:

It takes 11.6 millisecs to find the 25135 XY pairs generated using a constant of 0.5 in the test data generator.

[ ] is a shortcut for Application.Evaluate. The advantage of using the [ ] brackets is that it is concise and the formula inside the [ ] is exactly the same as when used in a worksheet cell. The disadvantage is that you cannot generate the formula as a string. I tend to only use this notation when evaluating my hidden workbook-scoped defined names, because they are not likely to change. (Of course sometimes I get lazy …)

Using Application.Evaluate instead of [ ]

You can use Evaluate or Application.Evaluate with a string instead of the [ ]

j = Evaluate(«COUNTIFS(A1:A100000,» & Chr(34) & «x» & Chr(34) & «,B1:B100000,» & Chr(34) & «y» & Chr(34) & «)»)

The timing is virtually identical to the [ ] shortcut method.

Application.Evaluate and the Activesheet

One trap for the unwary with [ ] , Evaluate and Application.Evaluate is that all references that do not contain a worksheet (unqualified references like A1:A100000) are assumed to refer to whatever the Active sheet currently happens to be.
So if you are going to use Application.Evaluate you should always use a qualified reference (Sheet1!A1:A100000) unless you like your code to live dangerously.

Worksheet.Evaluate

Worksheets and Charts also have an Evaluate Method. When you use these methods unqualified references are assumed to refer to the worksheet or chart.

Now for the surprise: Worksheet.Evaluate is twice as fast as Application.Evaluate!
(actually 6.1 millisecs as opposed to 11.6 millisecs)

I am fairly sure that there is a bug in Application.Evaluate that actually does the evaluation twice.
Certainly if you use Application.evaluate on a UDF the UDF will be executed twice.

Chart.Evaluate

In the real world I have never actually used Chart.Evaluate (probably because I hate programming Chart objects), but according to Help it seems you can do interesting things with it:

“Chart Objects. You can specify any chart object name, such as “Legend”, “Plot Area”, or “Series 1”, to access the properties and methods of that object. For example, Charts(«Chart1»).Evaluate(«Legend»).Font.Name returns the name of the font used in the legend.”

Evaluating Array Formulas

Amazingly if you give Evaluate an array formula it evaluates it as an array formula:

This is quite a lot slower than COUNTIFS: it takes nearly 19 milliseconds.
If you are a SUMPRODUCT fan you could use

j = ActiveSheet.Evaluate(«SUMPRODUCT(—(A1:A100000=» & Chr(34) & «x» & Chr(34) & «),—(B1:B100000=» & Chr(34) & «y» & Chr(34) & «))»)
But its not significantly faster.

Evaluate speed compared to a formula in a cell

You would expect Evaluate to be slower than Excel natively calculating the formula in a cell. And indeed it is, but its quite close for a single formula;

Countifs Formula 6.0
Evaluate Countifs 6.1

Array Sum Formula 16.9
Evaluate Array Sum 18.9

Evaluate speed compared to using Application.Worksheetfunction

It looks like there is a higher overhead to using Evaluate, which is what you would expect.
Here is a version of the FindXY sub using Evaluate with MATCH instead of Worksheetfunction.Match.

Sub FindXYEval()
Dim oRng As Range
Dim oLastRng As Range
Dim j As Long
Dim jRow As Long
Dim n As Long
Dim Rw As Long
Dim dTime As Double
dTime = Microtimer
Set oRng = Range(“a1:A100000”)
Set oLastRng = oRng(oRng.Rows.Count)
Rw = oLastRng.Row
On Error GoTo finish
With Application.WorksheetFunction
Do
Set oRng = Range(oRng(j + 1), oLastRng) ‘ Rw
End With
finish:
Debug.Print “MatchEval ” & n & ” ” & (Microtimer – dTime) * 1000
End Sub

This takes 3720 milliseconds compared to 478 milliseconds using Worksheetfunction.Match. There are just over 50000 calls to Evaluate or Match so I reckon the additional overhead of using Evaluate is about 65 Microseconds per call.

More Evaluate Limitations: Updated

• The string being evaluated must be less than 256 characters, even in Excel 2010.
• A1 style references can be evaluated in both A1 and R1C1 reference mode (Application.ReferenceStyle), but R1C1 style references can only be evaluated in R1C1 mode.
• Relative references in the string are treated as absolute, unless they are contained in defined names in which case the defined name is evaluated with respect to cell A1.
• Dates should be in USA format (Month-Day-Year).
• Evaluate will return an error value if the string formulae contains external references to closed workbooks or XLM functions.
• Using Evaluate INDEX(rng,rownum,COLUMN()) gives incorrect answers except for the first column. Evaluate 0+INDEX(rng,rownum,COLUMN()) works
• If the string formula contains a reference to both a UDF and a name it will fail with an error 2029 if the name reference occurs AFTER the UDF reference:

• If fred is a named range and xyz() is a user defined VBA function then this statement returns error 2029: application.Evaluate(“=xyz(b1)+fred”)
• This statement returns the correct value: application.Evaluate(“=fred+xyz(b1)”)
• Microsoft KB article 823604 identifies this problem but does not correctly diagnose the circumstances that cause it.

You can bypass many of these limitations (at the cost of performance) by inserting the formula string into a worksheet cell and then reading the resulting cell value back into a VBA variable.

Error Handling

If Evaluate cannot evaluate the formula string it returns an error rather than raising an error, so the result of Evaluate should always be assigned to a Variant.

Conclusion

• The Evaluate method adds a lot of power to VBA
• Always use Worksheet.Evaluate rather than Application.Evaluate: its twice as fast and less error-prone
• Using Worksheet.Evaluate has comparable speed to a cell formula but a higher overhead
• Worksheetfunction has a lower overhead than Evaluate
• Beware the Quirks of Evaluate!

So whats your experience of Evaluate?

VBA Evaluate Method

Domski

Well-known Member

I’m slowly starting to get my head around using the Evaluate function which I’ve found you can use sometimes instead of looping through cells in a column.

A couple of examples being:

The first I use as a lot of ID’s on our SAP system export as text with leading zeroes and I format other data to perform lookups. The second I just wrote to answer a post on here for shortest way to fill a range with 51, 101 , 151, 201 etc and it was the shortest I could think of.

I find it a lot faster than looping through thousands of cells and wondered if anyone else used it much and how.

I have never found much documentation on it and the help file makes little sense to me as to how it should be used.

Some videos you may like

Excel Facts

Domski

Well-known Member

There is a discussion here on Planet Evil which I should have included a link to I guess suggesting it’s a very powerful function but little used.

Jon von der Heyden

MrExcel MVP, Moderator

Firefly2012

Well-known Member

Fazza

MrExcel MVP

Trebor76

Well-known Member

This article nicely outlines the advantages of this little known / used VBA function.

DocAElstein

Banned user

. I tossed a coin whether to post this here or in the Lounge, but chose to reply to one of the many Evaluate Threads that I came across in my Googling the last couple of days, as I thought in the future People considering the evaluate Method (Function) might find this or any answers I get to it useful……..

. I was wondering if the somewhat under rated status of the evaluate Function (Method) lies in a too short, simple, or if I may boldly suggest possibly wrong definition. …..As a beginner (Late Starter) I came in with a fresh mind and saw initially the Evaluate function from, for example, typical Googled definitions, as something along the lines of “….taking a string argument….” That “…..allows spreadsheet functions to be used in VBA…”
. I would say a better definition might be along the lines…”within the brackets of the evaluate method —
Evaluate(
That is to say here!
)
— you construct expressions to evaluate by concatenating text, VBA code, and Spreadsheet functions. The concatenating, or as I would say “Going into and out of VBA and Spreadsheet Worlds”, involves a tricky convention with Ampersands and Quotes. Once one has a lot of experience with this one can I suspect almost do anything in a “one liner code”. As such I wonder if the evaluate function comes closer than VBA in truly being a Object Orientated Language. (I missed out on the change from “normal” Programming to the idea of a hierarchy system to allow well structured “one line type codes” in place of many lines of classical code).

. My initial experience with computers stopped about 25 years ago. But I have some very distant fading memories of some very similar looking “Evaluate” thing that had nothing to do with Excel or VBA. I cannot drag out of my memory more on that. Maybe some long experienced computer Profi’s out there could help jog the memory on that one. That being the case, once one has the VBA Evaluate well mastered one might be able to achieve what one wants without Excel or VBA? But I am stretching my beginner’s brain there a bit!
. My conclusions may be naive (or downright wrong!), and I would welcome any comments or criticisms to help me and others learning and / or considering using the evaluate Method. After a few days “Googling” this Theme I see there are some quite differing opinions…..
Alan

jimayers

Board Regular

So I’m reading VBA & Macros 2016 (Jelen) and came across the «evealuate» method. After looking into it and reading some confusing answers am I right in my thoughts:

A loop or array will store values until the loop is finished and then fill the appropriate cells as per the code
whereas evaluate will fill the cell immediately as per the code, and not store anything past the «next» group of cells.

Turn a string into a formula with Evaluate

I encountered a situation recently where I needed to convert a text string into a formula for calculation. I was expecting to find Excel would have the ability to do this. And it does . . . in part.

If you have a number stored as text you can multiply it by 1 and it will be converted to a number. But what if we want to convert a whole text string into a formula, rather than just converting a single number? How can we achieve this? Excel has the EVALUATE formula, however, this is a legacy formula which does not quite function how you might Expect. So, let’s see how we can solve this.

Using EVALUATE to convert string to a formula

Let’s use an easy example of how the EVALUATE function might convert a string into a formula.

In our worksheet we have data in 3 cells

A1 = 1
A2 = +
A3 = 1

If we wanted to use this information to create the formula 1+1 (which equals 2), how could we do it? The EVALUATE formula would be the perfect option, except it only partially exists. If we were to include a formula such as:

Note: The & is used to combine cells together into a single string, so our example would become:

It seems like it might work, but if we try, we will receive an error like this:

However, we can create that same formula as a named range.

Formulas -> Define Names -> Name Manager

Enter the formula in the Refers To box and click OK.

That named range is now available for use in formulas.

The result in Cell C1 will become: 2

This might be useful to a certain extent, but in most circumstances it does not provide a suitable solution for converting a text string to a formula. We need something which gives the same result, but which can be used as a normal Excel formula.

EVALUATE as a User Defined Function to convert string to formula

It is possible to create our own Excel functions with VBA; these are known as a User Defined Functions (UDF). These work the same way as Excel’s standard functions, but we determine how the result should be calculated.

If we insert the following VBA code into a Module of the Visual Basic Editor we can create our own EVALUATE formula.

That’s it. Just 4 lines. Even though Excel does not have the EVALUATE function itself, VBA still does.

We are now able to use this as if it were a normal function.

The result in Cell C1 will become: 2

There is no need to create a named range each time, and this formula is now available for use in our workbook.

Note: If the code is not placed in a Module it will not work.

Conclusion

To convert a text string to a formula it appears the VBA User Defined Function route is the best option. Whilst it is possible to use a named range, it is restricted by what it can achieve.