Question Problem checking number of decimal places in excel via COM-Object

[fc2005]

fellows,

I am writing a program with OpenEdge Progress 11.7, which is to validate an excel file provided by internal users. One of the things that I need to validate is the number of digits that the decimal numbers have on the spreadsheet. On the excel, the decimal numbers are displayed with 4 decimal places. However, if I invoke the LENGTH function in Progress to Read the decimal numbers, it returns me the integer that show the decimal numbers have more than 4 decimal places(please refer to the below screenshots). Is there any workaround that I might adopt? Please accept my gratitude for any advice.

Thank you.

Pseudo Code:
DEFINE VARIABLE chExcel AS COM-HANDLE. DEFINE VARIABLE chWB AS COM-HANDLE. DEFINE VARIABLE chWS AS COM-HANDLE. DEFINE VARIABLE cInFinFile AS CHARACTER NO-UNDO. cInFinFile = "N:\test.xlsx". /* create excel instance */ CREATE "Excel.Application" chExcel. ASSIGN chExcel:Visible = TRUE chExcel:screenupdating = TRUE chExcel:displayAlerts = FALSE chWB = chExcel:Workbooks:OPEN(cInFinFile). MESSAGE chExcel:Range("C5"):VALUE SKIP LENGTH(chExcel:Range("C5"):VALUE) VIEW-AS ALERT-BOX. chWB:CLOSE. RELEASE OBJECT chWB NO-ERROR. chExcel:QUIT(). RELEASE OBJECT chExcel NO-ERROR.

Value in Excel:


Value get in Progress:


I try to invoke the Excel com-handle RUN to call the "LEN" function instead to check the length but the return does not look good

 

[Patrice Perrot]

Hi
It seems that excel round the value with a precision of 4 degits.

So you could try
MESSAGE chExcel:Range("C5"):VALUE SKIP LENGTH(chExcel:Range("C5"):VALUE) SKIp
Round(Decimal(chExcel:Range("C5"):VALUE) , 4 )
VIEW-AS ALERT-BOX.
Patrice

 

[fc2005]

Dear Patrice,
Thank you very much for your reply. Yes, if I invoke the ROUND function, most likely the rounded values match the values on the excel. However, the ultimate purpose of my program is to validate the user input to ensure them inputting a decimal number with at most 4 decimal places. Hence, I prefer not to invoke the round function, instead I want to create a mechanism to check the user input in the excel and throw out an alert if the inputted number is found more than 4 decimal places. But once again, I would like to thank you for your reply.

Here is some additional information:
When I use the Visual Basic Immediate Window to debug the value, it is giving the value "1.43" instead of "1.4299...". And I have another cell, which is liked what you say, the displayed value in the cell, which is formatted, is 0.9829 but its actual value is 0.982866666666667. And if I use the immediate window to debug, it gives me "0.982866666666667", so I think the issue that I reported might not solely due to the excel formatting problem.

 

[TomBascom]

I seriously doubt that any user directly input those values. You are showing numbers that are clearly repeating decimals as a result of calculations on the cells. So you will not be throwing alert on INPUT of such a number. To do that you need to confine your checking to cells that users input, not cells that receive calculated values.

 

[Rob Fitzpatrick]

Pulling decimal values directly from Excel to ABL seems like a bad idea to me, with lots of edge cases waiting to happen if those involved don't understand how numeric precision is handled in both platforms, and how they may change going from one to the other.

In general, if you are dealing with a decimal value in Excel and you expect a certain precision, e.g. two significant digits after the decimal when dealing with currency values, you should use the Excel round function to make that precision explicit.

Note also that the stored precision of a number isn't necessarily the same as the display value, which is affected by the cell's number format.

Value in Excel:

For example, the value above could be the result of the formula =143/100, with a number format of 0.0000. Or it could be a formula =143001/100000 with a format 0.0000. In one case there are two digits of precision after the decimal point, in the other there are five. The underlying values are different even though the displayed values look the same.

Finally, even if you have the right precision for a value in Excel, it may not seem correct once it comes into the AVM. If you have the value 1.43 in a cell in Excel, even if entered as a scalar value rather than the result of a calculation, the value displayed by your ABL code will be 1.4299999999. So you could round it again on the ABL side to see the expected value, but at that point are you really validating what the user entered in Excel?

Maybe you would get better results if you wrote this validation logic in VBA rather than ABL.