VBA Evaluate Method

[Domski]

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:

Sub Convert_Pers_Nums()

' formats values in a column as fixed length text string padded with 0's

    Dim strFormat As String
    Dim intFormat As Integer


    intFormat = InputBox("How many characters?")
    strFormat = WorksheetFunction.Rept("0", intFormat)
    With Selection
        .NumberFormat = "@"
        .Value = Evaluate("IF(" & .Address & " = """","""",Text(" & .Address & ",""" & strFormat & """))")
    End With


End Sub

and:

Sub Fill_Col()


' fills range with mutliples of n x 50 + 1


    With Range("H1:H200")
        .Value = Evaluate("Row(" & .Address & ")*50+1")
    End With


End Sub

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.

Dom

 

[Domski]

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.

Dom

 

[Jon von der Heyden]

I use it a lot in the way you describe in your 1st post.

 

[Firefly2012]

I tend to use it to bulk trim field values from lots of csv files I receive which have generouse quantities of trailng spaces in them

 

[Fazza]

some old threads http://www.mrexcel.com/forum/showthread.php?262787 and http://www.mrexcel.com/forum/showthread.php?272459
[TABLE="width: 48"]
[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[/TABLE]

 

[Trebor76]

Hi Domski,

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

Robert

 

[DocAElstein]

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

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

Thanks for your help - Jim A

 

[Jon von der Heyden]

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

Thanks for your help - Jim A

You're kinda on the right track. Here is how I describe it:

A loop is an iterative process (I know you know this). The loop logic / instruction is completed at the point of each iteration, and then say the result is written back to the respective cell. This may be fine (say using a For Each), but it doesn't take place in one hit, and the changes may be slower than an Evaluate method, especially if you haven't considered things like Calculation (i.e. calculation may be invoked each time a cell is changed, i.e. at each iteration). Really it depends on how the code and loop is written.

The Evaluate method, by contrast, and in the manner that I believe you are referring to, is a bit like entering a block array formula, but instead of yielding formula result, it's value results are given. I don't suppose I am explaining it well, but my understanding is that it's application is similar to the following: Say you have some random values in A1:A10 (go ahead and enter some random numbers say between 1 and 100). In B1:B10 you want to gross up these numbers by 20% (e.g. UK standard VAT rate).

Now go ahead and select B1:B10 (make sure you grab all ten cells). Now type:


=A1:A10*1.2

And commit the formula with Control+Shift+Enter, NOT just Enter. If done properly you will see that Excel surrounds the formula with curly braces, indicating that this is an array formula. I like to look at these as a single entity, i.e. the formula in B1 is not apart from the formula in B2. The span of 10 cells contains a single common formula. Go ahead and try and change one of them. You will notice that Excel forbids the change - you will need to affect the entity as a whole. I see this as invoking calculation only once, and I believe all results are computed in memory (as it is an array), which tends to be quicker than in range (e.g. For Each loop). So the equivalent could be:

With Range("A1:A10")
    .Offset(, 1).Value = Evaluate("if(row(" & .Address & ")," & .Address & "*1.2)")
End With

But you need to exercise caution using Evaluate, because there are some pit falls, such as handling date values (interpreted in US MDY format), and formula length (limited to 255 chars).

The best resource I can think of to point you to is Charles Williams blog post here.

 

[jimayers]

You're kinda on the right track. Here is how I describe it:

A loop is an iterative process (I know you know this). The loop logic / instruction is completed at the point of each iteration, and then say the result is written back to the respective cell. This may be fine (say using a For Each), but it doesn't take place in one hit, and the changes may be slower than an Evaluate method, especially if you haven't considered things like Calculation (i.e. calculation may be invoked each time a cell is changed, i.e. at each iteration). Really it depends on how the code and loop is written.

The Evaluate method, by contrast, and in the manner that I believe you are referring to, is a bit like entering a block array formula, but instead of yielding formula result, it's value results are given. I don't suppose I am explaining it well, but my understanding is that it's application is similar to the following: Say you have some random values in A1:A10 (go ahead and enter some random numbers say between 1 and 100). In B1:B10 you want to gross up these numbers by 20% (e.g. UK standard VAT rate).

Now go ahead and select B1:B10 (make sure you grab all ten cells). Now type:


=A1:A10*1.2

And commit the formula with Control+Shift+Enter, NOT just Enter. If done properly you will see that Excel surrounds the formula with curly braces, indicating that this is an array formula. I like to look at these as a single entity, i.e. the formula in B1 is not apart from the formula in B2. The span of 10 cells contains a single common formula. Go ahead and try and change one of them. You will notice that Excel forbids the change - you will need to affect the entity as a whole. I see this as invoking calculation only once, and I believe all results are computed in memory (as it is an array), which tends to be quicker than in range (e.g. For Each loop). So the equivalent could be:

With Range("A1:A10")
    .Offset(, 1).Value = Evaluate("if(row(" & .Address & ")," & .Address & "*1.2)")
End With

But you need to exercise caution using Evaluate, because there are some pit falls, such as handling date values (interpreted in US MDY format), and formula length (limited to 255 chars).

The best resource I can think of to point you to is Charles Williams blog post here.

Thank you for your response. i am starting to get a picture, but have yet been able to write my own making it work. But I still get tied up with simple things like:
in your example why "offset?"
Jim A