use of evaluate and exact preceded with a -- in vba

[monoj chakraborty]

while trying to solve a issue to merge cells, i came across a segment code as follows :

[rCount = Evaluate("=SUMPRODUCT((--EXACT(""" & cel.Value & """, GSV!$A$6:$A$75)))")
]

while the code seems to work with the above, I am unable to understand the logic and also what the various functions , like evaluate, exact, "--", are doing here.

If someone can help me understand the logic of the above formula, then I can extend the logic, in other programmes

Thans

 

[GraH - Guido]

while trying to solve a issue to merge cells, i came across a segment code as follows :

[rCount = Evaluate("=SUMPRODUCT((--EXACT(""" & cel.Value & """, GSV!$A$6:$A$75)))")
]

while the code seems to work with the above, I am unable to understand the logic and also what the various functions , like evaluate, exact, "--", are doing here.

If someone can help me understand the logic of the above formula, then I can extend the logic, in other programmes

Thans

Hi, by code you mean VBA I guess?
For so far I know "Evaluate" is not an excel function. It seems to be a VBA function, but I don't know it. A quick google search allowed me to find this explanation though (ozgrid forum)
"The VBA help topic for EVALUATE is a bit vague and probably doesn't do it justice. In fact, after reading through it for yourself, you probably wouldn't see much cause to use it. Here's what it can do:
1. Converts string math expressions to values.
2. Converts 1D and 2D string arrays to their array equivalents.
3. Capable of processing any formula a worksheet cell can process! (wow, WOW)"
"--" is a double negation. The first "-" will turn true and false to the value -1 and 0 the second "-" turns the values positive, hence -1 becomes 1.
Exact verifies whether a string matches with another string and returns TRUE or FALSE. With the conversion "--" SUMPRODUCT can do a sum of the evaluated range. So rCount is the number of matched strings.
Evaluate in your case is option 3: allowing to perform the formula.

 

[Marc L]

Hi !

As Evaluate is the more powerful VBA function,
it can avoid some gas factory code !

A sample in the thread
How to increse column value by 1 as per count condition in VBA …
​

 

[NARAYANK991]

Hi ,

The VBA method Evaluate does exactly what its English meaning implies ; it evaluates an expression ; the expression itself is an Excel worksheet formula.

The following links explain in more detail :

http://analystcave.com/excel-vba-evaluate-tips-and-tricks-to-use-application-evaluate/

http://www.thesmallman.com/using-evaluate-in-vba/

The worksheet function EXACT does a comparison between two values ; the Excel help on this function is adequate enough ; in case you need more detail , see this link :

https://www.exceltip.com/excel-text-formulas/excel-exact-function.html

Narayan