VBA Trick of the Week :: Avoid Loop for Range Calculations – Evaluate

Posted: June 19, 2013 by Transformer in Excel, VBA
Tags: , , ,

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

 

Advertisements
Comments
  1. Winston Snyder says:

    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

  2. Transformer says:

    @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.

  3. […] 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 […]

  4. […] 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 […]

  5. Dr. (Physics(Retired!)) Alan Elston says:

    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!!

  6. […] #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 […]

  7. yu says:

    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

    • Transformer says:

      Yes.It will work.
      e.g.

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

      • Dr. (Physics(Retired!)) Alan Elston says:

        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

      • Transformer says:

        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/

      • Dr. (Physics(Retired!)) Alan Elston says:

        . 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

      • Transformer says:

        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.

      • Dr. (Physics(Retired!)) Alan Elston says:

        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

  8. Alan Elston says:

    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

    • Alan Elston says:

      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

  9. Alan Elston says:

    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

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s