[Solved] Why doesn't Calc show the macro result?

[spudtu]

I've written this macro (dectodms) which does work when I run it from inside the editor. But when I try to use it in a Doc A.ods cell (as a function), the cell remains blank. So #NAME or #VALUE do not appear in the cell, suggesting that Calc is recognizing the macro.

Code: Select all

REM  *****  BASIC  *****


Option Explicit

Function dmstodec(Optional a)

Dim seconds as Single, dmstodec as Single, absa as Single, absbuk as Single
Dim intega as Integer, minutes as Integer, intsign as Integer
	

If a<0 Then
	intsign=-1
Else
	intsign=1
End If

absa=abs(a)
intega=int(absa)

minutes=int((absa-intega)*100)
seconds=((absa-intega)*100-minutes)*100
absbuk=intega+(minutes/60)+(seconds/3600)
dmstodec=absbuk*intsign

End Function
	

Yet I have another macro function (quadNE), which originally was in another spreadsheet (Doc B.ods), which does work as a cell function when I copy it into the Doc A.ods library.

Code: Select all

REM  *****  BASIC  *****

option EXPLICIT

function quadNE(Optional n,Optional e)

'Dim n
'Dim e
   REM n and e will be cell references; dim command would reset them to 0
   
if n=0 then

   Select Case e
   case >0, 0
   quadNE=1                     REM [b][color=#4000FF]value of function is always returned from here[/color][/b]
   case <0
   quadNE=4
   end Select
   goto endF

elseif e=0 then

      REM N<>0, E =0

   Select Case n
   case <0
   quadNE=2
   Case else
   quadNE=1
   end Select
   goto endF

elseif e>0 then

   select Case n
   case >0
   quadNE=1
   case <0
   quadNE=2
   end Select
   goto endF

elseif e<0 then
   
   select Case n
   case >0
   quadNE=4
   case <0
   quadNE=3
   end select
   
end if

endf:

end function

I'm not a very experienced macro user, and I wouldn't be surprised if I'm doing something wrong, and it might be something really simple, but I've been reading OpenOffice Wiki pages, searching the forum and I can't figure this out. Any ideas would be really appreciated!

[FJCC]

Run the following version of your code and the function will return a value. I simply removed the declaration of a variable dmstodec, which is the name of the function.
I added a print statement to illustrate a problem. If you run DMSTODEC(2.30), it returns 2.5111, not 2.5. The print statement reveals the problem. The value of minutes is 29 and seconds is 99.99953 because absa - intega is 0.29999995.

Code: Select all

Option Explicit

Function dmstodec(Optional a)

Dim seconds as Single 
'Dim	dmstodec as Single 
Dim	absa as Single 
Dim	absbuk as Single
Dim intega as Integer, minutes as Integer, intsign as Integer
	

If a<0 Then
	intsign=-1
Else
	intsign=1
End If

absa=abs(a)
intega=int(absa)

minutes=int((absa-intega)*100)
seconds=((absa-intega)*100-minutes)*100
absbuk=intega+(minutes/60)+(seconds/3600)
print a, minutes, seconds, absa-intega
dmstodec=absbuk*intsign

End Function

[Lupp]

@spudto:
Yes. The literal answer to your question is that the name used for the function is superposed by an internal Dim statement (declaration). You may even see it as a bug of StarOffice Basic that this is not reported as an error.

But:
Wouldn't it be a good idea to explain what you eventually want to achieve using your function?
Just specifying the macro means that the helper must first "reverse engineer" a non-functioning code in order to understand what it is supposed to be about.
See also: https://en.wikipedia.org/wiki/XY_problem .

[spudtu]

@FJCC and @Lupp
Thank you. The DIM declaration error, almost simple enough to figure out - but I didn't!

@FJCC
Thank you again for exposing the problem with absolute value - integer value. I need to get my head around why that is happening. Maybe I'm going to have to perform this task via text handling instead of arithmetic, but I owe you a beverage of your choice, at the minimum!

@Lupp
Yes, you're right. I should have explained the macro purpose, not the first time I made that mistake.

The purpose of dmstodec is to convert an angle in the format DD.MMSS (degrees, minutes, seconds) to a decimal angle. So, in FJCC's example,

dmstodec(2.3000) should = 2.5000

[FJCC]

I agree that handling the input as text seems like a better path. The DD.MMSS format is just a compact way to encode the information. It isn't a numeric value as far as the decoding algorithm is concerned.

[Lupp]

...
The purpose of decdms is to convert an angle in the format DD.MMSS (degrees, minutes, seconds) to a decimal angle.
...

based on the unit DEG ("°") again.

And here my problem with understanding starts: Where from do you get angles represented by decimals misusing decimal formatting of numbers in that strange way?

(@FJCC)
...Maybe I'm going to have to perform this task via text handling instead of arithmetic, ...

Yes! But arithmetic will still be needed, of course.
A general remark (IMO everything): Trying to represent all kinds of measurable values, scaled values, dates, money amounts, .... by differently formatted numbers lead into a very bad situation. The decision to do it this way was probably owed to "slow" processors, "small" and expensive RAM, and narrow exchange channels.

Even nowadays is sometimes expressliy stated (e.g.) that "dates are numbers". Such false credos create a lot of confusion. Values are values and the numbers helping to represent them need to be combined with a mapping into their value spaces. This mapping can be given in many cases simply by pre- or post-fixing a unit designator, and by accepting the resulting string as the value needing to be communicated.

Even before the computer age we needed decades in (German) education to get this accepted as the standard. Now everything is on risk again.

Therefore: Yes! Use strings! And try to do that in a standardized way if there is any true standard in sight.

Serious additional problems are caused by other old decisions. And by the fact that we obviously are inapt of getting rid of such things. One of these decisions (>3000 y ago) was to basically apply a sexagesimal system to angles an to DOT. The second one, not quite as old, was to apply sexagesimal (partly modified) subdivisions in this important range.


As a retired teacher I started a little poject about 1.5 y ago to create support for those wanting remedy. One part of that project was to supply means for evaluating (and also for writing) old-style values even in mixed units. As an old retired teacher I have to admit that the project will die together with me.

However, considering this thread I digged for the already existing code (Basic for demo purposes), and succeeded to apply it to the case. Probably there is somebody interested to take over (send PM!). You find the mentioned code applied in the attached example in the sheet 'ConversionBothDirections'.
CAUTION!! THE DEMO ONLY WORKS WITH LibO Versions 6.2 or higher and may even freeze lower versions!

aoo111188technicalValues.ods

(29.33 KiB) Downloaded 98 times

I also apply for a beverage!

Cheers.

===Editing===
Oh my! I missed to thinkl before I uploaded.
The exemple uses the REGEX() function only available in LibreOffice versions >=6.2.
I can't replace it easily, but I will at least make a demo for the code I talked so much about. Coming soon.
===Editing again===
Found that my mentioned code also uses REGEX() internally via a FunctionAccess object.
I will not soon find the time to replace it there.
THE DEMO ONLY WORKS WITH LibO Versions 6.2 or higher and may even freeze lower versions!

[spudtu]

Lupp, your application for a beverage has been expedited and approved! Congratulations! You are warmly welcomed to redeem the beverage if you ever are in Massachusetts!

I'm working on performing the conversions in the body of the spreadsheet, with some mixture of text handling and math. I'll post the result if anyone is interested. I did find that declaring some variables in the macro as Double instead of Single changed the value of the error, but did not eliminate it.

Questions of differing formats representing values are not my specialty, but in the narrow example of the DD.MMSS format, I know it is perplexing and seems for all the world to be wrong-headed. I can't defend its use. You could probably think of at least a half-dozen (there's that base 6 again!) more logical systems to represent angles.

I can only say that in my field of land surveying, I find it to be convenient and even pleasing at times. I've worked for decades in this field and degrees/minutes/seconds are, for me, second only to breathing (okay, slight exaggeration). It breaks down three stages of precision into convenient packets.

There are many other reasons. For example, I can relatively quickly calculate in my head what distance deviation will occur in a line of a certain length that deviates from another line (0.03 feet/100 feet of line/1 minute). I use that mental estimate often when working outdoors. We also work with many documents as old as 100+ years which use that system.

So I really don't want to give up that system, and to be able to type out an angle either from old documents, equipment readouts, or my thinking, in the DD.MMSS format, makes things easier.

Commercial survey software also uses the format. The spreadsheets and macros I'm writing are supplementing the primary software that I use.

Lupp, you may have some very interesting project there. I can understand some of the points you make. I'm sorry if you are the last pioneer in your thinking. Your spreadsheet is interesting, and thank you for trying to adapt it to this case. As you suggest, OpenOffice does not seem to recognize REGEX and some other commands. But that's ok, you guys have already helped to revise my efforts in a more productive direction. Thank you.

Best to you in all endeavors!

[karolus]

Hallo

instead the oversophisticated REGEX-formula by @Lupp in D3 I would suggest:

Code: Select all

=REGEX(TEXT($A3;"#.0000") ; "([-+]?\d+)(\.)(\d\d)(\d\d)" ; "$1° $3′ $4″";"g")

instead the UDF in E3 I would prefer to use:

Code: Select all

=VALUE(REGEX(TEXT($A3;"#.0000") ; "([-+]?\d+)(\.)(\d\d)(\d\d)" ; "$1:$3:$4";"g"))*24

of course you will need Libreoffice for this!

[MrProgrammer]

I'm not a very experienced macro user …

Given that, you should avoid evil macros if there is another simple way to get the desired result. And in this case there is. Instead of using fake angles stored as DD.MMSS use times to store the angles. With times, Calc knows that minutes and seconds are represented in base 60. So you can add, subtract, multiply, average, these angles, something you can't do with DD.MMSS numbers. Since you can do calculations with angles when stored as times, perhaps you don't even need to convert to decimal degrees. Calc makes it easy to enter times, just use a colon as the separator. For example, enter 2 degrees 30 minutes as 2:30. But you can tell Calc to display the time values in DD°MM'SS" format! Look at my DMS style which uses format code [HH]\°MM\'SS\". Be sure to use [HH], not HH.

Conversion from DD°MM'SS" to decimal degrees is simple, just multiply by 24. Conversion from decimal degrees to DD°MM'SS" is simple, just divide by 24. There is no need for macro functions! I've attached a spreadsheet which shows this idea on sheet S0. This would work on even ancient versions of OpenOffice from, say, 20 years ago. You can calculate sine, cosine, … too for example =TAN(RADIANS(A8*24)) is the tangent of 45°00'00".

202401301719.gif (29.15 KiB) Viewed 2315 times

202401301600.ods

(16.28 KiB) Downloaded 105 times

Yet I have another macro function (quadNE) …

We had a discussion about quadNE six years ago in [Solved] Macro in spreadsheet - syntax problem? where I suggested using formula =MOD(DEGREES(ATAN2(n;e));360) so that you can determine the angle, given northing and easting, without your quadNE macro. See sheet S1 in the attachment.

But the MOD command is great. Would have saved me a few hours of grappling with angles outside the 0-360 range, not to mention converting back and forth from decimal to D.MS degrees. I hadn't noticed it would deal with non-integers. I will use it now.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know, especially section 4. Times in cells

[spudtu]

@karolus
I don't have LibreOffice, but I'm curious to understand what these formulas aim to do, especially in terms of what @Lupp is trying to do.

@MrProgrammer
Thank you for another CALC trick! TIME is a pretty slick way to use DMS numbers. It looks like TIME sees the unit value as 24 hours with a base 60 fraction. I'll keep it in mind as I'm working on this spreadsheet and others. Since converting to a TIME-based format would involve other spreadsheets and commercial software that I use, I don't think I can convert my input format, but it might be good for calculation purposes, output, or to save a step or two in cell formulas.

@MrProgrammer,
Yes! I remember our conversation of 6 years ago and I did start using the MOD command. Thank you! It made some things a lot easier. I do continue to use QuadNE for some purposes. Here is a sample of the spreadsheet. It's not fully operational, but getting closer. Again, its purpose is a proportional distribution of error in angle and distance measurements of a polygon on the face of the (assumed flat) earth.

sls compass adjuster 5.3 SAMPLE.ods

(126.69 KiB) Downloaded 104 times

I did get the dectodms macro working. @FJCC pointed out both a logical problem and a precision-rounding problem with it when I first posted. I seem to have repaired the algorithm logic and worked around the rounding problem. The rounding problem is demonstrated by the first print statement in the macro below. In summary, and using the example of -4.3 as an argument, BASIC results are that

ABS(-4.3)-INT(ABS(-4.3)) ~ 0.29999999
and then INT(100 X 29.999999) = 29

Maybe this is due to precision limits in binary floating point arithmetic in BASIC. Making the variables DOUBLE precision lowers that precision error, but doesn't eliminate it.

In modifying the above formula, I get

FIX(ABS(-4.3)x100-INT(ABS(-4.3)) x 100) = 30

as desired. I tested the macro with 8 or 10 examples, and rounding errors did not occur.

If you run the following macro, some of the print statements offer insight to the rounding issue.

Code: Select all

REM  *****  BASIC  *****


Option Explicit


Function dmstodec(Optional a)

	REM works 2-1-2023
	REM D. Sperduto
	REM do not declare function name - it will zap its value

Dim seconds as Double
Dim absa as Double
Dim absang as Double
Dim intega as Double
Dim minutes as Double, intsign as Double, secondsrough as Double

a=-4.3
print (abs(a)-int(abs(a)))*100, fix(((abs(a)-int(abs(a)))*100)

absa=abs(a)
intega=int(absa)

	REM write intega
	REM write abs(a)-int(a)
	REM write fix(abs(a))

	REM print (abs(a)-int(abs(a)))*100, (absa-intega)*100 


	REM write abs(a)*1000000000000000, int(abs(a))*10000000000000000
	
minutes=fix(absa*100-intega*100)
secondsrough=(absa-intega)*100-minutes
seconds=(absa*100-intega*100-minutes)*100
absang=intega+(minutes/60)+(seconds/3600)
dmstodec=absang*sgn(a)

	REM print a, absa, intega, minutes, secondsrough, seconds, absa-intega
	
print dmstodec

End Function
	

I did try solving the DMS to decimal task by some text handling commands in CALC formulas. I got pretty close, but it turns out there is a bug (it seems) in the MID command. When I use it like this

MID("4.3000);SEARCH(".";E23)+1;2)

the formula returns "3". It should return "30".

After banging around for a few hours trying to get past this problem, I threw down my hands (previously I had thrown them UP) and plowed through with the macro instead. I really like that I can use it like a simple function that does the whole task, instead of a long formula I have to paste into other long formulas.

[Lupp]

Code: Select all

=MID("4.3000);SEARCH(".";E23)+1;2)

isn't a correct formula at all and the reference to E23 doesn't make sense. Did you mean

Code: Select all

=MID(4.3000;SEARCH(".";4.3000)+1;2)

If so you should get the string ".3" returned. I assume you accepted the setting >Tools>Options>LibreOffice Calc>Calculate>>Enable regular expressions in formulae
(Respective option exists in AOO.)
Which makes SEARCH() interpret the search string as a RegEx - where "." means one arbitrary character.
Use FIND() instead which always takes the search string as a literal.
In addition the formula

Code: Select all

=MID(E23;SEARCH(".";E23)+1;2)

with a number type in cell E23 resulting in trailing spaces if default-formatted wouldn't work because the default NumberFormat 'General' doesn't return them. MID() and FIND() are supposed to use the default format even if the cell has setz a different one.

aoo111188_ConcerningFINDvsSEARCH.ods

(8.48 KiB) Downloaded 97 times

(It's not always exactly easy to get reliably correct formulas without "oversophisticated" parts.)

[Lupp]

One final post by me:
1. I would like to ask the OriginalQuestioner to replace in his first post (the question) the subject by something like "Why doesn't my macro show the expected result?".
2. There is a serious suggestion how to replace entries misusing an ordinary decimal format by using the semicolon as the (single) relevant delimiter. This would be "as shorthand" as what was actually used, but avoid lots of problems and possible misunderstandings.

What's suggested is demonstrated in the new attachment.

aoo111188_ConcerningFINDvsSEARCHandRecommendedSemicolon.ods

(9.79 KiB) Downloaded 99 times

[spudtu]

@Lupp
Apologies, I was not paying close enough attention when I cut and pasted and then edited the formula from the spreadsheet and you kindly tried to guess my meaning. I meant to post this, which is similar to your ";" spreadsheet formula except I used SEARCH instead of FIND:

Code: Select all

=MID(4.3000;SEARCH(".";4.3000)+2;2)

Yes, I do have regular expressions option on.

The formula above returns "3". It wanted it to return "30".

If I make the position offset +1 instead of +2, it would make more sense

Code: Select all

=MID(4.3000;SEARCH(".";4.3000)+1;2)

but then it returns ".3"

But when I take a page from your ";" spreadsheet and do this

Code: Select all

=MID("4;3000";SEARCH(";";"4;3000")+1;2)

it correctly returns "30".

But I don't think I need to dig more deeply into the problem with using MID and SEARCH or FIND, since I seem to have a working macro.

PART II
@Lupp
1. Would not the existing title be more descriptive considering that I was not getting any result at all? The cell with the macro returned blank, as @FJCC pointed out because I had declared the function name dmstodec as a variable.

2. That's a nice little routine you came up with there. I will have to think about how it might be useful in my world. I agree that the way modern land surveying software has developed has resulted in nomenclature that is misleading to anyone outside the field. And I think I might understand in a more concrete way your greater campaign to change systematic practices like that.

I rely daily on two or more commercial softwares using DD.MMSS, and a several of my self-created spreadsheets. Perhaps there are types of software that already use a ";" instead of ".". But the ";" format and the simple formulas will be useful to me somehow, I'm guessing. Thank you.

[Lupp]

It seems you didn't read my explanation concerning the difference between SEARCH() and FIND().

I rely daily on two or more commercial softwares using DD.MMSS, and a several of my self-created spreadsheets. Perhaps there are types of software that already use a ";" instead of ".". But the ";" format and the simple formulas will be useful to me somehow, I'm guessing. Thank you.

My suggestion was not about formatting, but about a reasonable, unmistakable, textual representation of your information which can easily be gotten based on the obscure misuse of formatted numbers.

[spudtu]

Ah! In reading both your posts this morning, I did not notice that you had posted two spreadsheets. Sorry. I can see now those formulas would allow a more unique representation of angle values.

I do understand, I think, your greater point about unmistakable representations of values, benefits of that, and how your suggested formulas would serve that end. Unfortunately, the representations we use have become the standard among tens of thousands of surveyors and the software companies which work with us. It might take some time to change people's thinking and the infrastructure, if there were even a general acknowledgment of the benefit. But at least you have placed the idea in one more person's mind.

[MrProgrammer]

I meant to post this: =MID(4.3000;SEARCH(".";4.3000)+2;2)
The formula above returns "3". It wanted it to return "30".

Type that in a cell, press Enter, then look at the formula bar and you will see why the value is "3" instead of "30". This demonstrates one of the dangers of working with fake numbers in D.MMSS format.

Yes, I do have regular expressions option on.

Do you understand Regular Expressions? If not, you should disable that feature or you should use FIND, since it doesn't use that feature. Read the link to learn why your SEARCH for a period doesn't do what you expect. See section 8 Single Character Match.

Unfortunately, the representations we use have become the standard among tens of thousands of surveyors and the software companies which work with us.

If you will continue to work with fake D.MMSS numbers, perhaps these Basic functions will help. If you won't be using times for angles, the only two macros you need are DEC2DMS and DMS2DEC. You will have to copy/paste the functions into a module of your choice in My Macros. After doing that, this attachment demonstrates how they are used, with a sheet for each function. It uses styles DegDec, DegDMS, and TimeDMS to help avoid confusion about cell values. These styles add decorations to the numbers, but the cell values do not include them. The decorations only appear when viewing the cell. The decorated cells have numeric values. You can copy these styles to your own spreadsheet by copying a cell using them from my attachment and pasting it in your spreadsheet. Apply styles as appropriate in your spreadsheet using the Format → Styles and Formatting dialog.

202402032142.ods

(16.45 KiB) Downloaded 90 times

Function DEC2DMS(Ddddd As Double) As Double
DEC2DMS = Int(Ddddd) + Val(Format(Ddddd/24,"MMSS"))/10000
End Function

Function DEC2TIME(Ddddd As Double) As Double
DEC2TIME = Ddddd/24
End Function

Function DMS2DEC(Dmmss As Double) As Double
DMS2DEC = TimeValue(Format(Dmmss*10000,"0\:00\:00"))*24
End Function

Function DMS2TIME(Dmmss As Double) As Double
DMS2TIME = TimeValue(Format(Dmmss*10000,"0\:00\:00"))
End Function

Function TIME2DEC(HHmmss As Double) As Double
TIME2DEC = HHmmss*24
End Function

Function TIME2DMS(HHmmss As Double) As Double
TIME2DMS = Int(HHmmss*24) + Val(Format(HHmmss,"MMSS"))/10000
End Function

I do continue to use QuadNE for some purposes.

Your QUADNE is a couple of dozen lines. I wrote a simple macro called NE2QUAD which produces the same result as QUADNE, and a macro called NE2DEC which converts Northing and Easting to a nonnegative angle in decimal degrees less than 360. You will find sheets for these two functions in the attachment.

Function NE2QUAD(N As Double, E As Double) As Double
NE2QUAD = Choose(3*Sgn(N)+Sgn(E)+5,3,2,2,4,1,1,4,1,1)
End Function

Function NE2DEC(N As Double, E As Double) As Double
Dim D As Double
Select Case True
   Case N>0  : D = Atn(E/N)*180/PI
   Case N<0  : D = Atn(E/N)*180/PI + Iif(E>0,180,-180)
   Case E=0  : D = 0 ' N=0 and E=0 is undefined!
   Case Else : D = 90*Sgn(E)
End Select
NE2DEC = D - Int(D/360)*360
End Function

[spudtu]

@MrProgrammer
I do understand in a rudimentary way about regular expressions, but don't use them much, and forgot that the "." in the formula

Code: Select all

 =MID(4.3000;SEARCH(".";4.3000)+2;2)

would be one example. In experimenting with the formula, I tried using "\" before the ".", adding or removing double-quotes, I tried using FIND, and other approaches, none of which seemed to return the desired "30". Also, CALC reduces "4.3000" to "4.3" as soon as I remove focus from the cell and then refocus on it. I don't know if that plays a part.

But now the macros I wanted to work are working, largely due to help on this forum, and doing the job. So to get my spreadsheet accomplishing its purpose comes down to other challenges, not number formats.

The Wiki page you posted on regular expressions is a lot more thorough and helpful than the CALC help pages, so thanks for that, too.

That's quite a suite of DD.MMSS conversion, coordinate and quadrant macros you've posted. They use some BASIC commands I don't know about, but the code seems clear and simple.

I do have to continue working with those number types. For better or worse, it's the way surveyors work in some software, and does not slow us down. I'm sure I'll be experimenting with your macros in my next round of modifying/writing survey reduction/adjustment spreadsheets.