VBA - Hex Number Thinks Its Scientific

A

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
 
G

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
 
J

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top