Excel VBA Tips n Tricks #3 Most powerful function in Excel Visual Basic - The EVALUATE Formula

Using the Evaluate function, exclusive to Excel's VBA, you can easily use "Normal" functions and arguments, just like you would in a cell.
Sometimes you don't want to or need to use the Range or Cells objects and want to keep it simple, especially if you will always have the correct sheet selected for this example. Here are two of the syntaxes

answer = [a1 - a2]
[a1] = "Hello World!"
answer = Evaluate("=A1 - A2")
Stop

Fantastic Developer Tools:

[HocExcelOnline]

Thanks for the vid. Just want to share. You can also use [] to declare arrays in VBA:
dim arr() as variant
dim arr2() as variant
var arr = [ { 1, 2, 3, 4, 5 } ]
' or
var arr2 = [ { 1, 2 ; 3, 4 ; 5, 6 } ]

[renzmarlonlim]

Great video! I have 1 question, how can you make the range variable using evaluate? example the number of rows change everytime I use sum formula.

[d.amitai]

Hi there,
Thanks for this video :-)
Can you please point me to the video where you teach more advanced ways to use the EVALUATE function?
Perhaps you show there some examples how to use variable ranges rather than fixed ranges?

[ExcelVbaIsFun]

Merry Christmas everyone. I hope God blesses you richly this upcoming year. Remember, you ARE a success!! Believe it, be it! Blessings my friends!! Dan

[ExcelVbaIsFun]

Your video is uploading now, saleall! Its tips n tricks series number 48.

[ian_senior]

fyi you don't need to include the equals sign (=) in the evaluate function Evaluate("Sum(A:A)") works fine