[Solved] Macro in spreadsheet - syntax problem?

[spudtu]

I am a beginning macro writer, with intermediate past programming experience. I studied OpenOffice help and forum for hours, Mr. Pitronyak's macro guide for more hours, but am stumped about a few things.

I'm having trouble getting function macros to operate in a spreadsheet. I got a couple to work, but as an example, I have this very simple, silly macro function below. If I click on "Run" it writes "hello" in the macro editor, but if I enter quadNE in a spreadsheet cell, I just get error #Name?.

What am I doing or not doing?



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

option EXPLICIT

function quadNE


quadNE="hello"
write quadNE

end function

Thanks if anyone has a clue!

[RoryOF]

See
Using a macro as a function

[Zizi64]

Code: Select all

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

option EXPLICIT

function quadNE
quadNE="hello"
write quadNE
end function

1. Issue: The Write statement calls the function itself. It is a recursion without exit.
2. The Write statement needs more than one parameters. See it in the Basic HELP:

Write Statement [Runtime]
Writes data to a sequential file.
Syntax:
Write [#FileName], [Expressionlist]
Parameters:
FileName: A numeric expression that contains the file number that was set by the Open statement for the respective file.
ExpressionList: Variables or expressions that you want to be written to a file, separated by commas.
If the expression list is omitted, the Write statement appends an empty line to the file.
To add an expression list to a new or an existing file, the file must be opened in the Output or Append mode.
Strings that you write are enclosed by quotation marks and separated by commas. You do not need to enter these delimiters in the expression list.
Each Write statement outputs a line end symbol as last entry.
Numbers with decimal delimiters are converted according to the locale settings.
Example:
Sub ExampleWrite
Dim iCount As Integer
Dim sValue As String
iCount = Freefile
Open "C:\data.txt" For Output As iCount
sValue = "Hamburg"
Write #iCount,sValue,200
sValue = "New York"
Write #iCount,sValue,300
sValue = "Miami"
Write #iCount,sValue,450
Close #iCount
End Sub

[spudtu]

Thank you so much, Rory and Zizi! I somehow never saw this help page and I will study what both of you have suggested and repost about my (hopeful) progress. Already I had some success closing and then reopening the spreadsheet. Didn't know about that.

[Zizi64]

What do you want to do with the WRITE command?

To display the value of a variable you can use the PRINT or the MSGBOX statement/function.

[spudtu]

Ok, I read the macro guide Rory linked me to, and found this expanded https://wiki.openoffice.org/w/images/7/ ... Macros.pdf Calc Guide Chap. 12 Macros. Both excellent help.

I don't really need the Write command - I guess the Function value will display on its own when called by the spreadsheet.

The little program I listed above was so I could focus on learning the basic syntax of writing and using a macro, and learn about output.

So now I've written the function I really want to run, to determine the quadrant of a line between two cartesian points, defined by their differences in Northing coordinates and Easting coordinates.

The purpose of the function isn't that important - but it seems to be getting stuck on the first occurrance of assigning a value to the function.

Code: Select all

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

option EXPLICIT

function quadNE(n,e)

Dim n
Dim e
	REM n and e will be cell references
	
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

So the value is always returned as 1, no matter what the values of the arguments. I have tried changing the highlighted line above to

quadNE=0

and then the value is always returned as 0.

[RoryOF]

When entering code listings, please use PostReply or FullEditor buttons, and surround your code with code tags
[code]
Place
lines
of
code here [/code]

I've done it for you in the previous posting.

[RoryOF]

The line

Code: Select all

case >0, 0

controls that return. Do you think this syntax is correct?

[FJCC]

Do not use the lines

Code: Select all

Dim n
Dim e

That wipes out the values you passed into the function and resets each variable to 0. Just remove those two lines of code.

[MrProgrammer]

I studied OpenOffice help and forum for hours, Mr. Pitronyak's macro guide for more hours …

I've written the function I really want to run, to determine the quadrant of a line between two cartesian points, defined by their differences in Northing coordinates and Easting coordinates.

You don't need an evil macro with three dozen lines. With "Northing difference" in A2 and "Easting difference" in B2 the quadrant is: =2*(B2<0)+(A2*B2<0)+1. If either difference is zero the answer is not well defined since the location is on the boundary between two quadrants but the formula picks one of the two possibilities.

Do you think [CASE >0, 0] is correct?

I believe it executes the block if the condition variable is greater than or equal to 0. My guess is that CASE >=0 would also work. That would probably be clearer for most people. The documentation I've found for the syntax of the SELECT/CASE statement is a bit vague.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[spudtu]

When entering code listings, please use PostReply or FullEditor buttons, and surround your code with code tags

Message received about framing code in code tags. Thank you!

For what it's worth (from this novice), I read the section on Case Select command in Open Office Macros Explained by Andrew Pitonyak carefully and it seems like the syntax

Code: Select all

case >0, 0

should be correct. I think my problem was using the Dim statements which apparently

wipes out the values you passed into the function and resets each variable to 0. Just remove those two lines of code.

Yikes! No wonder I was getting crazy results.

but when I remove the Dim statements, then run the macro, I get the compile error

"BASIC runtime error. Argument is not optional"

which I only vaguely understand.

You don't need an evil macro with three dozen lines. With "Northing difference" in A2 and "Easting difference" in B2 the quadrant is: =2*(B2<0)+(A2*B2<0)+1. If either difference is zero the answer is not well defined since the location is on the boundary between two quadrants but the formula picks one of the two possibilities.

wow, Mr. Programmer. I am mortified if that will work, after writing a silly evil macro with three dozen lines! Except I am glad to be learning about macros. I will have to play around with that formula. I do frequently have cases where one of the arguments is 0, but maybe I can filter those cases with some preliminary cells with if statements, and handle them separately.

So I guess my only outstanding question is about the error message when I delete the Dim statements.

[spudtu]

Ok, here's my update. I don't know why this works, but on the theory that I should just go along with the gag, I thought I'd try making the arguments optional

Code: Select all

function quadNE(Optional n,Optional e)

So now the function does return the "correct" value according to the argument values.

Example:

In the spreadsheet, the following values are assigned:

E13 = 0.00
F13 = -461.72

When another cell contains the Function

quadNE(E13;F13)

it now returns "4" as a value, which according to the code logic, is correct.

I'll play around with this a little more, and I would like to know if there is a more "correct" way to make this macro work, but thank you all!

[FJCC]

I do not see the error. Check the attached document.

MrProgrammer is saying that the answer is inherently ambiguous when one of the function arguments is zero. That isn't a weakness particular to his proposed solution. What is the quadrant of (0, -1)?

[spudtu]

Oh, yes. I agree with Mr. Programmer. The answer when either argument is 0, is an arbitrary/ambiguous answer. His solution appears to be perfectly competent. It is brilliant. It would have been a shorter road if I'd started out with his formula.

I should have said, also, that for my purposes, I have a protocol for the function value in those ambiguous cases. So, for example,

What is the quadrant of (0, -1)?

I want the function to return the value "3". The value "4" would be just as correct, but I have set up other formulas in the spreadsheet to handle this case as a "3", so I have designed the macro logic to return that value.

The attachment you sent also returns the value "3", which is the correct quadrant for the arguments given below:

quadNE(-1;-7)

I do not think there is an error in the macro now - or at least it is returning correct values.

I really appreciate everyone's help. Removal of the Dim statements, and other suggestions, enabled me to get this macro to work. I never would have figured out those things just by scratching my head for another week.

[spudtu]

Macro Function QUADNE(n,e) as tested and working, returning customized values for either n=0 and/or e=0

Code: Select all

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

function quadNE(Optional n,Optional e)

	REM n and e will be cell references
	
if n=0 then

	Select Case e
	case <0
	quadNE=4
	case >0, 0
	quadNE=1
	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

[RoryOF]

[I read the section on Case Select command in Open Office Macros Explained by Andrew Pitonyak carefully and it seems like the syntax

Code: Select all

case >0, 0

should be correct.

Ah! I see - a multiple condition case statement. I had forgotten about them (I looked up Pitonyak to remind me).

I prefer the version MrProgrammer offered ">=0"; I think it is clearer.

[MrProgrammer]

Macro Function QUADNE(n,e) as tested and working, returning customized values for either n=0 and/or e=0

=2*(B2<0)+(A2*B2<0)+1+(SIGN(A2)+SIGN(B2)=-1) should match what QUADNE returns.

[spudtu]

[=2*(B2<0)+(A2*B2<0)+1+(SIGN(A2)+SIGN(B2)=-1) should match what QUADNE returns.

Wow, again. Thank you.

Lesson for me seems to be: think before you code!

[MrProgrammer]

Lesson for me seems to be: think before you code!

It is a good lesson for me too. Thinking further about the desire to customize the quadrants for ambiguous cases, formula =CHOOSE(3*SIGN(A2)+SIGN(B2)+5;3;2;2;4;1;1;4;1;1) would be better. Then one can quickly make adjustments to those cases.

I am a bit confused by the numbering scheme. If I arrange Northing and Easting as I would on a map, the quadrants assigned by QUADNE (below, assgined clockwise) do not match the standard plane geometry assignments (assigned counter-clockwise).

NE.png (8.56 KiB) Viewed 3868 times

[Solved] Match closest East/North to Lat/long was a discussion about Northing and Easting earlier this year and my introduction to those terms.

[spudtu]

Yeah, that revision of your formula is even easier.

Most people are used to the "counter-clockwise" quandrants which is the mathematics convention, with angles measured from the positive X-axis.

I work in land surveying and we measure angles from "Azimuth North" traditionally, and go clockwise. There are exceptions, but the system we use is "Cartographical Azimuth" to give it a fancy name I never use, as described in this Wikipedia article on Azimuths https://en.wikipedia.org/wiki/Azimuth

So I am contstantly fighting instinct when I visualize sines and cosines for survey azimuths, because of course, like most of us, I am grounded in high-school math, right?

Survey azimuths. Angle A is in quadrant 1, B=2, C=3, D=4

In the above sketch, obviously the sine for angle A would be (if the line is one unit) the horizontal line from "A" back to the north axis.

To add more jazz to the calculations, most software (like Calc), angles are in the "counter-clockwise" system, so you have to translate when writing formulas for land surveying.

The whole reason I wanted a function like QUADNE is to add a correction factor to

DEGREES(ATAN(e/n))

This formula will return an angle between -90 and 90 degrees for all values of e/n. So I want to determine the actual quadrant represented by e and n, and add a factor (via lookup table) to get a nice, friendly angle in degrees between 0 and 360.

For example, without a correction factor

DEGREES(ATAN(-1/1)) = -45 degrees

with a VLOOKUP table

DEGREES(ATAN(-1/1)) + VLOOKUP(QUADNE;range;column;1) = -45 + 360 = 315 degrees

The two results are mathematically and geometrically equivalent, but the latter is easier to work with for most of us.

At this point, I have to suspect there is a more elegant way of doing it, but...

Cheers and thank you all.

[MrProgrammer]

The whole reason I wanted a function like QUADNE is to add a correction factor to DEGREES(ATAN(e/n))

XY Problem?
Try =MOD(DEGREES(ATAN2(n;e));360) to see if it's close to what you want. Perhaps all this work with quadrants and macros was unnecessary. ATAN2 understands quadrants and also avoids #DIV/0! errors. I'm surprised you would use ATAN(e/n) instead of ATAN(n/e) but I am sure I don't fully understand Northing and Easting conventions.

[spudtu]

I have been sidetracked for the week, but I am going to reformulate parts of my spreadsheet using the MOD and ATAN2 commands as you suggest. I had looked briefly at those, but didn't fully appreciate their relevance to my job. Unfortunately, I think the ATAN2 command does not get me out of the business of quadrant evaluation. The counter-clockwise system and reversal of quadrant system seems to require yet another algorithm to get back to a survey system. I will use it, but mainly to avoid the DIV/0 error.

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.

I have to now believe I have been at least a modest example of the "XY Problem". Sincere apologies to all who feel they spun their wheels.
However, I am very grateful to have cracked a little more of the macro code. Seems like it gives me a way to simplify some formula-writing, and I have written a lot of complex spreadsheet formulas which can be mind-breaking to debug.

And although I will be more careful in the future to tell the "backstory", part of my caution was that I hate to ask people to do the work for me. Pet peeve: people that post in a forum like this who haven't even bothered to read a little of the help documentation.

Still, in new XY awareness, here is a brief description of my "big picture".

I am creating a spreadsheet which does Traverse Adjustments for simple (non-branching) traverses. A simplified definition of a traverse is a closed polygon on the face of the earth (small area, assumed to be a plane) whose sides and internal angles are measured with survey instruments (tapes, electronic distance devices, theodelites,etc.). Invevitably, the measurements are not perfect and there is an "error of closure".

For each line ("course") of the traverse, there is a y- and an x-component. After determining the y- (E-L) and x (E-D) components of the error, we redistribute the error amongst all the lines of the traverse.

There are a thousand versions of software out there that accept traverse data and do this adjustment, but the version I have blew up after 10 years of use. It was difficult to use anyhow, and for that and several reasons, decided to just write my own program/spreadsheet.

That's how I ended up here.

So thank you again. I'm gonna proceed with my spreadsheet with the tips and tricks I've learned here, and will keep my eyes open if you have any more.

[spudtu]

Perhaps all this work with quadrants and macros was unnecessary. ATAN2 understands quadrants and also avoids #DIV/0! errors.

Right again, Mr. Programmer. After playing around with the ATAN2 and MOD functions, I found that, almost perversely, that

DEGREES(ATAN2(N;E))+IF(E<0;360;0)

yields angles in the survey system without quadrant macro somersaults. On the face of it, to a surveyor, it might seem nonsensical, since

tan (E/N)

yields a survey angle (angles are from North). But the reverse (clockwise) convention for survey angles flips everything around a second time so the ATAN2 function works for my purposes. It is the mirror image of a mirror image, giving you a true-to-life image.

Then, surprisingly, also,

MOD(angle;360)

produces an angle between 0-360 even when angle<0.

Thanks again.