Home / Questions / 332099
Accepted
JCstdio
Asked: 2020-02-26 00:55:45 +0800 CST 2020-02-26 00:55:45 +0800 CST

vba when inserting a value rounds large numbers

  • 772

I am trying to do a very simple operation, which is to copy a data from one cell to another in excel by vba. The thing is that they are large numbers, and when inserting the value in the cell, it rounds off the last digits.

For example: 1234567891234567 transforms it into 1234567891234570

Does anyone know why?

Thank you very much.

vba

2 Answers

  1. Best Answer

    It is because of the limits of Excel itself.

    Excel Specifications and Limits

    In the link that I have put, there is a part that just explains what happens to you:

    enter image description here

    When entering the digit number 16, even applying numerical formatting, Excel will ignore it, due to the limits of the program itself.

    In your example you say that 1234567891234567, which just has 16 digits, rounds it to 1234567891234570. It keeps the 16 digits, but from 16 onwards they are zeros.

    The only solution if you need it to be displayed as it is, is as commented before, putting an apostrophe (single quote) before the number. That way, Excel will recognize it as text, and you'll be able to view it.

    If you need such a large number to do mathematical calculations, you may need another program, because Excel is too small for you. If it is just to display it because it is, for example, a code or some kind of identifier, then you can use the apostrophe, which will not affect the formulation.

    Even when you put '1234567891234567in a cell, for Excel inside there will only be a text that puts 1234567891234567, the quote does not bother you at all, because Excel ignores it for formulation purposes.

    • 1

  2. You have to apply a number format to the cells/range you want.

    For example

    ActiveSheet.Columns("D").NumberFormat = "########################"

    Although it may be the best option to format the cell as text:

    ActiveSheet.Columns("D").NumberFormat = "@"
    • 0