What is the "--" operator used for? 1

[rudejohn]

Can anyone clue me in as to why some VBA code uses "--" before a formula? Example:

num = Evaluate("=SUMPRODUCT(--(B2:B5=H4"),--(C2:C4="))

~I'm splicing code pieces together so don't worry if the ranges/parentheses don't quite match up

Thanks in advance!


************
RudeJohn
************

 

[xlbo]

coerces textual numbers to be true values - similar to using the VALUE() function

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

... or, in this case, boolean values to numbers.

All it needs to force the conversion (or 'coercion') to numeric data is a single arithmetic operator - the double negative equates to a non-operation so only the data type is changed and not the value.

Another common operation which has the same effect is multiplying by 1. There are some other variations on the theme and, from time to time I have seen debates (though never agreement) in the Excel community about which is the most efficient method.

Of course the reason for doing it is that the SUMPRODUCT function needs arrays of numeric data to work on.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[rudejohn]

Thanks to both of you.

************
RudeJohn
************