VBA - Hex Number Thinks Its Scientific
 

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


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=488405

VBA - Hex Number Thinks Its Scientific
 

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
--
Gary's Student


"ajocius" wrote:


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


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=488405

VBA - Hex Number Thinks Its Scientific
 

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


--
Jim
"ajocius" wrote in
message ...

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


--
ajocius
------------------------------------------------------------------------
ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=488405

VBA - Hex Number Thinks Its Scientific
 

Gary and Jim,
Thank you for your assistance.
Tony


--
ajocius
------------------------------------------------------------------------
ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
View this thread: http://www.excelforum.com/showthread...hreadid=488405

VBA - Hex Number Thinks Its Scientific
 

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


"Jim Rech" wrote in message
...
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


--
Jim
"ajocius" wrote in
message ...

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


--
ajocius
------------------------------------------------------------------------
ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
View this thread:
http://www.excelforum.com/showthread...hreadid=488405