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