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
--
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
>
>

[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


--
Jim
"ajocius" <ajocius.1z4hna_1133040001.989@excelforum-nospam.com> wrote in
message news:ajocius.1z4hna_1133040001.989@excelforum-nospam.com...
>
> 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
>

[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


"Jim Rech" <jrrech@hotmail.com> wrote in message
news:eSZRGPt8FHA.3804@TK2MSFTNGP14.phx.gbl...
> 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" <ajocius.1z4hna_1133040001.989@excelforum-nospam.com> wrote in
> message news:ajocius.1z4hna_1133040001.989@excelforum-nospam.com...
>>
>> 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
>>
>
>