4

I've been searching online trying to figure out what the use of evaluate is. What I get from msdn is: " An expression that returns an object in the Applies To list." I have no clue what this means.

The reason I ask is because I've been given a piece of code and I'm trying to make some logical sense out of it. Why is it written this way? What is the advantage of using evaluate instead of a more traditional approach? What is the correct syntax of a long line of nested functions?

Here is my code example:

With Range("B1", Cells(Rows.Count, "B").End(xlUp))
        .Value = Evaluate("Index(If(Left(Trim(" & .Address & "),1)=""."",Replace(Trim(" & .Address & "),1,1,""""),Trim(" & .Address & ")),)")
End With

Can someone help me break this down and make some sense out of it? It is supposed to remove leading periods and get rid of excess spaces in all cells in column b. My problem is that it only works if I run it twice. If I could make some sense out of this then I may be able to manipulate it to make it function correctly.

For extra credit, How would I build a statement like this if I wanted to go through the same range and remove all dashes ("-")?

I really want to learn. Any help appreciated.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Matthew Paulin
  • 59
  • 1
  • 2
  • 11

1 Answers1

3

OK here goes:

Evaluate tells the application to evaluate a function in this context.

The function is a string concatenation of "Index(If(Left(... which includes some dynamic components (.Address), because it's being applied to the entire range:

Range("B1", Cells(Rows.Count, "B").End(xlUp))

What this does, effectively, is to evaluate the formula for each cell in that range, but only writes the formula's evaluated value to each cell.

Equivalent would be to fill the range with the formula, and then do a copy + paste-special (values only) to the range. This is obviously more expensive in terms of memory and time consuming processes, especially for a larger range object.

I personally don't favor this approach, but that's a matter of my personal preference primarily.

Advantages in this case is that it's filling in an entire range of cells -- which could be 10 cells or 10,000 cells or 1,048,576 cells (in Excel 2007+) in one statement.

Alternative methods would be to do a For/Next loop (which is expensive in terms of memory and therefore slow on large ranges), even if you're doing a loop over an array in memory and writing the resulting array to the worksheet, I think there is a certain elegance to using a single statement like this code.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • 2
    +1 David, very good explanation for a learner. Specially `What this does, effectively, is to evaluate the formula for each cell in that range, but only writes the formula's evaluated value to each cell.` This should be on MSDN :) [Additionally have a look at Evaluate() in VBA](http://stackoverflow.com/questions/17484571/evaluate-in-vba) –  Sep 05 '13 at 08:55
  • ok so let me see if I understand this. With evaluate, instead of storing a value to a variable, performing an operation on the variable, and then returning the variable value back to the sheet, The evalutate function does this on the fly? how does this differ from using Application.WorksheetFunction Property ? – Matthew Paulin Sep 05 '13 at 10:29
  • @MatthewPaulin try writing this formula out using `Application.WorksheetFunction` and see how far you get :) `Evaluate` lets you write the formula more-or-less as you would in the cells themselves. – David Zemens Sep 05 '13 at 13:18
  • I thank you for your patience. I'm just trying to determin why the evaluate would be better than something simple like: `Sub test456() Dim myRange As Range, c As Range; Set myRange = Range("B1", Cells(Rows.Count, "B").End(xlUp)); For Each c In myRange c.Value = Trim(Replace(c, ".", "", 1, 1)); c.Value = Replace(c, " ", " "); Next; End Sub` – Matthew Paulin Sep 05 '13 at 14:21
  • Sorry for double post, but to clarify the above, Is the sole reason for using evauluate instead of a for/next loop to save processing time? And at would point/context would this be a noticable difference (10k cell range, 100k cell range...)? – Matthew Paulin Sep 05 '13 at 14:52
  • 1
    See also this post for some of the quirks ofg Evaluate http://fastexcel.wordpress.com/2011/11/02/evaluate-functions-and-formulas-fun-how-to-make-excels-evaluate-method-twice-as-fast/ – Charles Williams Sep 05 '13 at 21:58