Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Hi,
Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Hi Luciano
Check your other post you made. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Luciano wrote:
Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Homework, anyone? Function toBase(ByVal what As Variant, base As Long) As String 'Should be able to handle any decimal number to the limits of a variant, 'and bases from 2 to 36. If (base < 2) Or (base 36) Or (Int(what) < what) Then Exit Function Dim tmp As String 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") While (what < 0) tmp = digits(what Mod base) & tmp what = what \ base Wend toBase = tmp End Function For base 37+ add your extra digits to the end of the array and change 36 to your new max. -- They'd just stopped trusting anything they couldn't see. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Em sexta-feira, 8 de março de 2013 22h22min07s UTC-3, Luciano escreveu:
Hi, Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano Hi Auric, Thank you very much! However, do you have any idea about how coud I convert to any base instead only decimal to any other (e.g. binary to decimal or octal to binary, etc)? Thanks in advance, Luciano |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Luciano wrote:
Em sexta-feira, 8 de março de 2013 22h22min07s UTC-3, Luciano escreveu: Hi, Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano Hi Auric, Thank you very much! However, do you have any idea about how coud I convert to any base instead only decimal to any other (e.g. binary to decimal or octal to binary, etc)? Thanks in advance, Luciano do it twice. from one base to decimal and decimal to the other base. You just need another set of functions which convert any base to decimal. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
thank Auric! for this very useful function
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 isabelle Le 2013-03-09 02:02, Auric__ a écrit : Homework, anyone? Function toBase(ByVal what As Variant, base As Long) As String 'Should be able to handle any decimal number to the limits of a variant, 'and bases from 2 to 36. If (base < 2) Or (base 36) Or (Int(what) < what) Then Exit Function Dim tmp As String 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") While (what < 0) tmp = digits(what Mod base) & tmp what = what \ base Wend toBase = tmp End Function For base 37+ add your extra digits to the end of the array and change 36 to your new max. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
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. -- Ah! Soda monster! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Em sexta-feira, 8 de março de 2013 22h22min07s UTC-3, Luciano escreveu:
Hi, Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano Fantastic Auric!!!! Thank you very much! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Em sexta-feira, 8 de março de 2013 22h22min07s UTC-3, Luciano escreveu:
Hi, Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano I have tested for several number sizes and bases but the unique problem is that in spite of a base like 2 has only two notations (0,1) the function calculate a "possible" corresponding decimal if we type something that is not inside theses possible notations (e.g. if we type 22 in binary to decimal it calculates the number 6). |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Luciano wrote:
I have tested for several number sizes and bases but the unique problem is that in spite of a base like 2 has only two notations (0,1) the function calculate a "possible" corresponding decimal if we type something that is not inside theses possible notations (e.g. if we type 22 in binary to decimal it calculates the number 6). (For others reading, he's referring to my fromBaseToDec function.) You could make sure your inputs have the correct numerals in them before passing them to my function. Or, you can add this line right below the "Error 13" line: If chk base Then Error 9: Exit Function Error 9 is "Subscript out of range". -- You're beginning to make sense. It's scaring me. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
On Fri, 8 Mar 2013 17:22:07 -0800 (PST), Luciano wrote:
Hi, Does somebody have any idea about how to develop some VBA function or macro aiming conversion of several numerals types and that could be used for small and large numbers(e.g. 100000000000 in decimal)? The major numerals base for which I need this conversions a Binary (Base 2) Ternary (Base 3) Quaternary (Base 4) Quinary (Baase 5) Senary (Base 6) Septenary (Base 7) Octal (Base 8) Nonary (Base 9) Decimal (Base 10) Undecimal (Base 11) Duodecimal (Base 12) Base 13 Hexadecimal (Base 16) Vigesimal (Base 20) and others... Thanks in advance, Luciano Unless you want to reinvent the wheel, it's already been done and available as a free Excel add-in. See http://www.thetropicalevents.com/Xnumbers60.htm which does base conversions and also handles more precision than does Excel. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
Ron Rosenfeld wrote:
Unless you want to reinvent the wheel, ....but that's half the fun! ;-) -- No match, though, for the dynamite in his hand. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert numerals
On Sat, 9 Mar 2013 18:42:50 +0000 (UTC), "Auric__" wrote:
Ron Rosenfeld wrote: Unless you want to reinvent the wheel, ...but that's half the fun! ;-) Yes, sometimes it is. |
#14
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. |
Reply |
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 |