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:

  1. 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.
  2. eg in cell A1:
  3. =Dec2Base(100,9)
 

Test the code:

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

Sample File:

Dec2Base.zip 7.44KB 

Approved by mdmackillop


This entry has been viewed 92 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express