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