I want to know if it would be possible to have excel be able to work out
the binary codes and convert them to different forms.
For example, me being able to give excel a base 10 code and it works it
to base 2. or base 2 to base 16 or base 10 to base 16, etc.
Any help Please
Thanks a lot,
Anthony
Excel has some functions in the analysis toolpak to convert between binary,
octal, decimal and hexadecimal. Look up binary in HELP for further details.
There will also be information about how to install the ATP (which comes with
Excel).
I have also written a UDF that has a greater range than the ATP functions. It
can convert positive numbers between any base in the range 2-64; and also
handles decimals.
To use this, first open the Visual Basic Editor <alt-F11>. Ensure your current
project is highlighted in the Project Explorer Window. Then: Insert/Module
and copy in the code below into the window that opens.
To use the UDF, enter a formula in the form:
=BaseConvert(Number, BaseFrom, BaseTo, [number of Decimals]) The last argument
is optional. If omitted, the output will be an integer. The arguments can be
either entered directly, or cell references can be used.
=========================
Function BaseConvert(num, FromBase As Integer, _
ToBase As Integer, Optional DecPlace As Long) _
As String
Dim LDI As Integer 'Leading Digit Index
Dim i As Integer, j As Integer
Dim Temp, Temp2
Dim Digits()
Dim r
On Error GoTo HANDLER
If FromBase > 62 Or ToBase > 62 _
Or FromBase < 2 Or ToBase < 2 Then
BaseConvert = "Base out of range"
Exit Function
End If
If InStr(1, num, "E") And FromBase = 10 Then
num = CDec(num)
End If
'Convert to Base 10
LDI = InStr(1, num, ".") - 2
If LDI = -2 Then LDI = Len(num) - 1
j = LDI
Temp = Replace(num, ".", "")
For i = 1 To Len(Temp)
Temp2 = Mid(Temp, i, 1)
Select Case Temp2
Case "A" To "Z"
Temp2 = Asc(Temp2) - 55
Case "a" To "z"
Temp2 = Asc(Temp2) - 61
End Select
If Temp2 >= FromBase Then
BaseConvert = "Invalid Digit"
Exit Function
End If
r = CDec(r + Temp2 * FromBase ^ j)
j = j - 1
Next i
LDI = Fix(CDec(Log(r) / Log(ToBase)))
If r < 1 Then LDI = 0
ReDim Digits(LDI)
For i = UBound(Digits) To 0 Step -1
Digits(i) = Format(Fix(r / ToBase ^ i))
r = CDbl(r - Digits(i) * ToBase ^ i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
Temp = StrReverse(Join(Digits, "")) 'Integer portion
ReDim Digits(DecPlace)
If r <> 0 Then
Digits(0) = "."
For i = 1 To UBound(Digits)
Digits(i) = Format(Fix(r / ToBase ^ -i))
r = CDec(r - Digits(i) * ToBase ^ -i)
Select Case Digits(i)
Case 10 To 35
Digits(i) = Chr(Digits(i) + 55)
Case 36 To 62
Digits(i) = Chr(Digits(i) + 61)
End Select
Next i
End If
BaseConvert = Temp & Join(Digits, "")
Exit Function
HANDLER: MsgBox ("Error: " & Err.Number & " " & Err.Description & vbLf & _
"Number being converted: " & num)
End Function
========================
--ron
