| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
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 |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CONVERT ROMAN TO ARABIC NUMERALS | Excel Worksheet Functions | |||
| How do I convert Roman numerals to Arabic (reverse of ROMAN)? | Excel Worksheet Functions | |||
| Convert numbers (numerals) to words (text) | Excel Discussion (Misc queries) | |||
| Convert numbers (numerals) to words (text) | Excel Programming | |||
| Possible to have sheet to convert roman numerals? | Excel Programming | |||
Threaded Mode
