VBA Evaluate Method

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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:

Code:
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:

Code:
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
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
 
Upvote 0
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
 
Upvote 0
. 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
 
Upvote 0
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
 
Upvote 0
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:

Code:
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.
 
Last edited:
Upvote 0
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:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,520
Members
448,575
Latest member
hycrow

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top