Microsoft KB Archive/213361

= XL2000: Conversion to Base x Function Not Available =

Article ID: 213361

Article Last Modified on 11/23/2006

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q213361





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:

https://partner.microsoft.com/global/30000104

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. </ol>

<div class="references_section">