A single piece of Data in a worksheet cell is presented as 179.5950
It presents in this fashion as I have formatted the presentation in the worksheet as a number with four decimal places.
the above formatting is ONLY for presentation. I believe the data is stored as 179.59500000000 for a total of 15 alpha numeric characters.
But when VBA code brings in the above stored number with all its zeros, excel truncates the trailing zeros (thinking it knows best instead of allowing me to tell it how many trailing zeros I desire) and thus hands me a number without any trailing zeros.
Thus formatting data in a worksheet permits me to preserve the presentation of trailing zeros but the data is STORED in excel without preserving the trailing zeros as I have defined in presentation.
Some of you, who work with numbers in a traditional sense will express that this is not a problem. After all there is no mathematical difference between 179.595 and 179.5950
But there is a difference in two totally different ways.
first engineers know that the number of decimal places conveys precision.
Thus keeping the trailing zero conveys that the data is good to the thousandths(in this example) of a unit of measurement. Truncating removes information important to an engineer but not to a pure mathematician.
Secondly, A number that appears to be "one" number may in fact be a number that stores multiple numbers. In my case, an extremely well known surveying company sells Total Stations (a surveying instrument) which outputs degrees, minutes, seconds for an angle as a single number: DDD.MMSS It is a number that stores three pieces of data
In the example I am grappling with, 179.5950 is a number that expects to be decoded as:
Degrees = 179
Minutes = 59
Seconds = 50
But because excel hands the code a number which truncates trailing edges, the decoding becomes
Degrees = 179
Minutes = 59
Seconds = 5
The results are unacceptable.
This problem is NOT a VBA coding problem but instead requires understanding how excel retrieves stored data (different from the presented data) to VBA code. There is no need to present code(though I am willing). This is a more fundamental problem.
Please offer insight into this problem and possible solutions.
Thank you
Bookmarks