VBA Express : Excel - Decimal base conversion function

Excel

Decimal base conversion function

Ease of Use

Easy

Version tested with

2003

Submitted by:

RichardSchollar

Description:

Provides a VBA function to convert a decimal integer value to another base in the range base 2 to base 16. The function returns a string.

Discussion:

Extends the functionality provided by the Analysis Toolpak Dec2*** functions to cater for all other bases between 2 and 16. Could be useful if you need to know what the value would be in other bases eg for teachers setting math tests. It also provides a greater range of decimal values that can be converted (eg Analysis Toolpak limit on Dec2Bin is 512 decimal - with this function it is only limited by the size of a Long Integer decimal - ie 2,147,483,647).

Code:

instructions for use

			Option Explicit 
Sub tester() 
     'testing sub - this code is NOT required to run the function - it only
     'demonstrates the function in operation
    Dim s As String 
    s = "Decimal number 255 in..." & vbCr & "Binary is " & Dec2Base(255, 2) & vbCr & _ 
    "Octal is " & Dec2Base(255, 8) & vbCr & _ 
    "Base 13 is " & Dec2Base(255, 13) & vbCr & _ 
    "Hexadecimal is " & Dec2Base(255, 16) 
    MsgBox s 
End Sub 
 
 
Public Function Dec2Base(Num As Long, base As Long) As String 
     'converts a decimal number to the equivalent in the specified base
     '(base 2 to base 16).  Base needs to be specified as decimal ie
     '8 for base 8, 16 for base 16, 2 for base 2 etc
    Static Digits As Variant 
    Dim i As Long, alHolder() As Long, sTemp As String 
     
    If IsEmpty(Digits) Then _ 
    Digits = VBA.Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F") 
     
     'check valid base:
    If base > 16 Then Dec2Base = "Invalid base used": Exit Function 
     'fill holder array:
    i = 0 
    Do 
        ReDim Preserve alHolder(0 To i) 
        alHolder(i) = Num Mod base 
        i = i + 1 
        Num = Num \ base 
    Loop While Num > 0 
     
     'build string result in base:
    sTemp = "" 
    For i = i - 1 To 0 Step -1 
        sTemp = sTemp & Digits(alHolder(i)) 
    Next i 
     'output:
    Dec2Base = sTemp 
End Function

How to use:

Copy the code to a standard module in a workbook. The function can then be called by other procedures within that workbook or used directly in the worksheets of the workbook.
eg in cell A1:
=Dec2Base(100,9)

Test the code:

Copy the code in and run the 'tester' sub.

Sample File:

Dec2Base.zip 7.44KB

Approved by lucas

This entry has been viewed 47 times.