CODEWITHSUNDEEP
vbaexcel
Matthew Paulin
Matthew Paulin

Reputation: 59

Breaking down evaluate in vba

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.

Upvotes: 4

Views: 9861

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 3

Related Questions