Macro make new sheets spew endlessly

Thumboldt · January 22, 2023, 3:55am

I am writing macro function to calculate distance between two GPS coordinates. I am new to writing macros and Basic and not entirely sure what I’m doing.

Libre Calc 7.3.5.2 (x64)
Win 10 21H2

However, after much ponderous hacking, the macro function appears to work. The following is the contents of Module in My Macros and Dialogs.

REM Option Explicit

Function GeoDistance(lat1 as Double,lon1 as Double,lat2 as Double,lon2 as Double)
Dim Lat1Radians as Double
Dim Lat2Radians as Double
Dim DistLat as Double
Dim DistLon as Double
Dim oService as Object
Dim varA as Double


Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")

Lat1Radians = oService.callFunction( "Radians", Array(lat1))
Lat2Radians = oService.callFunction( "Radians", Array(lat2))
Lon1Radians = oService.callFunction( "Radians", Array(lon1))
Lon2Radians = oService.callFunction( "Radians", Array(lon2))
DistLat = Lat1Radians-Lat2Radians
DistLon = Lon1Radians-Lon2Radians


varA = sin(DistLat/2)*sin(DistLat/2)+sin(DistLon/2)*sin(DistLon/2)*cos(Lat1Radians)*cos(Lat2Radians)

varB = 2*oService.callFunction( "Atan2", Array(sqr(1-varA),sqr(varA)))
earthRadius = 6371
distance = earthRadius*varB*1000


GeoDistance = distance

'GeoDistance = 999

End Function

If I call function GeoDistance(args...) from Calc sheet it appears to work (although very slow - which is maybe for another topic).

However, if I click the + sign adjacent to Sheet at bottom taskbar, Calc begins creating endless new sheets. I have to click top-right X to close app without saving.

What is wrong?

JohnSUN · January 22, 2023, 8:07am

Welcome! I don’t see anything in the macro code that would cause such unpleasant program behavior. I think it’s just a coincidence that your function began to give the correct results and Calc began to produce sheets. Kindly check if the sheets will be created in another workbook that doesn’t have this function call. Perhaps your mouse just has a sticky left button?

JohnSUN · January 22, 2023, 8:32am

By the way, why don’t you use lat1*3.14159265358979324/180 instead of oService.callFunction( "Radians", Array(lat1))?

JohnSUN · January 23, 2023, 10:53am

Great video, enjoyed it a lot, thanks! It was somewhat reminiscent of the tricks of David Copperfield - the brain says that this simply cannot be, but the eyes say the opposite.

It turns out that if the UDF is calculated long enough and repeatedly, and during the recalculation of formulas we perform the addition of a sheet, then Calc cannot complete the operation correctly and repeats this again and again.
To test this hypothesis, I created a simple, terribly stupid and at the same time quite long function:

Dim i As Long 
Dim aRes As Double 
	For i = 1 To 10000*aVal + aVal
		aRes=(aRes+i) Mod 10
	Next i
	longLoopVal = aRes
End Function

And yes, after several unsuccessful attempts, I got this effect - the sheets began to breed like cavy.

So your point

was correct - slow computation leads to erroneous program behavior.

Now let’s try to solve your problem. By the way, what is your problem?

You don’t want to have so many sheets?
The simplest solution is to give Calc time to finish the calculations and only then move on (for example, add sheets).
Or turn off automatic recalculation of formulas for the duration of the spreadsheet layout, prepare all sheets, data and formulas, and only then recalculate everything at once.
Do you want to report a bug in the program?
Then it’s worth sending a bug report to Bugzilla. This bug is so troubling that I think the developers will start fixing it very quickly.
Would you like to get the distances between coordinates so fast that the multi-sheet error just doesn’t show up?

It is not difficult. Select cell C2, press Ctrl+F3 and Add, enter a range name (for example, getDistanceA1B2, why not?) and enter the formula

ATAN2(SIN(RADIANS(A1)) * SIN(RADIANS(A2)) + COS(RADIANS(A1)) * COS(RADIANS(A2)) * COS(ABS(RADIANS(B2) - RADIANS(B1))); ((COS(RADIANS(A2)) * SIN(RADIANS(B2) - RADIANS(B1))) ^ 2 + (COS(RADIANS(A1)) * SIN(RADIANS(A2)) - SIN(RADIANS(A1)) * COS(RADIANS(A2)) * COS(ABS(RADIANS(B2) - RADIANS(B1)))) ^ 2) ^ 0.5) * 6372.795

Now paste the formula =getDistanceA1B2 into cell C2 and drag this cell down.

What’s the point? Built-in functions are calculated much faster, calculations will be completed much faster than you drag the mouse to the plus sign

Be sure to check on the test data - I do not understand anything in navigation, I could make a mistake in some sign. I can’t imagine how it is possible to calculate distances on this ball at all!

Thumboldt · January 23, 2023, 1:14am

I’ve confirmed that it only occurs when using the macro function Geodistance().

If I have between two and five rows, then click on the + button, just one sheets is spawned.

However, if I have six or more rows of data (and five or more calls to macro function), clicking on the + button spawns endless sheets and I must close the app from the top-right X unable to save the document.

My mouse-button is definitely not sticky! (Nice thought).

I can upload a video of this happening if necessary (and allowed).

By the way, why don’t you use lat1*3.14159265358979324/180 instead of oService.callFunction( "Radians", Array(lat1))?

Thanks for that! Just shows how far my math education extended!

(Edit)

I’ve done further testing. Sometimes the sheets will endlessly spawn if I enter just three rows, sometimes more. It seems random.

Here’s a link to a screen cap of it happening:

https://youtu.be/K76oY3W0im4

Thumboldt · January 23, 2023, 1:25am

I’ve edited macro to remove call to internal Radians() function. Still have exactly same behaviour.

REM Option Explicit

Function GeoDistance(lat1 as Double,lon1 as Double,lat2 as Double,lon2 as Double)
Dim Lat1Radians as Double
Dim Lat2Radians as Double
Dim DistLat as Double
Dim DistLon as Double
Dim oService as Object
Dim varA as Double


Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")

my_pi = 3.1415926535897932384626433
Lat1Radians = lat1*my_pi/180
Lat2Radians = lat2*my_pi/180
Lon1Radians = lon1*my_pi/180
Lon2Radians = lon2*my_pi/180

DistLat = Lat1Radians-Lat2Radians
DistLon = Lon1Radians-Lon2Radians


varA = sin(DistLat/2)*sin(DistLat/2)+sin(DistLon/2)*sin(DistLon/2)*cos(Lat1Radians)*cos(Lat2Radians)

varB = 2*oService.callFunction( "Atan2", Array(sqr(1-varA),sqr(varA)))
earthRadius = 6371
distance = earthRadius*varB*1000


GeoDistance = distance

'GeoDistance = 999

End Function

'Sub Main

'Print GeoDistance(32,115,33,116)
''Print "Hello"
'End Sub

karolus · January 23, 2023, 8:25am

It’s nice for you to have your own »my_pi«!
nevertheless, due to the limited precision of floating point numbers, this is shortened to 15 digits.
Besides, even BASIC knows the mathematical constant pi, which you may use even if you could not claim it as your own.
If you want higher accuracy, you may use »earthRadius = 6371.0088«

sokol92 · January 23, 2023, 11:43am

You should also avoid creating an oService object (on each call), which is an extremely expensive operation.
Use

Atn(sqr(varA) / sqr(1-varA))

instead of

oService.callFunction( "Atan2", Array(sqr(1-varA),sqr(varA)))

mapado/haversine/blob/main/haversine/haversine.py

from math import radians, cos, sin, asin, sqrt, degrees, pi, atan2
from enum import Enum
from typing import Union, Tuple


# mean earth radius - https://en.wikipedia.org/wiki/Earth_radius#Mean_radius
_AVG_EARTH_RADIUS_KM = 6371.0088


class Unit(Enum):
    """
    Enumeration of supported units.
    The full list can be checked by iterating over the class; e.g.
    the expression `tuple(Unit)`.
    """

    KILOMETERS = 'km'
    METERS = 'm'
    MILES = 'mi'
    NAUTICAL_MILES = 'nmi'

This file has been truncated. show original

I apologise for overestimating both your sense of humour and your mathematical understanding.

Thumboldt · January 23, 2023, 11:51am

Thanks for the help and the funny pic of the globe.

It turns out that if the UDF is calculated long enough and repeatedly, and during the recalculation of formulas we perform the addition of a sheet, then Calc cannot complete the operation correctly and repeats this again and again.

I tested this with my function: opened a sheet of a few gps data points, with distance calculations using UDF GeoDistance() in thirty rows. All function results were displayed; I left the sheet untouched for several minutes (!), then pressed the +. Sheets began spawning crazily.

I don’t think your theory is correct.

Now let’s try to solve your problem. By the way, what is your problem?

Well, I was teaching myself about Calc and macros and thought it’d be amusing to figure out how to write a macro for calculating distance between GPS points in recorded track from bicycle rides.

It took me a while to get it working (for example, who would’ve thought atan2() args in Calc are the reverse of usual?), and I was quite pleased with myself, and thought the function might actually be useful.

As it turns out, I cannot add a new sheet to any sheet that uses this macro function! That’s the problem.

Would you like to get the distances between coordinates so fast that the multi-sheet error just doesn’t show up?

Of course, although I dispute it’s got anything to do with Calc still calculating stuff.

I tried your formula in the way you suggested. Pretty good! New stuff learned there.

Do you want to report a bug in the program?

I think this might be a bug.

JohnSUN · January 23, 2023, 12:17pm

Thumboldt · January 23, 2023, 11:57am

@sokol92

You should also avoid creating an oService object (on each call), which is an extremely expensive operation.

Thanks for the alternative. Out of curiosity, in this case it appears I can make do without calling atan2. If that were not an option, is there a way to

avoid creating an oService object (on each call)
?

sokol92 · January 23, 2023, 12:41pm

Let’s compare performance together. Run the macro Test.

Option Explicit 
Global oFA As Object

' x must be >0 !
Sub Atan2_native(Byval x As Double, Byval y As Double) As Double
  Atan2_native=Atn(y/x)
End Sub

Sub Atan2_FA(Byval x As Double, Byval y As Double) As Double
  Dim oService
  oService = createUnoService("com.sun.star.sheet.FunctionAccess")
  Atan2_FA=oService.callFunction("Atan2", Array(x,y))
End Sub

Sub Atan2_FA_Opt(Byval x As Double, Byval y As Double) As Double
  If oFA Is Nothing Then oFA=createUnoService("com.sun.star.sheet.FunctionAccess")
  Atan2_FA_Opt=oFA.callFunction("Atan2", Array(x,y))
End Sub

Sub Test()
  Dim s As String, t As Double, n As Long, i As Long, res As Double
  n=10000
  t=getSystemTicks()
  For i=1 To n
     res=Atan2_native(Cdbl(i), n)
  Next i
  s="Atan2_native: " & (getSystemTicks()-t)
  
  t=getSystemTicks()
  For i=1 To n
     res=Atan2_FA(Cdbl(i), n)
  Next i
  s=s & Chr(10) & "Atan2_FA: " & (getSystemTicks()-t)

  t=getSystemTicks()
  For i=1 To n
     res=Atan2_FA_Opt(Cdbl(i), n)
  Next i
  s=s & Chr(10) & "Atan2_FA_Opt: " & (getSystemTicks()-t)
  Msgbox s
End Sub

My results:

Atan2_native: 95
Atan2_FA: 9130
Atan2_FA_Opt: 3029

Thumboldt · January 23, 2023, 12:54pm

I might need some time to digest all that. I still don’t know when to use sub(routines) and when to use functions! This in particular is mystifying:

Appears to be pretty identical to

Atan2_FA

but takes a third the time??

sokol92 · January 23, 2023, 1:01pm

The difference is that in Atan2_FA_Opt the object is created once, not on every call.

Thumboldt · January 23, 2023, 1:03pm

Ah OK, I see. Thanks. Interesting.

Thumboldt · January 23, 2023, 12:07pm

@karolus

Actually, I wanted to tell you that it is completely irrelevant to define and use “my_pi” with 20, 200 or 2000 decimal places. only the first ~15 significant digits are used, just as with the builtin constant pi.

Perhaps I should’ve acknowledged your advice, and mentioned that I’d just plugged in the first definition of pi I came across without really caring how many significant digits it had. By that stage I was more concerned with why endless sheets were being spawned.

What I also noticed was a rather superior-sounding sarcasm, misplaced sarcasm if only your English were better. Maybe google “lowest form of wit”?

Thanks for the links to earth radius etc. Did you know that you can get better accuracy (without all the palaver of writing a function for an oblate spheroid) with small distances by using the radius of the earth at your latitude? As it turns out, that’s about 6372.168km at sea-level here. Can you work out where I am?