VBA - Hex Number Thinks Its Scientific

[ajocius]

Group,
I use the following to convert a decimal summation into a hex
checksum. Only problem is when I report a number back like 7E15, excel
thinks its scientific notation and reports back 7000000000000000.
Decimal CheckSum = 32277, convert to Hex = 7E15, DisplayCheckSum reads
back 7000000000000000. How do I ensure a hex number instead of
scientific notation?

DisplayCheckSum = Right(Hex(CheckSum), 4)

Tony

 

[Gary''s Student]

I reproduced you problem and have a solution:

Sub Macro1()
Dim i As Long
i = 32277
displayCheckSum = Right(Hex(i), 4)
MsgBox (i)
MsgBox (displayCheckSum)
Cells(1, 1).Value = displayCheckSum
End Sub

If you run this and A1 is formatted General, then
7000000000000000 appears in the formula bar

But if you first format A1 as Text and run it then
7E15 appears in the formula bar

 

[Jim Rech]

If the problem is entering the string in a cell you have to either preceed
it with an apostophe or set the cell's number format to text before entering
the string:

Sub a()
ActiveCell.Value = "'" & "7E15"
End Sub

Sub aa()
With ActiveCell
.NumberFormat = "@"
.Value = "7E15"
End With
End Sub

 

[ajocius]

Gary and Jim,
Thank you for your assistance.
Tony

 

[RB Smissaert]

Found that making the cell number format text and then entering the number
has unpredictable results when it is a
a number like this: 0123456
Sometimes it works (displayed as above) and sometimes it doesn't (leading
zero stripped off).
The only sure way to handle this unfortunately seems to add a leading single
quote.

RBS