Results 1 to 5 of 5

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

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

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

    Back in my "Convert a Number in Any Base (Up To 36) to a (Very Large) Decimal Number" 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.
    Code:
    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...
    Code:
    MsgBox Dec2Base("326022581", 2)  ==>  10011011011101011010110110101
    
    MsgBox Dec2Base("79228162514264337593543950335", 16)  ==>  FFFFFFFFFFFFFFFFFFFFFFFF
    
    MsgBox Dec2Base("3561869315733788", 36)  ==>  Z2KS69UIAK
    Last edited by Rick Rothstein; 04-30-2017 at 12:25 AM.

  2. #2
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick

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

    Code:
    DecimalValue = CDec(DecimalValue) \ Base

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    The Excel notation DecimalValue = Int(CDec(DecimalValue) / Base)
    can be written in VBA as:

    Code:
    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
    Last edited by DocAElstein; 06-10-2023 at 01:25 PM.

  4. #4

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    For the sake of posterity, here's a related link Project: VBA - Integer Division and Mod
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2013, 06:18 AM
  2. Read/write very large xl2007 files
    By Rasm in forum Excel Help
    Replies: 3
    Last Post: 04-07-2012, 05:28 AM
  3. Replies: 4
    Last Post: 03-10-2012, 07:15 PM
  4. VBA Function To Extract Decimal Numbers
    By PcMax in forum Excel Help
    Replies: 7
    Last Post: 11-19-2011, 09:42 PM
  5. Unique Large Values From Duplicate List
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 10-04-2011, 02:17 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •