XL2000: Conversion to Base x Function Not Available (213361)

SYMPTOMS

Microsoft Excel version 4.0 includes an add-in function named BASE() which accepts input for a base 10 number (decimal number) and a number designating the base to which it should be converted. BASE() outputs a text string representation of the number in that base. For example, BASE() converts the decimal number 10 to the string "1010" if you request base 2.

Microsoft Excel versions 5.0 and later do not include the BASE() function. When you open a workbook in Microsoft Excel version 5.0 or later, you receive a "#NAME?" error in the cells that contain the BASE() function.

WORKAROUND

The following example uses Visual Basic for Applications to create a user-defined function Base(). The functionality of this procedure is similar to that of Base() available with Excel 4.0

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Create a new workbook.
Press Alt+F11 to open the Visual Basic Editor.

Insert a new module and enter the following code:

Function Base(InValue As Double, NewBase As Double) As String
    Dim TestVal As Double, counter As Integer, Alpha As String
    Dim Holder As Integer
    Application.Volatile
    On Error GoTo FixError
    If InValue = 0 Then GoTo FixError
    If NewBase = 0 Then GoTo FixError
    counter = 0
    TestVal = 0
    While TestVal < InValue
        TestVal = NewBase ^ counter
        If TestVal < InValue Then counter = counter + 1
    Wend
    counter = counter - 1
    Alpha = ""
    Holder = 0
    For x = counter To 0 Step -1
        Holder = Int(InValue / (NewBase ^ x))
        Alpha = Alpha & Application.Text(Holder, "0")
        InValue = InValue - (Holder * (NewBase ^ x))
    Next x
    Base = Alpha
    Exit Function
FixError:
    Base = "#Value"
End Function

Close the Visual Basic Editor and return to Sheet1.
Enter the following values, and then press ENTER:
```
   A1: 10
   A2: 2
   A3: =Base(A1,A2)
					
```
Notice that cell A3 displays "1010" as the binary representation of decimal 10.

XL2000: Conversion to Base x Function Not Available (213361)

SYMPTOMS

WORKAROUND

REFERENCES