Convert a (Possibly) Very Large Positive Decimal Number to Any Base (Up To 36)

[Rick Rothstein]

Back in my "Convert a Number in Any Base (Up To 36) to a (Very Large) Decimal Number (http://www.excelfox.com/forum/f22/convert-number-any-base-up-36-very-large-decimal-number-456/)" article, I posted a function that would convert a number in any base to a decimal number where that number could contain up to 29 decimal digits... the following function is the compliment to it... with it, you can convert any decimal value up to a maximum of 79228162514264337593543950335 to any base (up to 36). The function takes two arguments... the decimal number you want to convert and the base you want to convert it to. Because VB will want to convert numbers that are too large to scientific notation, you will need to pass such large values into the function as a text string... smaller numbers can be passed in as numbers or text strings.

Function Dec2Base(DecimalValue As Variant, Base As Long) As String
Const PossibleDigits = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
DecimalValue = CDec(DecimalValue)
Do Until DecimalValue = 0
Dec2Base = Mid(PossibleDigits, CDec(DecimalValue) - Base * _
Int(DecimalValue / Base) + 1, 1) & Dec2Base
DecimalValue = Int(CDec(DecimalValue) / Base)
Loop
End Function

Here are a few examples to give you an idea of its use...

MsgBox Dec2Base("326022581", 2) ==> 10011011011101011010110110101

MsgBox Dec2Base("79228162514264337593543950335", 16) ==> FFFFFFFFFFFFFFFFFFFFFFFF

MsgBox Dec2Base("3561869315733788", 36) ==> Z2KS69UIAK

[snb]

Hi Rick

The Excel notation DecimalValue = Int(CDec(DecimalValue) / Base)
can be written in VBA as:


DecimalValue = CDec(DecimalValue) \ Base

[Rick Rothstein]

The Excel notation DecimalValue = Int(CDec(DecimalValue) / Base)
can be written in VBA as:


DecimalValue = CDec(DecimalValue) \ Base
I know about that altenative using the Integer Division symbol (the backward slash) for whole number arguments**, but it only works for numbers up to a maximumn of 2147483647 (a Long)... an Overflow error will be raised if you try to use values greater than that. I designed my function to work with numbers up to a maximumn of 79228162514264337593543950335, so I deliberately chose the expression I used in order to avoid that problem.

**So that readers of this thread do not go away with the wrong idea, the equivalence snb spoke about is valid only when the two numbers being divided are whole numbers. Since Integer Division holds only for values up to a maximum of a Long, we can drop the CDec function calls to reveal these two expressions...

N = Int(Numerator / Denominator)

and

N = Numerator \ Denominator

The problem with using the second code line with non-whole numbers is that VB rounds both the Numerator and Denominator to whole numbers before performing the division whereas the first code line does not do any rounding at all. Here is an example to show you the problem...

Numerator = 4.5
Denominator = 1.5

The first code line... Int(4.5/1.5)... returns 3 to the variable N as expected; however, the second code line... 4.5\1.5... returns 2 to the variable N. Why 2 you are probably asking? Because with one exception, all rounding in VBA uses what is known as "Banker's Rounding" where 5's are rounded to the nearest preceeding even number. So, 4.5 gets rounded to 4 (the nearest even number) and 1.5 gets rounded to 2 (again, the nearest even number); hence, because the values are rounded before the division occurs, the second code line ends up dividing 4 by 2 to produce the value 2 before the implied Int function is applied to it (which does not matter for this answer as it is already a whole number value, but would matter had the division produced a floating point value).

Oh, I mentioned before that all rounding in VBA uses Banker's Rounding with one exception... that one exception is the Format function... it uses what I call "normal" rounding (5's are always rounded up to the next higher whole number). To see that, just execute these lines of code in the Immediate Window...

MsgBox Round(2.5, 0) 'Round uses Banker's Rounding

MsgBox Format(2.5, "0") 'Format uses normal rounding

xxx (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[snb]

That's an awfully clear explanation !. Thanks, Rick


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg (https://www.youtube.com/watch?v=v_1iqtOnUMg&lc=UgxLtKj969oiIu7zNb94AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw (https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg.9irLgSdeU3r9itU7zdnW Hw)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htJ6TpIO XR)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg.9ht16tzryC49htOKs4jh 3M)
https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg (https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

For the sake of posterity, here's a related link Project: VBA - Integer Division and Mod (http://zo-d.com/blog/archives/programming/vba-integer-division-and-mod.html)