Consulting

Results 1 to 5 of 5

Thread: Excel is forcing numbers on me!

  1. #1

    Unhappy Excel is forcing numbers on me!

    Hi folks,
    I type the numbers shown below into cells in a worksheet but Microsoft Excel modifies them.
    Data typed into a cell Data displayed by Excel
    999,999,999,999,999.99 999,999,999,999,999.00
    99,999,999,999,999,999 99,999,999,999,999,900.00
    99,999,999,999,999,999,999 99,999,999,999,999,900,000.00
    999,999,999,999,999,999,999 999,999,999,999,999,000,000.00
    999,999,999.99 1000000000

    Is there a way to stop this from happening? I am writing a vba code that uses the numbers typed into certain cells and I do not want Excel to alter them because that may lead to the wrong results.


    Thanks.

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    740
    Location
    may be typing an apostrophe in front of your numbers is what you need?

    '999,999

  3. #3
    VBAX Tutor
    Joined
    Mar 2014
    Posts
    210
    Location
    Expand the cell length.
    Also, the value you see IN the cell may be different in the function bar (the Fx box,atop the cells C & D).
    The function bar IS THE EXACT NUMBER, but the cell box may round or reformat for viewing. (dumb)
    Last edited by ranman256; 06-05-2014 at 10:39 AM.

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,082
    Location
    It's all down to the floating point engine in Excel. Although it can display 30 decimal points, the precision for a specified number is confined to 15 significant figures.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,662
    Location
    It's ugly, but if you enter long numbers as text (with a leading single quote: '99999999999999999) internally you can get more precision using Decimal-typed variables


    Sub numbers()
        Dim v As Variant
        
        'Decimal variables are stored as 96-bit (12-byte) signed integers scaled by a variable power of 10.
        'The power of 10 scaling factor specifies the number of digits to the right of the decimal point, and
        'ranges from 0 to 28. With a scale of 0 (no decimal places), the largest possible value is
        '+/-79,228,162,514,264,337,593,543,950,335. With a 28 decimal places, the largest value is
        '+/-7.9228162514264337593543950335 and the smallest, non-zero value is +/-0.0000000000000000000000000001.
        
        'Note
        'At this time the Decimal data type can only be used within a Variant, that is, you cannot declare a variable
        'to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.
        
        v = CDec(Replace(ActiveSheet.Range("a1").Text, ",", vbNullString))
        MsgBox v
    End Sub

    If you start doing math, I suspect that you'll get inaccuracies.

    I remember seeing a BCD package for excel a long time ago that allow almost unlimited precision by treating string 'numbers' on a character by character basis
    Attached Images Attached Images
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

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