At times we need to change cell values in a range based on a calculation. The most common way of doing it is using loop.

**e.g. **We have numeric data in range A1:B10 of Sheet1 and we need to multiply all cells of this range by 2.

```
Sub Test()
Dim rngData As Range
Dim rngCell As Range
Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
For Each rngCell In rngData
rngCell = rngCell * 2
Next
End Sub
```

In the above example, **Evaluate** can be used instead of looping through cells, it works faster than a loop.

```
Sub Test()
Dim rngData As Range
Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
rngData = Evaluate(rngData.Address & "*2")
End Sub
```

Let’s take another example. Suppose we’ve some text values in range A1:A10 and we want to extract and keep only the first three letters of the values in all cells of this range. We could try to do so using the below code:

```
Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:B10")
rngData = Evaluate("Left(" & rngData.Address & ",3)")
```

BUT, you’ll find it does not work. It will fill the whole range with first 3 letters of cell A1. The reason is that if the Excel function used in **Evaluate** does not accept an array, the **Evaluate** function will not return an array. So in order to make this function return an array we need to modify the code slightly like this:

```
Set rngData = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
rngData = Evaluate("if(Row(1:10),left(" & rngData.Address & ",3))")
```

In this case, *ROW(1:10)* returns an array of numbers from 1 to 10. Any numeric value other than 0 returned from a logical function is considered as TRUE, So there are 10 vertical TRUE values. For each TRUE, it will return the corresponding cell’s value from A1:A10.

Similarly it can be used for a horizontal range, containing one row and multiple columns.

**Try it Out:**

What would you do for a two-dimensional range, containing multiple rows and multiple columns? There is actually a trick that needs to be used to be able to use **Evaluate**. Try it out and find out yourself or wait for a follow-up 😉

**Other Methods of Avoiding Loops**

Application.Index to Slice Arrays

Why not use PasteSpecial instead of looping?

Option Explicit

Sub RngMultiply()

Dim ws As Worksheet

Dim r As Range

Set ws = ThisWorkbook.Worksheets(“Sheet1”)

With ws

.[C1] = 2

.[C1].Copy

Set r = .[A1].CurrentRegion

r.PasteSpecial _

operation:=xlPasteSpecialOperationMultiply

End With

Set r = Nothing

Set ws = Nothing

End Sub

@winston i am also not in favor of using loops thats why i’ve shared better way of doing it.Evaluate is faster….Try running it on large data set,you will find Evaluate faster than your approach.

and See other examples also that i shared above, that are not about multiplication.

[…] You are welcome and thanks for the feedback. You can find an explanation about how it works at VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan Hope this helps […]

[…] Thanks Rory, will look into sorting the data first, was hoping I could leave it alone – hence using MATCH is optional parameter 0 for exact match as they're unique values. FYI xenou, a link with better explanation of using Evaluate; Evaluate doesn't accept an array and hence return an array, so to make it return an array, you need to modify the code: VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan […]

Hi,

. I am not a Programmer, but am just learning VBA and currently must get to grips with this “Row” thing in Evaluating Stuff!

. By experimenting I notice that your second programm does NOT work if you remove the & “*2”

. It does however if you add the Row() thing:

Sub TestUsefulGyaanRow()

Dim rngData As Range

Set rngData = ThisWorkbook.Worksheets(“Fox”).Range(“A1:B10”)

rngData = Evaluate(“IF(Row(),” & rngData.Address & “)”)

End Sub

(Also Column() instead of Row() works just as well.)

How does that tie up with your comment “……The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array………”

I am also playing around with concatenating using the Evaluate thing. There are similar strange results there: If I were to modify the program above to concatenate-

Sub TestUsefulGyaanIsConcatenating()

Dim rngData As Range

Set rngData = ThisWorkbook.Worksheets(“Fox”).Range(“A1:B10”)

rngData = Evaluate(rngData.Address & “&”” – “”&” & rngData.Address)

End Sub

Then it does work without the Row() or Column() bit

Alan

An Old Confused Physisist with no idea about computers!!

[…] #15 in Multiple Columns Into Single Column Using Data Text To Column – Page 2 And general reference VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate | Useful Gyaan ) . But thanks again, your reply may have put me back on track on this one. I will get on to that […]

[…] […]

what if i want to multiply by a variable x instead of 2 , it does work

e.g , we calculate x (=2) and then want to a range*x

Yes.It will work.

e.g.

rngData = Evaluate(rngData.Address & “*” & x)

Hi Transformer,

So coming back to my question..

… if

rngData = Evaluate(rngData.Address & “*” & x)

and

rngData = Evaluate(rngData.Address & “*2” )

and

rngData = Evaluate(rngData.Address & “&”” – “”&” & rngData.Address)

works, but something like

rngData = Evaluate(rngData.Address)

only works with the Row() stuff, then how does that tie up with your comment

““……The reason is that if the Excel function used in Evaluate does not accept an array, the Evaluate function will not return an array………”

I am just trying to understand it a bit better – In the meantime I have some pretty amazing Evaluate “one liners” developed in particular from those threads referenced above…. But I still struggle to understand how they work!!, or as I think the saying goes with you guys.. “are coerced” to work”!!!

Thanks again

Alan

Hi Alan,

Evaluation of rngData.Address is not same as the evaluation of functions that do not accept an array. You might have noticed, in case of Evaluate(rngData.Address) you get blank cells but in case of functions it fills the whole range with the first value.

When an operation is performed on range

e.g. Evaluate(rngData.Address & “+0”)

then Evaluate function returns variant array but if we pass a range without permorming any operation on it

e.g. Evaluate(rngData.Address) then it returns range, hence it doesn’t fill any value in the target range.To check the same try this:

rngData.Address = Evaluate(rngData.Address).Value

However, if one needs to fill one range with the values of other range without performing any calculation then one can do it simply like this:

rngDataNew.Value = rngData.Value

Evaluate function has got some limitations as well,it is worth checking the below link:

http://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/

. Hi Transformer.

. Thanks for reply. Some interesting new info all suggesting that exactly how Evaluate works is more complicated than simply a method to combine worksheet functions and VBA code.

. Before I try to understand it further..were, or would, you expect that I get the following results which I appear to…

.1 rngData.Address = Evaluate(rngData.Address).Value – gives an error which roughly translated says “cant assign to a constant”?

.2 Both these variations work and return the correct range values

rngData = Evaluate(rngData.Address).Value

rngData.Value = Evaluate(“” & rngData.Address & “”).Value

Alan

Hi Alan,

I meant

rngData = Evaluate(rngData.Address).Value

it was a typing mistake.Important thing is right hand side of the statement that shows Evaluate(rngData.Address) returns a range object, value property has been used to show the same.

Hi Transformer,

. Many thanks, the typo had me confused.

. Some further results may support what you were trying to say about Evaluate(rngData.Address) returning a range object. – Assuming rngA is simply Range A3:A10 and similarly rngC is simply range C3:C10….

rngC = Evaluate(rngA.Address).Value ‘ This works returning the correct values

rngC.Value = Evaluate(” ” & rngA.Address & ” “).Value ‘This works returning the correct values

rngC = Evaluate(rngA.Address).Address ‘ This Returns $A$3:$A$10

rngC.Value = Evaluate(rngA.Address).Address ‘ This Returns $A$3:$A$10

. As explained once line has been coerced into working with

rngC = Evaluate(“IF(Row(),” & rngA.Address & “)”)

Or another variation I found along the way!

rngC = Evaluate(rngA.Address & “&”” “””)

This will produced the correct values rather than empty cells.

. So then the following will not work

rngC = Evaluate(“IF(Row(),” & rngA.Address & “)”).Address

rngC = Evaluate(rngA.Address & “&”” “””).Address

Giving error 424 Object needed. Again maybe supporting your argument that the range object has been changed now to a variant

. However I get more and more baffling results the more I experiment. I give just a few:

. I believe the following 6 lines are from the syntax the same.

rngC = Evaluate(rngA.Address & rngA.Address).Address

rngC = Evaluate(“” & rngA.Address & “” & “” & rngA.Address & “”).Address

rngC = Evaluate(” ” & rngA.Address & “” & “” & rngA.Address & ” “).Address

rngC = Evaluate(“” & rngA.Address & ” ” & ” ” & rngA.Address & “”).Address

rngC = Evaluate(” ” & rngA.Address & ” ” & ” ” & rngA.Address & ” “).Address

The first 3 give error 424 Object needed, but line 4 5 return the address $A$3:$A$10

. But you can spend hours with different variations and usually find something that works as you wish it to!

. I am still wondering whether The Range Evaluate thing, with tricks to “coerce” are based on tuition and trial and error rather than really understanding what is going on..

. But I could write a Book on endless variations of all the above. I will not confuse the issue here with any more examples!!

Alan

Hi

This article sparked off my interest in Range Evaluate a couple of years back:

Re, the question: “…What would you do for a two-dimensional range, containing multiple rows and multiple columns? …”

I would start like this:

In a spreadsheet, Excel will, in many cases, allow us to use a range reference of number of cells in a formula in the place where one might initially expect the syntax to accept one cell. This is done to allow the use of such formulas to return an Array output. One of the things that the famous Control+Shift+Enter, CSE, is about is to simply “tell” excel that you want the formula carried out repeatedly for a number of cell references. It is arguable that Excel holds the evaluated formula in such a multi cell argument formula usage always, but that one reason for Control+Shift+Enter, – just an arbitrary key combination to trigger Excel to do things a bit differently, or make all the values “available”. In the Type 2 CSE formula entry, you need to select a range of cells for the multiple value output. In the case of the Type 2 CSE formula entry, one could argue that Excel simply needs to have a cell range to fit in all the values.

I would suggest there is a parallel with the how formulas under the CSE “instruction” work and any formulas generally whether they be in VBA Item Properties, Evaluate or in a spreadsheet cell: If Excel “goes through Items in a range, or “sees” a formula containing a function / functions that does / do return an Array / Arrays, then it will proceed as follows:

It will do the evaluation for as many times as

( largest “horizontal” Array ) x ( largest “vertical” Array )

Excel expects therefore that number of answers, a collection. I would suggest / guess that it works in its “Item” mode, sequentially going through the Items. The Item order for things related in spreadsheet ranges follows the convention “..all columns across from the left in a row, then “down”” to the next row..”. Subsequently Excel places those sequential values in an order of columns then rows which mirrors the

( largest “horizontal” Array ) x ( largest “vertical” Array )

During the evaluations if parts of the formula containing normal functions which do not return Arrays, but which have “available” a range of answers, are present due to the multi cell references in it arguments, then Excel proceeds as follows. For each Item value it takes for the ( largest “horizontal” Array ) x ( largest “vertical” Array ) , it will look for, and take, if available, the corresponding “horizontal” and “vertical” value “presented” / “available” and use that value in that Item evaluation.

Regarding the question: “…What would you do for a two-dimensional range, containing multiple rows and multiple columns? …”

Take the very simple case of a spreadsheet range of this 2 row x 2 column form in the range say B2:C3

1000 2000

3000 4000

Evaluate(“=Left(B2:C3,3)”) will return just

100

Evaluate(“=If(Row(1:3)*Column(A:C),Left(B2:C3,3))”) will return

100 200 Err

300 400 Err

Err Err Err

This last result ties up with my explanation above: Evaluation is done for the maximum rows x maximum columns

The criteria of the If Function first argument is always met ( we always have those rows ) , so at each evaluation the second argument is carried out. This produces a returned error , Err, when those “horizontal” or “vertical” Items are not available.

The following result are possibly not immediately obvious.

Evaluate(“=If(Row(1:3)*Column(A:A),Left(B2:C3,3))”) and

Evaluate(“=If(Row(1:3),Left(B2:C3,3))”) will return

100 200

300 400

Err Err

But the same explanation is valid. Once any Array returning Function is present then all Arrays, including the available, but not returned results from Left(B2:C3,3). So Excel sets the maximum “horizontal” to 2 columns.

The following results are explained similarly

Evaluate(“=If(Row(),Left(B2:C3,3))”) and

Evaluate(“=If(Column(),Left(B2:C3,3))”) will return

100 200

300 400

In a spreadsheet the Row() or Column() returns the row or column number of the cell it is in. The evaluation occurs at Hitting Enter. Effectively then the evaluation is done on what Excel is considering to be the active cell at the time. Excel will take what it is considering to be the active cell for Evaluate. ( In which Worksheet will depend on where the code is , that is to say if Evaluate defaults to the Application.Evaluate or a Worksheets(“ “).Evaluate )

But regardless of which cell and correspondingly which result is returned, we always have the If Function first argument met, so the second argument is evaluated. The important point again here is that the Row() or Column Function returns an Array. It is in this case only a one element Array. But it is never the less an Array returning function. This causes Excel to do evaluations and return results for the largest Array in the total formula. So this is set in this case by the Array size of B2:C3

Alan

Ref for last Post mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column-2.html#post4375354

Hi, just adding a bit further to my last post….

_1) just another angle on a basic explanation of what is generally going on

_2) answering a variation of the question … : “…What would you do for a two-dimensional range, containing multiple rows and multiple columns? …”

_1) So…. Start again with the basic problem of getting the Spreadsheet Function Left( , ) to return us an Array with say this in it

100

300

Abd to get that I am using a spreadsheet range, say B2:B3, with these values in the cells

1000

3000

In my post above and in the Blog in general we demonstrated the = If(Row( ) , Left(B2:B3,3) ) idea for use within Evaluate(“ “)

Condier now the situation in the Spreadsheet:

If one tries to use formulas of that form writing the formula directly into a spreadsheet cell they fail ( mostly ***** )

If one puts those forms of our formulas in a cell and Hits Ctrl+Shift+Enter, rather than Enter, ( known as “CSE Type 1”) then they do not fail but only give the first value,

100

If a two cell “vertical” range in the spreadsheet is selected instead of a single cell, and the formula put in the formula bar, and then entered with Ctrl+Shift+Enter, ( known as “CSE Type 2” ), then we get

100

300

Also in the spreadsheet, repeating the above using = Left(B2:B3,3) instead of = If(Row( ) , Left(B2:B3,3) ) also returns

100

for the CSE Type 1 and

100

300

For CSE Type 2

The problem in the spreadsheet without the use of CSE could be explained that actually having the formula within the cell cause the normal Excel process to be in place. This process is very complicated and is probably a trade secret. But my experiments suggest that what we “see” is just a display based on a complex set of offset memory locations which are based on a single Excel Range Object. What we see is based in some complex way on where rows and columns intercept.

( ***** We note here as an aside, that a formula such as = Left(B2:B3,3) or even =B2:B3 will not fail if it appears in either row 2 or row 3 anywhere in the spreadsheet. Again this is probably down to how Excel “wors” – I note that in general in code work single “breadth” Arrays ( 1 Dimension or 1 column 2 Dimensional Arrays or 1 row 2 Dimensional Arrays ) work very efficiently. They appear to work differently in VBA to how multiple dimension Array calculation works. Excel seems to “hold” them as an Array which duplicates the row or column across the whole worksheet. They are always there, but mostly you do not see them. The intercept process works then very efficiently. Note however, if =B2:C3 is written in row 2 or 3, then the formula still fails. The memory space in the other dimension cannot be used for the “duplicates” as then it is needed for the additional dimension in a 2 or more “breadth” Array )

CSE has the effect I think of “removing” the connection / Intercept Excel process. In addition it instructs to carry out the “ …all columns in a row , then next row ..” multiple Item sequential evaluation. The selecting before the CSE entry, if done, of a multiple cell range will effectively “disconnect all those cells”, and they are available to display all available results. Selecting before the CSE entry a spreadsheet area too big for a 2 Dimension range, such as =B2:C3 , ( That is to say selecting E4:G6 before the CSE 2 Type entry of =B2:C3 in that range ) , will give errors in the extra cells outside the dimensions of 2 x 2, that is to say G4:G6 and E6:G6 will display errors. Selecting before the CSE entry a spreadsheet area too big for a 1 “breadth” range such as B2:B3, will duplicate the results in the extra columns and only error in the extra rows. This ties up with the special way Excel handles a single “breadth” Array. You see now it is somehow “holding” it as available values ( across in this case ) the entire worksheet.

Evaluate (“ “) is approximately explained sometimes as doing what would be done if the enclosed within the “ “ string were in a spreadsheet cell. But it does not actually put the string on a cell. It presumambly share oor uses some similar coding. There is no need to “remove the connection” for a formula holding an Array. It does the evaluation without the “link” to a cell. Orin other words, it is doing the process internally. This

Evaluate(“=B2:C3”)

will “work” returning a 2 x 2 Array of the values in that spreadsheet range. We take no extra action as in the CSE to “decouple it” . Evaluate is a VBA Function. We find that in VBA generally if Arrays are accepted as arguments and are used, then the “ …all columns in a row , then next row ..” evaluation takes place returning results in an Array of dimensions size given by

( largest “horizontal” Array ) x ( largest “vertical” Array )

As discussed previously Arrays “hidden” will be used in the evaluation and also in the determining of the output sized Array

( largest “horizontal” Array ) x ( largest “vertical” Array )

This would appear to be intrinsic to a VBA Function that accepts Arrays as arguments. As we have seen a simple If(Row(),__ or any such thing will do to get the VBA Evaluate to , that is to say VBA to “kick in” it’s the “ …all columns in a row , then next row ..” evaluation. This is often called coercing. Part of CSE entry in a spreadsheet does the “turning on” of the sequential by Item evaluation.

Another common way is to do the “kicking in” “ coercing” is to use the syntaxly accepted

Evaluate(“=Index(Left(B2:B3,3),)”) or Evaluate(“=Index(Left(B2:B3,3),,)”)

or often seen in this form

Evaluate(“=Index(Left(B2:B3,3),0)”) or Evaluate(“=Index(Left(B2:B3,3),0,0)”)

In a spreadsheet =Index(Left(B2:B3,3),) follows the behaviour requiring to be in row 2 or 3 or alternatively the CSE entry is required for it to “work”. For something of 2 dimensions such as Index(Left(B2:C3,3,,) or Index(Left(B2:C3,3,0,0) it also works with the CSE entry, but not in any cell without the CSE entry. ( Note the two , , in the last formulas) What is “working” here is Index doing its job of presenting the “Array” given. It would appear that the way Index works is to “look in” or “Look for” the Array. The first argument of the Index is simply being evaluated to return the Matrix Array it require , ( if index is able to) . Once again we do not need the “CSE decoupling” from the spreadsheet when doing this within Evaluate(“ “), and also a double or single comer will do for any range. This last point somehow also ties up that we are not decoupling a 2 dimensional area for output with Evaluate(“ “), but we do need to do that in a spreadsheet. Index here is effectively giving us whole rows and columns, or both. Index works like this. This is the so called splitting phenomena discussed also in another Blog at this site !!!!! Ref

Ref mrexcel.com/forum/excel-questions/806702-visual-basic-applications-evaluate-range-vlookup-2.html#post3946289

!!!!! Ref usefulgyaan.wordpress.com/2013/06/12/vba-trick-of-the-week-slicing-an-array-without-loop-application-index/

_2) Now, answering a variation of the question … : “…What would you do for a two-dimensional range, containing multiple rows and multiple columns? …”

We have seen that in actual fact there is no need to include any range in the Row( ) or Column or Row( ) x Column ( ) tricks. Once the Array .. “ …all columns in a row , then next row ..” evaluation “kicks in”, then the maximum rows and columns evaluated in Item sequence is determined by the biggest range anywhere, ( including any Arrays “hidden” in a function which does not normally return from itself its available values). As we saw we only need a range reference to extend the evaluation into further rows or columns. So we would use this to give us an Array output.

For example

Evaluate(“=If(Row(1:3)*Column(A:B),2)”) will cause 2 to be evaluated for 3 rows and 2 columns returning

2 2

2 2

2 2

Evaluate(“=If(Row(1:3)*Column(A:B),200*Row(1:2))”) will return

200 200

400 400

Err Err

Such code lines seen typically with an Address in the ( ) of the Row( ) or Column( ) Functions are a convenient, fast “one liner” , “Range Evaluate” way to generate Arrays to be used for example in the arguments of a nice “one liner Index” Code to generate a range.

Ref mrexcel.com/forum/excel-questions/908760-visual-basic-applications-copy-2-dimensional-array-into-1-dimensional-single-column.html#post4370502

Ref excelforum.com/showthread.php?t=1138428&p=4381996&highlight=#post4381996

Ref excelforum.com/excel-programming-vba-macros/1138627-dividing-the-items-of-an-array-over-multiple-columns.html#post4382887

Ref excelforum.com/excel-programming-vba-macros/1162669-vba-to-assign-value.html

Alan