I am trying to convert Decimal to Hex and found the following codes in Excel
community website. The function works fine except that when I enter decimal
values such as 112 or 160 or 208 nothing happens; there is no hex value
returned. These are the only numbers that I noticed so far, there could be
more!

Any reason for this?

Any help would be greatly appreciated.

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

I did a further check and found out that if the decimal values are 112, 128,
144, 160, 176, 192, 208, 224 and 240 no hex values will be returned.

"Francis Ang" wrote:

> I am trying to convert Decimal to Hex and found the following codes in Excel
> community website. The function works fine except that when I enter decimal
> values such as 112 or 160 or 208 nothing happens; there is no hex value
> returned. These are the only numbers that I noticed so far, there could be
> more!
>
> Any reason for this?
>
> Any help would be greatly appreciated.
>
> 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

On Mar 17, 8:30*am, Francis Ang <Francis...@discussions.microsoft.com>
wrote:
> I did a further check and found out that if the decimal values are 112, 128,
> 144, 160, 176, 192, 208, 224 and 240 no hex values will be returned.
>
>
>
> "Francis Ang" wrote:
> > I am trying to convert Decimal to Hex and found the following codes in Excel
> > community website. *The function works fine except that when I enter decimal
> > values such as 112 or 160 or 208 nothing happens; there is no hex value
> > returned. *These are the only numbers that I noticed so far, there could be
> > more! *
>
> > Any reason for this?
>
> > Any help would be greatly appreciated.
>
> > 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_215474....
> > ' 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- Hide quoted text -
>
> - Show quoted text -

I did not go through the code that you have posted but you can use
this code:

Function DecToHex(ByVal x As Variant) As String

Dim y

x = CDec(x)
Do
y = (x / 16 - Int(x / 16)) * 16
If y < 10 Then
DecToHex = y & DecToHex
Else
DecToHex = Chr$(y + 55) & DecToHex
End If
x = Int(x / 16)
Loop While x
End Function

MsgBox Hex(160)
--
Jim Cone
Portland, Oregon USA




"Francis Ang" <(E-Mail Removed)>
wrote in message
I did a further check and found out that if the decimal values are 112, 128,
144, 160, 176, 192, 208, 224 and 240 no hex values will be returned.

Thanks Dk.

Your solution worked for me. Thank you very much.

"dk" wrote:

> On Mar 17, 8:30 am, Francis Ang <Francis...@discussions.microsoft.com>
> wrote:
> > I did a further check and found out that if the decimal values are 112, 128,
> > 144, 160, 176, 192, 208, 224 and 240 no hex values will be returned.
> >
> >
> >
> > "Francis Ang" wrote:
> > > I am trying to convert Decimal to Hex and found the following codes in Excel
> > > community website. The function works fine except that when I enter decimal
> > > values such as 112 or 160 or 208 nothing happens; there is no hex value
> > > returned. These are the only numbers that I noticed so far, there could be
> > > more!
> >
> > > Any reason for this?
> >
> > > Any help would be greatly appreciated.
> >
> > > 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_215474....
> > > ' 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- Hide quoted text -
> >
> > - Show quoted text -
>
> I did not go through the code that you have posted but you can use
> this code:
>
> Function DecToHex(ByVal x As Variant) As String
>
> Dim y
>
> x = CDec(x)
> Do
> y = (x / 16 - Int(x / 16)) * 16
> If y < 10 Then
> DecToHex = y & DecToHex
> Else
> DecToHex = Chr$(y + 55) & DecToHex
> End If
> x = Int(x / 16)
> Loop While x
> End Function
>

Thanks for the suggestion, Jim.

Initially, I did not know understand your reply. Then it dawned upon me how
to apply of your suggestion to solve my problem.

Thank you, Jim.

"Jim Cone" wrote:

>
> MsgBox Hex(160)
> --
> Jim Cone
> Portland, Oregon USA
>
>
>
>
> "Francis Ang" <(E-Mail Removed)>
> wrote in message
> I did a further check and found out that if the decimal values are 112, 128,
> 144, 160, 176, 192, 208, 224 and 240 no hex values will be returned.
>
>
>

There is a DEC2HEX function in xl2007
http://msdn.microsoft.com/en-us/library/bb239275.aspx

and in the analysis toolpak addin for xl2003
http://office.microsoft.com/en-us/ex...090541033.aspx

--

Regards,
Nigel
(E-Mail Removed)



"Francis Ang" <(E-Mail Removed)> wrote in message
news:68B9B005-3FFF-49E5-89D5-(E-Mail Removed)...
>I am trying to convert Decimal to Hex and found the following codes in
>Excel
> community website. The function works fine except that when I enter
> decimal
> values such as 112 or 160 or 208 nothing happens; there is no hex value
> returned. These are the only numbers that I noticed so far, there could
> be
> more!
>
> Any reason for this?
>
> Any help would be greatly appreciated.
>
> 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