Set cell to long number value without rounding

[AB91]

Hi,

I'm sure I'm just missing something obvious but I have a raw data set, which I'm putting into a dictionary.

One of the values is an ID, which can (but not always) be a 20 digit number.

I've got it into the dictionary by defining the item as variant, and it's being stored as "Variant/String" and it stored in the dictionary without rounding - which is perfect.

However I'm then trying to get those values into the spreadsheet, by looping through the dictionary and setting certain cells equal to certain values, but every time I try to put this ID into Excel, it rounds after 10 digits.

Can someone please help?

I'm trying:

Cells([row],[column]) = DictionaryEntry.ID

And I'm totally lost.

Thanks in advance!

---------

Edit: Probably not the smartest solution but I just went in a formatted the relevant columns a text field prior to putting the values in. Thanks for everyone's help

[mehmetcik]

I think that you will need to format the cell as text.

I depends on what you want to do with the number.


https://support.microsoft.com/en-gb/...ectly-in-excel

[mjr veverka]

https://support.microsoft.com/en-us/...sults-in-excel
"In the case of Excel, although Excel can store numbers from 1.79769313486232E308 to 2.2250738585072E-308, it can only do so within 15 digits of precision. This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel."

And also:
https://en.wikipedia.org/wiki/Numeri...icrosoft_Excel
https://superuser.com/questions/3739...igits-in-excel
https://answers.microsoft.com/en-us/...4-f9bb412c9039
etc.

[AB91]

It's being stored correctly in the VBA dictionary as a string/variant, but I can't get it to be put into a cell as a text field - any advice there??

[mjr veverka]

1. Excel limitations
2. Vba is just an add-on to excel
3. How do you change the number into text at the vba level before you copy it into the cell ? - show the methodology

[AB91]

Hi Porucha - thanks for your help, I'm just not that good at VBA unfortunately.

So the long number is stored as a text field in the source data.
In the dictionary, I've assigned it to a variant field by "=[cell].Text"
Then in the dictionary, it's being stored as the full number, as a Variant/String (based on looking at the Locals window when it runs)
So then all I'm trying to do is put that text value into a cell, which I'm failing at. If that's an Excel limitation, then I'll figure something else out, but as it's a text/string I assume there is a way to work it

[mjr veverka]

You can do experiments:

Please Login or Register  to view this content.