Round function and Scientific Notation problem

English
, , , , ,
#1

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
#2

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

#3

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

#4

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.]

#5

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.

#6

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.

#7

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.

#11

-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.

#12

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.

#13

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.

#14

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.

#15

Let’s try this again :slight_smile: 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.

#8

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

MsgBox Format( 0.0333333, "0.00")
#9

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.

#10

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

#16

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.
#17

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.

#18

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.

#19

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.