#Vba excel convert string to long 32 bit#
#Vba excel convert string to long code#
#Vba excel convert string to long plus#

The Val() function had removed the leading zeros and treated 8080 as a signed 2s complement 16-bit value.

= Val('&H' + variable) had converted this to -32896, an invalid value to pass as a colour property. The string that had caused the problem was '008080'. Having successfully processed tens of thousands of these I was suddeny presented with an 'out of range' error pop-up.

#Vba excel convert string to long code#

I recently encountered this conversion problem in some code that was converting hexadecimal RGB colour values which originated as a 6-character text string in a Word document. Similarly, the 24-bit range is from -8388608 to 8388607, and so on. binary 11111101 = hex FD = decimal (-128 + 125) = -3, 10110100 = hex B4 = decimal (-128 + 52) = -76.Ģs complement applies the same effect at each increasing 8-bit boundary, thus for 16 bits, the sign bit is bit-15 (with a value of -32,768) and the positive value is in bits 0 to 14, giving a 16-bit range of values from -32768 to 32767.

#Vba excel convert string to long plus#

If the sign bit is set, the value of the byte is -128 plus the positive value of whatever is stored in bits 0 to 6. The 2s complement range of values for 8 bits is from -128 (with only bit-7 set) to +127 (with only bits 0 to 6 set). If all 8 bits are set, the byte value becomes (-128 + 127) which gives the negative decimal value of -1. This only leaves the seven bits from 0 to 6 to hold the actual value, giving them a maximum value of 127. However in 2s complement, bit-7 is the 'sign bit'. Therefore, the highest possible value if all bits are set is 255. Normally, the most significant bit is bit-7 at the left which has a value of 128, the least significant bit is bit-0 at the right which has a value of 1. I'll explain it in its simplest form, from first principles, by the way it works on a byte, so that it's understandable even by absolute beginners. When I was an assembly language programmer in the 1970s, working more in binary and octal than anything else, this was a very common issue, known as '2s complement'. I realise that this is a very old question and that there are some ingenious solutions to it, but I'm surprised that nobody has explained what it is that seems to be the root cause of the problem, and hence what might possibly be a one-line solution in many cases.

I found my way here looking for a Word VBA solution, but what I've discovered might also apply to other Office apps. If myHex2Dec < 0 And Len(hexString) = 8 Then If Left(hexString, 2) = '&h' Or Left(hexString, 2) = '&H' Then hexString = Mid(hexString, 3) 'only for positive numbersįunction myHex2Dec(hexString As String) As Variant There seems to be an issue when converting 8-digit Hex numbers because apparently signed 32-bit is used somewhere in the process which results in the sign mistake even though Decimal could handle the number.

#Vba excel convert string to long 32 bit#

Since I only have 32 bit office at the moment, this is for Decimal and CDec. LongLong and CLngLngonly work on 64bit platforms though. If you want to go higher than 2^31 you could use Decimal or LongLong.