"The definition of insanity is doing the same thing over and over and expecting different results." - (Benjamin Franklin)

The EVALUATE method

(Excerpt from "Microsoft Excel/Visual Basic Reference" - ISBN 1-55615-920-X")

Applies TO Application Object, Chart Object, DialogSheet Object, Worksheet Object.

Description Converts a Microsoft Excel name to an object or to a value.

Syntax :
object.**Evaluate(name)**

object: Optional for **Application**,
required for **Chart**, **DialogSheet**, and **Worksheet : **
Contains the named object.

name: Required. The name of the object, using the naming convention of Excel.

** The following names in Excel may be used with this method:**

**A1-style references.** Any reference to a single cell using A1 notation.
All references are considered to be absolute references.

**Ranges.** You may use the range, intersect, and union operators (colon,
space and comma) with references.

**Defined names.** in the language of the macro.

**External references.** using the ! Operator. These references could be to
a cell or a name defined in another workbook,

**For example,
EVALUATE("[BOOK1.XLS]Sheet1! 1")**

**Grahic objects.** using their Excel name ("
Oval 3", for example). You cannot use the number alone.

For example, the following expression pairs are equivalent :

[A1].Value = 25

trigVariable = [SIN(45)]

trigVariable = Evaluate("SIN(45)")

Set FirstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).[A1]

Set FirstCellInSheet = Workbooks("BOOK1.XLS").Sheets(4).Evaluate("A1")

The advantage of using square brackets is that it is shorter

The advantage of using Evaluate is that the argument is a string, so you can construct the string in your code or use a Visual Basic variable.

Example :

Worksheets("Sheet1").Activate

boldCell = "A1"

Application.Evaluate(boldCell).Font.Bold = True

Used with SUMPRODUCT Function in Excel

**SUMPRODUCT** is the most important, useful and powerful function in Excel.

**AWSOME comment from José Luis Sallent Sanchez**

" After reading your lines (about SumProduct) I wondered if we could use this powerful formula in VBA. It took me a while but for the ones interested it can be used with another powerful tool: Evaluate, which will allow us to use any Excel formula in VBA. The two items combined can save a lot of lines of code."

**EVALUATE** is the key to using
ANY Excel formula in VBA so, if you need to write some
SOMEPRODUCT formula in your VBA code, you will use
EVALUATE !!

**... And remember : EVALUATE(something) may be written [something]**

**... And also : instead of coding "Range("A1") = something",**

**You may easily code [A1] = something **

**which is by far more quickier !!**

Instead of coding :

Range("L43") = "=ROUND(SUMPRODUCT(($A$2:$A$865= $Q17)*$F$2:$F$865),2)"

**we will code :**

[L43] = [ROUND(SUMPRODUCT(($A$2:$A$865=$Q17)*$F $2:$F$865),2)]

BUT NOTE : In the 1st case, the L43 contains a FORMULA, while in the 2nd case, L43 contains a RESULT !!!!!

**For those interested in a deeper explanation, see http://www.ozgrid.com/forum/showthread.php?t=52372**

**Remember : EVALUATE is for use in VBA Code !!!**