Hex2Dec and Dec2Hex don't work

G

Guest

Greetings !

I am running Excel 2002 (10.6501.6753) SP3
under Windows XP Home

I have both the "Analysis Toolpack" and
the "Analysis Toolpack VBA" loaded,
i.e. atpvean.xla and funcres.xla.

Hex2Bin, Bin2Dec and Dec2Bin all work fine.

But when I try to use either Hex2Dec or Dec2Hex
(the ones I really want to use, of course!),
they don't work.

HELP !
 
F

fanpages

Hi Robin,
I am running Excel 2002 (10.6501.6753) SP3
under Windows XP Home

I have both the "Analysis Toolpack" and
the "Analysis Toolpack VBA" loaded,
i.e. atpvean.xla and funcres.xla.

Hex2Bin, Bin2Dec and Dec2Bin all work fine.

But when I try to use either Hex2Dec or Dec2Hex
(the ones I really want to use, of course!),
they don't work.

HELP !
Click to expand...

Taken from a question I responded to at Experts-Exchange:
[ http://www.experts-exchange.com/Q_21601357.html ]

Here's some VBA to replace the Analysis Toolpak functions...

Option Explicit
Public Function Hex_To_Decimal(ByVal vntHex_Value As Variant) As Variant

'
----------------------------------------------------------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
'
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21547447.html
' Creating Column that does hexidecimal math
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 1 September 2005
'
----------------------------------------------------------------------------

Dim intLoop As Integer
Dim vntMultiplier As Variant
Dim vntReturn As Variant

On Error GoTo Err_Hex_To_Decimal

vntMultiplier = CDec(1)

For intLoop = Len(vntHex_Value) To 1 Step -1
vntReturn = vntReturn + CDec(vntMultiplier) * CDec("&H" &
Mid$(vntHex_Value, intLoop, 1))
vntMultiplier = CDec(vntMultiplier * 16)
Next intLoop

Exit_Hex_To_Decimal:

On Error Resume Next

Hex_To_Decimal = vntReturn

Exit Function

Err_Hex_To_Decimal:

On Error Resume Next

vntReturn = CDec(0)

Resume Exit_Hex_To_Decimal

End Function
Public Function Decimal_To_Hex(ByVal vntDecimal_Value As Variant) As String

'
----------------------------------------------------------------------------
' Experts Exchange Question:
' Home \ All Topics \ Applications \ MS Office \ Excel
'
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21547447.html
' Creating Column that does hexidecimal math
'
' Copyright (c) 2005 Clearlogic Concepts (UK) Limited
' N.Lee [ http://NigelLee.info ] - 1 September 2005
'
----------------------------------------------------------------------------

Dim intHex_Value As Integer
Dim intLoop As Integer
Dim vntDec_Value As Variant

Dim strReturn As String

On Error GoTo Err_Decimal_To_Hex

If Len(vntDecimal_Value) > 14 Then
strReturn = "* ERROR *"
Else
strReturn = ""
vntDec_Value = CDec(vntDecimal_Value)

For intLoop = Len(vntDecimal_Value) - 1 To 0 Step -1
intHex_Value = Int(vntDec_Value / (16 ^ intLoop))
vntDec_Value = vntDec_Value - (intHex_Value * (16 ^ intLoop))
strReturn = strReturn & Hex(intHex_Value)
Next intLoop
End If

Exit_Decimal_To_Hex:

On Error Resume Next

If Left$(strReturn, 1) = "0" Then
strReturn = StrReverse(strReturn)
strReturn = StrReverse(Left$(strReturn, InStr(strReturn, "0") - 1))
End If

Decimal_To_Hex = strReturn

Exit Function

Err_Decimal_To_Hex:

On Error Resume Next

strReturn = "* ERROR *"

Resume Exit_Decimal_To_Hex

End Function


BFN,

fp.
[ http://www.experts-exchange.com/M_258171.html ]
 
G

Guest

Here's some VBA to replace the Analysis Toolpak functions...
Click to expand...

Thank you very much indeed for that !

I'll give it a go.

But I am still curious as to WHY those Built-In functions don't work ?
 
G

Guest

Mark Lincoln said:
Can you provide some examples?
Click to expand...

Well....

When I found that they didn't seem to work,
I pasted in all the examples given in the HELP file - and those two STILL
didn't work !

From my spreadsheet:-

DEC2HEX(number,places)
Number is the decimal integer you want to convert. If number is negative,
places is ignored and DEC2HEX returns a 10-character (40-bit) hexadecimal
number in which the most significant bit is the sign bit. The remaining 39
bits are magnitude bits. Negative numbers are represented using
two's-complement notation.
Places is the number of characters to use. If places is omitted, DEC2HEX
uses the minimum number of characters necessary. Places is useful for padding
the return value with leading 0s (zeros).
#NAME? Converts decimal 100 to hexadecimal with 4 characters (0064)
#NAME? Converts decimal -54 to hexadecimal (FFFFFFFFCA)

Those two cells returning #NAME? contain:
=DEC2HEX(100, 4)
=DEC2HEX(-54)



HEX2DEC(number)
Number is the hexadecimal number you want to convert. Number cannot
contain more than 10 characters (40 bits). The most significant bit of number
is the sign bit. The remaining 39 bits are magnitude bits. Negative numbers
are represented using two's-complement notation.
#NAME? Converts hexadecimal A5 to decimal (165)
#NAME? Converts hexadecimal FFFFFFFF5B to decimal (-165)
#NAME? Converts hexadecimal 3DA408B9 to decimal (1034160313)

Those three cells returning #NAME? contain:
=HEX2DEC("A5")
=HEX2DEC("FFFFFFFF5B")
=HEX2DEC("3DA408B9")


HEX2BIN(number,places)
Number is the hexadecimal number you want to convert. Number cannot
contain more than 10 characters. The most significant bit of number is the
sign bit (40th bit from the right). The remaining 9 bits are magnitude bits.
Negative numbers are represented using two's-complement notation.
Places is the number of characters to use. If places is omitted, HEX2BIN
uses the minimum number of characters necessary. Places is useful for padding
the return value with leading 0s (zeros).
00001111 Converts hexadecimal F to binary, with 8 characters (00001111)
10110111 Converts hexadecimal B7 to binary (10110111)
1111111111 Converts hexadecimal FFFFFFFFFF to binary (1111111111)

Those three cells contain:
=HEX2BIN("F", 8)
=HEX2BIN("B7")
=HEX2BIN("FFFFFFFFFF")



Regards

Robin Clay
Robin_B DOT Clay AT virgin DOT net
In Dorset's Blackmore Vale
 
T

Tom Ogilvy

If your going to use VBA, you might as well let VBA do the work:

Demo'd from the immediate window

? clng("&HFFFFF")
1048575
? hex(1048575)
FFFFF
 
G

Guest

Tom Ogilvy said:
If your going to use VBA, you might as well let VBA do the work:
Click to expand...

Absolutely !

Why keep a dog and bark yourself?
Demo'd from the immediate window

? clng("&HFFFFF")
1048575
? hex(1048575)
FFFFF
Click to expand...

Thanks, Tom - those work on my comuter.

But....

WHY do the HEX2DEC and DEC2HEX functions not work in the spreadsheet?

Oh, and WHY do the Help files not tell me about "&H", I wonder....
 
M

Mark Lincoln

You might want to try deleting and reloading them. It would be strange
if that actually worked, but I've seen stranger things when it comes to
software.
 
T

Tom Ogilvy

If I look at the HEX function in Excel VBA help, at the bottom it says:

You can represent hexadecimal numbers directly by preceding numbers in the
proper range with &H. For example, &H10 represents decimal 16 in hexadecimal
notation.

That was in xl97.

as to the analysis toolpak functions, I have never had any problem with
them. Perhaps unselecting the two Addins in Tools=>Addins. Then close
excel. Then open and select them again. Also make sure you don't have any
other addin loaded that might have duplicate functions in them - also that
you haven't created any functions in VBA with the same names.
 

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

Similar Threads

DEC2HEX with VBA in Excel 2007 5
invoking add-in function from another add-in or VBA module 2
Excel VBA help - Range selection 5
Solver with VBA 1
FUNCRES.XLA file lost 5
RAND() and RANDBETWEEN() not working - Returning values outside defined range 5
EOMONTH error 2
Excel 2007 Beta 2: COM AddIn don't work 2

Top