About Us

witek

Auric__ wrote:
isabelle wrote:

thank Auric! for this very useful function

Np.

ps /
to prevent overtaking 2147483647, i added this line
If what 2147483647 Then toBase = 0: Exit Function
but if you have another solution, i'm interested

Damn, look at that, overflow. I didn't test very thoroughly, I guess. The
whole reason I made "what" a Variant was so you could pass large values; it
never occurred to me to actually *try* it. Sigh.

Here's a version with "what" as a Long. Instead of getting an overflow
*inside* my function, passing large values will overflow at the calling
line instead.

Function toBase(ByVal what As Long, base As Long) As String
'Should be able to handle any whole number to the limits of a Long,
'and bases from 2 to 36.
If (base < 2) Or (base 36) Then Exit Function
Dim tmp As String, sign As Boolean
Static digits As Variant
If IsEmpty(digits) Then digits = Array("0", "1", "2", "3", "4", "5", _
"6", "7", "8", "9", "A", "B", _
"C", "D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M", "N", _
"O", "P", "Q", "R", "S", "T", _
"U", "V", "W", "X", "Y", "Z")
sign = (what < 0)
what = Abs(what)
While (what < 0)
tmp = digits(what Mod base) & tmp
what = what \ base
Wend
If sign Then tmp = "-" & tmp
toBase = tmp
End Function

...and by request from the OP, here's the reciprocal function. Note that if
you pass any characters that aren't in "digits" I give you error 13 ("Type
Mismatch").

Function fromBaseToDec(ByVal what As String, base As Long) As Variant
'Should be able to handle any whole number to the limits of a Variant,
'and bases from 2 to 36.
If (base < 2) Or (base 36) Then Exit Function
Dim tmp As Variant, L0 As Long, sign As Boolean, digits As String
Dim chk As Long
digits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
sign = ("-" = Left$(what, 1))
If sign Then what = Mid$(what, 2)
what = UCase$(what)
For L0 = 1 To Len(what)
chk = InStr(digits, Mid$(what, L0, 1))
If chk < 1 Then Error 13: Exit Function
tmp = tmp + ((chk - 1) * (base ^ (Len(what) - L0)))
Next
If sign Then tmp = 0 - tmp
fromBaseToDec = tmp
End Function

This one I *did* test with large numbers. This:

fromBaseToDec("111111111111111", 11)

...returns 417724816941565, well beyond the limits of a Long.

return result as a string.

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
CONVERT ROMAN TO ARABIC NUMERALS	JOHN	Excel Worksheet Functions	7	April 14th 10 12:09 PM
How do I convert Roman numerals to Arabic (reverse of ROMAN)?	IanW[_2_]	Excel Worksheet Functions	5	May 4th 07 12:50 PM
Convert numbers (numerals) to words (text)	Yard Sale	Excel Discussion (Misc queries)	3	July 13th 06 08:38 AM
Convert numbers (numerals) to words (text)	Yard Sale[_2_]	Excel Programming	1	July 6th 06 11:56 AM
Possible to have sheet to convert roman numerals?	StargateFan[_3_]	Excel Programming	3	September 11th 05 02:05 PM

Menu

About Us