Interesting observation about evaluate command

[CodeNinja]

One of my favorite new "Toys" in VBA is the command Evaluate. For those of you who do not know what this does, it is one of the more powerful tools available to you, so I highly suggest playing with it.

Evaluate basically takes a string and runs any functions based on that string (or at least that is my understanding of it). So, if you did:
i = evaluate("3 + 4")
msgbox(i)

You would get a message box showing 7.

Evaluate also works with functions you create... so if you did the following:

[vba]Public Function getColLtr(i As Integer)
getColLtr = Split(Sheet1.Cells(1, i).Address, "$")(1)

End Function

Sub test2()
Dim s As String
s = "getColLtr(5)"

MsgBox (Evaluate(s))
MsgBox (getColLtr(5))

End Sub
[/vba]
You will get two message boxes of "E". That is to be expected. What I found interesting is that when I run the code in step mode, it never steps through the function when Evaluating, but it does step through when iterating the second message box.

Bizarre. I tested to see if evaluate was more efficient (ran faster) than the same function without evaluate, and no big surprise, the act of evaluating makes the function less efficient. I just think it is interesting in the step through that it does not iterate through any functions.

No questions here, just an odd observation.

Thanks.

[Trebor76]

but it does step through when iterating the second message box

Correct. That's because that line is calling the "getColLtr" function, but the previous line isn't.

The Evaluate function does what its name suggests, i.e. calculates the result of a string formula passed to it. It's a great alternative, amongst other uses, to trying to find a blank cell for your code to calculate a formula for later use.

Aaron Blood gives a great over of the function here.

Though there's generally no need to convert a column number to a letter for VBA use, the following is an example of the use of the Evaluate function in practice:

Option Explicit
Sub Macro5()
    'http://www.vbaexpress.com/forum/showthread.php?t=42695
    Dim intMyColNum As Integer
 
    intMyColNum = 5
    MsgBox Evaluate("substitute(address(1, " & intMyColNum & ", 4), ""1"", """")")
 
End Sub

Regards,

Robert

[snb]

In that case I'd prefer:

[vba]sub snb()
j = 5
MsgBox Split(Columns(j).Address(, 0), ":")(0)
end sub[/vba]

or

[VBA]
MsgBox [substitute(address(5,5,4),5,"")]
[/VBA]

[CodeNinja]

Aaron Blood gives a great over of the function here.

Trebor76,
That article was huge for me. Thanks.

[Kenneth Hobs]

Here is an example using the split method and evaluate. The [] method will be immediate but the string for Evaluate method lets you build the expression to evaluate.

[VBA]'http://www.mrexcel.com/forum/showthread.php?t=338789
Sub test_ColumnLetter_ColumnNumber()
MsgBox ColumnLetter(40), , "40"
MsgBox ColumnNumber("AN"), , "AN"
MsgBox ColumnLetterSNB(40), , "40"
End Sub

Function ColumnLetter(ColumnNum As Integer) As String
ColumnLetter = Split(Cells(1, ColumnNum).Address, "$")(1)
End Function

Function ColumnNumber(sColumnLetter As String) As Integer
ColumnNumber = Cells(1, sColumnLetter).Column
End Function

Function ColumnLetterSNB(ColumnNum As Integer) As String
Dim s As String
' http://www.vbaexpress.com/forum/showthread.php?t=42695
'MsgBox [substitute(address(5,5,4),5,"")]
s = "Substitute(Address(" & ColumnNum & ", " & ColumnNum & ",4)," & ColumnNum & ","""")"
ColumnLetterSNB = Evaluate(s)
End Function[/VBA]

When evaluating a range of cells, one could iterate each one. Using evaluate is more of a one liner method just up snb's alley. Here, I simply convert a range of cells to their absolute value or rewrite if not a number.

[VBA]Sub Test_RangeNumbersToAbsolute()
' [A1:A5] = [If(IsNumber(A1:A5), Abs(A1:A5), A1:A5)]
RangeNumbersToAbsolute "A1:J5"
End Sub

Sub RangeNumbersToAbsolute(x As String)
Range(x).Value = Evaluate("If(IsNumber(" & x & "),Abs(" & x & ")," & x & ")")
End Sub
[/VBA]

[Bob Phillips]

Using Evaluate on one of your own functions makes no sense to me. The only reason I can think to use it at all is to evaluate array formulae within VBA as Application and WorksheetFunction both fail with these.

[snb]

This way you can use the shorthand version of Evaluate:

[VBA]
Sub snb()
MsgBox ColumnLetterSNB(40)
End Sub

Function ColumnLetterSNB(ColumnNum As Integer) As String
ThisWorkbook.Names.Add "dw", ColumnNum
ColumnLetterSNB = [substitute(address(5,dw,4),5,"")]
End Function
[/VBA]

[Jan Karel Pieterse]

In 16 years of VBA programming I've only had one project where I could actually use the Evaluate function.
I needed a way for the user to specify the formula for the surface area calculation of shapes. The user wanted to be able to define his own shapes on a worksheet, where he could set up which dimensional variables there were and what formula would yield the surface area and volume of the shape. That is where Evaluate was useful.
Otherwise, I haven't seen any examples of evaluate you couldn't do more efficiently using plain straight-forward VB code.

[CodeNinja]

In 16 years of VBA programming I've only had one project where I could actually use the Evaluate function.
I needed a way for the user to specify the formula for the surface area calculation of shapes. The user wanted to be able to define his own shapes on a worksheet, where he could set up which dimensional variables there were and what formula would yield the surface area and volume of the shape. That is where Evaluate was useful.
Otherwise, I haven't seen any examples of evaluate you couldn't do more efficiently using plain straight-forward VB code.

Actually, I thought evaluate was a perfect solution for this post:

http://www.vbaexpress.com/forum/showthread.php?t=42646

It is what got me looking at evaluate in the first place.

[Jan Karel Pieterse]

Yep, that is one situation where evaluate comes in handy.

[Trebor76]

I've actually posted the following on that thread as a ways to show the usefulness of the Evaluate function:

Option Explicit
Sub Macro1()
    
    'Return the value of the formula =MID(B2,FIND("=",B2)+1,LEN(B2)-FIND("=",B2)+1) to cell A4
    Range("A4").Value = Evaluate("MID(" & Range("B2").Address & ",FIND(""=""," & Range("B2").Address & ")+1,LEN(" & Range("B2").Address & ")-FIND(""=""," & Range("B2").Address & ")+1)")
    
End Sub

[snb]

Which could also be gotten in VBA using:

[vba]
range("A4")=split(range("B2"),"=")(1)
[/vba]

or

[VBA][A4]=[MID(B2,FIND("=",B2)+1,LEN(B2))][/VBA]

[Bob Phillips]

Which could also be gotten in VBA using:

[vba]
range("A4")=split(range("B2"),"=")(1)
[/vba]

or

[VBA][A4]=[MID(B2,FIND("=",B2)+1,LEN(B2))][/VBA]

The second is just another way of using Evaluate!

[snb]

You get today's bonus points ! Congrats.

[Bob Phillips]

And you get the points for repeating what someone else has already provided. Wastrel.

[snb]

Thanks