Ask Your Question
0

Round function and Scientific Notation problem

asked 2017-07-14 21:22:38 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I've run into an issue with the ROUND function when coding a macro in Basic:

Function Round(dNumber As Double, iDecimals As Integer) As Variant
    Dim oRound As Object
    Dim vArgs(1 to 2) As Variant

    oRound = createUnoService("com.sun.star.sheet.FunctionAccess")

    vArgs(1) = dNumber
    vArgs(2) = iDecimals

    Round = oRound.callFunction("round", vArgs())
End Function

It returns Scientific Notation on dNumber values that are less than 0.1 instead of the rounded decimal value that's expected.

EXAMPLE: msgbox(Round(0.0333333, 2))

Results in: 3E-02.00 instead of the expected: 0.03

Can anyone tell me why this is occuring and if the solution that I've written below is the correct way of getting around the problem or if there's a better way?

Function Round(dNumber As Double, iDecimals As Integer) As Variant
    Dim oRound As Object 'Round function object
    Dim dCompNumber As Double 'Store the Compensated value of dNumber 
    Dim dResult As Double 'Result of the rounding to be passed
    Dim vArgs(1 to 2) As Variant 'Arguments: Number to be rounded, Number of decimal places

    dCompNumber = dNumber 'Copy dNumber

    oRound = createUnoService("com.sun.star.sheet.FunctionAccess")
    'Get access to the library that contains the Round() function

    'Compensate for Scientific Notation that occurs with numbers less than 0.1
    If dNumber < 0.1 Then dCompNumber = dNumber + 1 ' Add 1 to temporarily increase value > 0.1 

    vArgs(1) = dCompNumber
    vArgs(2) = iDecimals

    dResult = oRound.callFunction("round", vArgs())

    'Remove the Compensation for Scientific Notation
    If dNumber < 0.1 Then dResult = dResult - 1 'Subtract 1 from the temporary value so that it is back to < 0.1 

    Round = dResult
End Function
edit retag flag offensive close merge delete

Comments

Did you read my answer, in specific statement "-7-"?

Lupp gravatar imageLupp ( 2017-07-19 17:57:05 +0100 )edit

If I understand what you're referring to, Print() and Msgbox() do not themselves format the values put into them, correct?

CaptainCanuck76 gravatar imageCaptainCanuck76 ( 2017-07-19 18:14:33 +0100 )edit

They output text and therefore they have to format numbers in one or another way. The do it using the default format in the way they are programmed to do.
With my LibO: I get 0.03 by the command MsgBox(Round(0.33333,2)).based on the first posted code.
[In addition: The arguments array the CallFunction method expects is "zero-based" by default. You dimensioned it 1-based. It doesn't seem to matter, however.]

Lupp gravatar imageLupp ( 2017-07-19 18:44:34 +0100 )edit

There was an extremely blown up comment to my answer. Therefore I respond here.
Yes. This is absolutely in accordance with what I said: The user function Round does return its result using the type it gets returned by the Calc standard function ROUND which was called via the FunctionAccess service, This type is 'Double'. Since Double value is a 64-bit-pattern it cannot be displayed by MsgBox "as is". The command has to convert the value to a human readable text.

Lupp gravatar imageLupp ( 2017-07-21 18:15:33 +0100 )edit

MsgBox will use the format it is programmed to use for the purpose under the found circumstances.
In Version 5.1.something this may have been different as compared with other versions.
No matter: the displayed thing is always text. The number is 0.03 or 3/100 or 3% or 3.00000E-02 or whatever you like to use for telling always the one identical result.

Lupp gravatar imageLupp ( 2017-07-21 18:25:15 +0100 )edit

That the result was obtained by rounding is not a property of the number.
Since 'Double' is a diadic way to represent numbers there isn't even a way for Calc to store the value 3/100 exactly. It will use the start of a periodic bit pattern. No way for Calc or for MsgBox to know that you expect to see 0.03 The programmer of that stuff may try to guess what format a user might want to be used when numbers are to be displayed. Sometimes his guess will be right and sometimes not.

Lupp gravatar imageLupp ( 2017-07-21 18:40:55 +0100 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2017-07-15 10:23:17 +0100

Lupp gravatar image

-1- The first piece of code in the OQ is not VB. It uses the uno-api of LibreOffice.
-2- Do it simple:

REM  *****  BASIC  *****
Global fA As Object

Function myRound(pValue, pNumPlaces)
If IsNull(fA) Then fA = CreateUnoService("com.sun.star.sheet.FunctionAccess") 
myRound = fA.CallFunction("ROUND", Array(pValue, pNumPlaces))
End Function

-3- ROUND - whether called directly in Calc or by the FunctionAccess service - will always return a 'Double' value, never a "decimal value" and never a string formatting the result in any way. How did you see what you claimed to be a result in scientific notation?
-4- Formatting of numeric results for the display in Calc is done by the 'Numbers' format chosen. It does neither afflict the result type 'Number' nor change the cell's content / formularesult value in any way.
-5- The 'Format' function of BASIC actually performs a conversion from a numeric type value to a text.
-6- It's a pitty that LibO BASIC is blurring the facts by its maniacal fondness of automatic conversions.
-7- The way BASIC displays a number by 'Print' or 'MsgBox' is its own choice and does, again, not afflict the actual value.
-8- Same with automaic formatting of a numeric value if used in an operand position of a string expression.

edit flag offensive delete link more

Comments

In regards to statement -3- please see the screenshot:

 photo Libre_Office-Round1_zps4l3szqex.png

YOUR code with only superficial changes is used and the SCIENTIFIC NOTATION of 3E-02 is the result when the value of 0.03 is expected.

CaptainCanuck76 gravatar imageCaptainCanuck76 ( 2017-07-19 18:53:43 +0100 )edit

Sorry! I woud need a guide to get additional information out from the link.

The one relevant question is if we both agree that 0.03 and 3E-02 are two different representations of exactly the same number. If you use a version of LibO preferring the one or the other (or a different) representation by default under certain conditions is of little interest. If you insist on the usage of a specific format you can set the respective format code for the cells in Calc.

Lupp gravatar imageLupp ( 2017-07-20 00:25:09 +0100 )edit

If you want to get an output from BASIC stating a Print or a MsgBox command in a specific format you will need to convert the numeric value explicitly into a text.See @librebel 's answer. Only the spreadsheet cells can keep the value but show a text derived from the value based on a format code at the same time.

My code surely does not define any format for the rounded value. It simply returns the value which is handled by LibO as a 'Double precision floating-point' item.

Lupp gravatar imageLupp ( 2017-07-20 00:30:37 +0100 )edit

Let's try this again :) In regards to statement -3- please see the screenshot:

YOUR code with only superficial changes is used and the SCIENTIFIC NOTATION of 3E-02 is the result when the value of 0.03 is expected.

CaptainCanuck76 gravatar imageCaptainCanuck76 ( 2017-07-21 17:56:01 +0100 )edit
0

answered 2017-07-14 21:50:35 +0100

librebel gravatar image

For this case you could use the Format() function:

MsgBox Format( 0.0333333, "0.00")
edit flag offensive delete link more

Comments

Despite the fact that the ROUND function refers to a (virtual) decimal representation with its second parameter, we should thorougly keep apart the very different processes of formatting and rounding.

Rounding is a mathematical process. Done based on a dyadic floatingpoint representation (as 'Double' is) it may be described as INT(pValue/EXP(-pNumPlaces*LN(10)))*EXP(-pNumPlaces*LN(10)).
Of course the floatingpoint processor will provide fast commands for the purpose.

Lupp gravatar imageLupp ( 2017-07-15 10:41:52 +0100 )edit

Using Format() won't resolve the issue and I wasn't expecting it to but thanks for the response.

CaptainCanuck76 gravatar imageCaptainCanuck76 ( 2017-07-19 15:39:19 +0100 )edit
0

answered 2017-07-21 18:45:04 +0100

CaptainCanuck76 gravatar image

It has come to my attention (again) that LibO auto-formatting functionality may be the culprit here. For Round() results that are Double type variables and < 0.1, LibO seems to force Scientific Notation as far as I can determine.

I may have to resort to one of these:

  • Writing a rounding function that uses String variables instead of Double.
  • Writing a function that detects Scientific Notation and converts it to a decimal value while being contained in a String instead of Double.
  • Use (') single quote to force Double variables to text (String) in hopes of suppressing Scientific Notation.

https://ask.libreoffice.org/en/questi...

edit flag offensive delete link more

Comments

There is no culprit as there is no offence.
I do not actually undersatnd your list of alternatives. Only the first one makes some sense imo, but you don't get a rounding function this way by common terms. The correct way is to make numeric calculations whether containing a rounding function or not, and to stop at that. Getting values displayerd in one or another format is a different thing.

Lupp gravatar imageLupp ( 2017-07-21 20:22:40 +0100 )edit

Quoting @CaptainCanuck76 : "For Round() results that are Double type variables and < 0.1, LibO seems to force ..."
LibO shouldn't know that a value to be displayed was the result of a rounding.
In fact LibO Calc from its very first version V3.3. through my current V5.4.0.2RC used the 'General' format for such values which results in 0.03 for your =3/100.
But BASIC commands like MsgBox e.g.aren't Calc. They may use formats depending on conditions I do not know.

Lupp gravatar imageLupp ( 2017-07-21 21:14:38 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-14 21:22:38 +0100

Seen: 413 times

Last updated: Jul 21 '17