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 !

--
Regards

Robin
Robin_B DOT Clay AT virgin DOT net

Can you provide some examples?

"Mark Lincoln" wrote:

Can you provide some examples?

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

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 !

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/Appl..._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/Appl..._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 ]

"fanpages" wrote:

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.

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

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 ?

--
Regards

Robin

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

--
Regards,
Tom Ogilvy


"Robin Clay" <Robin_B DOT Clay AT virgin.net wrote in message
...
"fanpages" wrote:

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.

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

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 ?

--
Regards

Robin

"Tom Ogilvy" wrote:

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

Absolutely !

Why keep a dog and bark yourself?

Demo'd from the immediate window

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

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

--
Regards

Robin

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.

--
Regards,
Tom Ogilvy


"Robin Clay" <Robin_B DOT Clay AT virgin.net wrote in message
...
"Tom Ogilvy" wrote:

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

Absolutely !

Why keep a dog and bark yourself?

Demo'd from the immediate window

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

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

--
Regards

Robin

Robin,
Does this help ?

http://support.microsoft.com/default...b;en-us;121730

NickHK

"Robin Clay" <Robin_B DOT Clay AT virgin.net wrote in message
...
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 !

--
Regards

Robin
Robin_B DOT Clay AT virgin DOT net

"NickHK" wrote:

Does this help ?
http://support.microsoft.com/default...b;en-us;121730

Alas! No.
That deals with the Add-Ins not having been loaded - mine are, as evidenced
by all the other functions working properly.

Thanks for responding, though - it might have worked....

"Robin Clay" <Robin_B DOT Clay AT virgin.net wrote in message
...
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 !

--
Regards

Robin
Robin_B DOT Clay AT virgin DOT net



--
Regards

Robin

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.

"Mark Lincoln" wrote:
You might want to try deleting and reloading them.

Thanks - I did try that, but perhaps if I close Excel in between it might
work...

--
Regards

Robin

Robin

Did you ever find a reply on this ? I have a similar issue now in Excel 2007.

"Robin Clay" wrote:

"Mark Lincoln" wrote:
You might want to try deleting and reloading them.

Thanks - I did try that, but perhaps if I close Excel in between it might
work...

--
Regards

Robin