## EVALUATE with SUMPRODUCT

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

** Remarks** 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!A1")

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

**NOTE **Using square brackets (for example , "[A1:C5]") is identical to calling the Evaluate method with a string argument.

For example, the following expression pairs are equivalent :

`[A1].Value = 25`

`Evaluate("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** This example turns on bold formatting in cell A1 on Sheet 1.

`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 writen [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 !!!!!**

**Download the main example from ****http://www.affordsol.be/Evaluate & SumProduct.xlsm ****To see how to use the Evaluate command in VBA : in a worksheet and in a Userform !**

**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 !!!**

Previous page: SUMPRODUCT function

Next page: Worksheet Circle(s) Method