Setting colors in Hex and Decimal behaving differently

Question

I am trying to set an orangish color in the following manner:

WorkSheet.Range("A1:A5").Interior.color = 49407

and

WorkSheet.Range("A1:A5").Interior.color = &HC0FF 'Hex value of 49407

Aren't these two supposed to be exactly equivalent? The colors being set are different.

Answer 1

I believe the answers from Gimp and Johannes both miss the key issue.

A colour on a computer screen is defined by specifying how much red, blue and green you require. For each of the three colours you specify a number between 0 and 255. These three numbers are normally specified as a single number by concatenating the three separate numbers. With Html you specify a colour as #RRGGBB where RR, GG and BB are hexadecimal numbers or you can replace RRBBGG by the decimal equivalent. With Excel the sequence is reversed so &HBBGGRR or the decimal equivalent.

49407 is the decimal equivalent of 00C0FF which for Excel means Blue = 0, Green = 192 and Red = 255.

But &HC0FF or &H00C0FF is -16129 or Blue = 255, Green = 192 and Red = 255. This seems to be a flaw in the &H conversion. I cannot find anyway of getting C0FF converted to 49407.

If you insist on using the &H conversion, the best I can offer is:

 Range("A1:A5").Interior.color = &H01C0FF

This is Blue = 1, Green = 192 and Red = 255 and I cannot tell the difference from Blue = 0, Green = 192 and Red = 255.

But I would recommend:

Range("A1:A5").Interior.color = RGB(255,192,0)

because the RGB function always returns a positive number and you do not have to worry about Excel's reverse sequence.

Answer 2

No, those values are not equivalent: &HC0FF is -16129, whereas &HC0FF& is 49407.

VBA, being a rather old language uses 16 bit integers by default. In this day and age, you pretty much want longs always instead of ints, but you have to ask VBA for them.

&HC0FF is a construct that defines a 16-bit constant from hex. Because the sign bit in this value is set (on, negative) when interpreted as a 16-bit value, hence the conversion to -16129. We may consider that this sucks, but it is not a bug! When you use -16129 (as a signed integer), in 32 bit form, 1's are propagated thru the whole top 16 bits and that results in the blue value of 255.

What you really wanted here is a a 32-bit hex constant: &HC0FF&. The extra & on the end tells VBA that this is a long constant instead of an int. Interpreted in 32-bits, this is a positive value, so gives the decimal equivalent you're looking for.

In short, ask for long hex constants with the trailing &.

As an aside, this propensity of VBA toward 16 bit can also bite us when using decimal constants, such as in an expression 16000 * 16000, which will overflow 16 bit arithmetic. So, sometimes one needs to use the trailing & on decimal constants too (or assign one to a long first).