VBA Express : Excel - Convert any Decimal Number from 1 to 1.79769313486231E308 to Hex

Excel

Convert any Decimal Number from 1 to 1.79769313486231E308 to Hex

Ease of Use

Intermediate

Version tested with

2002, 2003

Submitted by:

Jacob Hilderbrand

Description:

This User Defined Function will convert a Decimal number to Hex.

Discussion:

Excel's DEC2HEX Worksheet Function will convert Decimal numbers to Hex, but only up to 5x10^11. It cannot convert larger numbers. If you need to convert a number larger than 5x10^11 to Hex, then this macro is what you need. Refer to the KB Entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=307) to reverse this process.

Code:

instructions for use

			Option Explicit 
 
Public Function DecToHex(Dec As Double) As String 
     
    Dim i               As Long 
    Dim n               As Long 
    Dim PlaceValHex     As Long 
    Dim Hex(1 To 256)   As String 
    Dim HexTemp         As String 
    Dim Divisor         As Long 
     
    Dec = Int(Dec) 
     
    For i = 256 To 2 Step -1 
        If Dec >= 16 ^ (i - 1) And Dec > 15 Then 
            PlaceValHex = Int(Dec / (16 ^ (i - 1))) 
            Dec = Dec - (16 ^ (i - 1)) * PlaceValHex 
            Select Case PlaceValHex 
            Case 0 To 9 
                Hex(i) = CStr(PlaceValHex) 
            Case Is = 10 
                Hex(i) = "A" 
            Case Is = 11 
                Hex(i) = "B" 
            Case Is = 12 
                Hex(i) = "C" 
            Case Is = 13 
                Hex(i) = "D" 
            Case Is = 14 
                Hex(i) = "E" 
            Case Is = 15 
                Hex(i) = "F" 
            End Select 
        Else 
            Hex(i) = "0" 
        End If 
    Next i 
    PlaceValHex = Dec 
    Select Case PlaceValHex 
    Case 0 To 9 
        Hex(1) = CStr(PlaceValHex) 
    Case Is = 10 
        Hex(1) = "A" 
    Case Is = 11 
        Hex(1) = "B" 
    Case Is = 12 
        Hex(1) = "C" 
    Case Is = 13 
        Hex(1) = "D" 
    Case Is = 14 
        Hex(1) = "E" 
    Case Is = 15 
        Hex(1) = "F" 
    End Select 
    For i = 256 To 1 Step -1 
        If Hex(i) = "0" Then 
        Else 
            n = i 
            Exit For 
        End If 
    Next i 
    For i = n To 1 Step -1 
        HexTemp = HexTemp & Hex(i) 
    Next i 
    DecToHex = HexTemp 
     
End Function

How to use:

Open Excel.
Alt + F11 to open the VBE.
Insert | Module.
Paste the code from above in the Code Window that opens up.
Close the VBE (Alt + Q or press the X in the top right corner).

Test the code:

Option 1: In a worksheet cell input = DecToHex(#).
Option 2: In a VBA macro input MyNum = DecToHex(#).
Just replace # with your number.

Sample File:

DecToHex.ZIP 8.17KB

Approved by mdmackillop

This entry has been viewed 103 times.