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

[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