Using macro as a function. Why isn't it working?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Using macro as a function. Why isn't it working?

Post by imdumb »

I used the macro recorder to make a macro.

Its supposed to delete whats in cell A1. When I run it regularly it works just fine.

Here's the code:

Code: Select all

Function deletecellA1()
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "a1"

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Flags"
args2(0).Value = "SVDFN"

dispatcher.executeDispatch(document, ".uno:Delete", "", 0, args2())

end Function
But what I want it to do is to run as a function in an "if" statement in cell B1, which looks like this:
=IF(C1=0;0;DELETECELLA1())

The problem is, when I type a "1" into cell C1 and enter it, when it gets to cell A1 to delete it, it tells me "Protected cells can not be modified" (with an ok button, and when you click that button it just goes to cell A2 and doesn't delete A1.).

"Protect document" is not being used. "Format cells">"Cell Protection" is not clicked.

Can anyone let me know why this is happening, and/or modify my code to make it work?

Thanks in advance! :D
Last edited by imdumb on Mon Oct 09, 2017 3:37 am, edited 6 times in total.
open office 4.1.2, windows 7
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Using a macro as a function. Why isn't it working?

Post by FJCC »

A function cannot change a cell different than the one from which it was called. It may not be able to do anything other than return a value. That is, I'm not sure a function in Z1 can change anything about Z1 other than its value.
What are you trying to accomplish by clearing A1? What side effect of that is desirable?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using a macro as a function. Why isn't it working?

Post by Zizi64 »

You must choose an another way to achieve this task. The functions can modify the cell (or a cellrange for the array-functions) only where the functions was called from.

Just a tip:
Use a "changed" event for the observed cell, that will launch your PROCEDURE (subroutine), instead of a function. That 'sub' will examine the value/text/formula of the source cell/s/, and it can modify the target cell/s/.
You must WRITE that macro based on the API functions, but you can not RECORD it.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

A function cannot change a cell different than the one from which it was called. It may not be able to do anything other than return a value. That is, I'm not sure a function in Z1 can change anything about Z1 other than its value.
Ok, at least I've learned something here and I can move away from trying to make that work.
What are you trying to accomplish by clearing A1? What side effect of that is desirable?
I'm trying to get a group of random numbers produced in other cells pasted into column A, and then when certain sequences hit, the numbers will get deleted and the column will then be reset for a new group of numbers.

Thanks for taking your time to help me with this!
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

FJCC wrote:A function cannot change a cell different than the one from which it was called. It may not be able to do anything other than return a value. That is, I'm not sure a function in Z1 can change anything about Z1 other than its value.
In fact a formula cannot change anything directly concerning the cell it is executed for by a side-effect of one of the called user-functions. Any effects are indirect based on the returned result. This also is the case for all the cells contained in the same sheet as the formula cell. The Sheet is locked insofar. Moreover the attempt to achieve such a side-effect does not raise an error. The respective commands are treated as if executed, but have no effect.

However, side effects of functions during formula evaluation are allowed to do many things affecting different sheets. In fact anything afaik.

Thus the following function will do as expected if called from a cell in one sheet for a cell in another sheet.

BTW
-1- Don't use the macro recorder if you want to learn something about programming for AOO.
-2- The name "uno:Delete" for the uno command is misleading. It is a comprehensive "Do as if the 'Del' key was applied to the cell."
-3- Be careful about side-effects of functions. (Recalculation may occor unexpectedly, e.g. Make sure the effects are what you want.)
-4- LibreOffice since (if I'm right) V 5.1 has loosened the same-sheet-restriction. The central properties 'String' and 'Value' of sister cells can be changed now. Concerning the 'Formula' property the only remaining restriction I know of is that it cannot be cleared (set to "").

The command "theCell.ClearContents(511)" is bad style. The numeric parameter should be composed of the respective named constants provided by the api. (511 being the summa of all that: Clear everything that can be cleared at all.)

Code: Select all

REM  *****  BASIC  *****
Function clearCell(pSheetName, pCellName)
theDoc   = ThisComponent
theSheet = theDoc.Sheets.GetByName(pSheetName)
theCell  = theSheet.GetCellRangeByName(pCellName)
theCell.ClearContents(511)
clearCell = "New cell type = " & theCell.Type
End Function
If the function was successful the new cell type returned should be 0 (zero) telling "Cell is blank."

(I did not yet test if side-effects of user-functions calling the STYLE() function, e.g, via a FunctionAccess object are executed. There may be additional flaws in my answer.)
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Lupp wrote:

-1- Don't use the macro recorder if you want to learn something about programming for AOO.
If you roll your eyes over my reply, I'd totally understand. But when I started my project, I didn't really expect to run into anything that would be so complicated the macro recorder couldn't handle. I didn't even know there were things it couldn't handle. So as you can probably guess I know almost nothing about code, and even less then that about code that didn't come from the recorder. But now I see I've got to learn something to have a chance at getting the project done.

I've at least learned that, so I guess that's progress. I'm pretty sure you've just helped me a lot, but I hope you'll suffer some dumb questions here. Here goes:

1. About that code you gave me.
The function line says:
clearCell(pSheetName, pCellName)

Ok, so the name of the file is deletecellA1.ods and the cell name is A1.

So, would the function I'd write in a cell look like this:
=clearCell(pdeletecellA1.ods,pA10)

or this:
=clearCell(deletecellA1,A1)

or something else?

Also according to what you wrote, I'm thinking this function would go in cell A1 of sheet 2. Am I right about that?

If you answer these questions will it be enough to get me by, or will I need to know a lot more?

If you think I'm being too lazy here to go out and learn these things for myself, then I probably wouldn't blame you. But the thing is, I'd hardly know where to even start, and what kind of time investment I'd have to make to learn something I may never have to use again. And I'd wonder whether or not I'd even be successful in finding answers.

So any extra help you could give would sure mean a lot!
open office 4.1.2, windows 7
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using a macro as a function. Why isn't it working?

Post by Zizi64 »

1. About that code you gave me.
The function line says:
clearCell(pSheetName, pCellName)

Ok, so the name of the file is deletecellA1.ods and the cell name is A1.

So, would the function I'd write in a cell look like this:
=clearCell(pdeletecellA1.ods,pA10)
The 'SheetName' means the name of the Sheet (the label of the sheet TAB), but not the filename of the document.


The Spreadsheet document - referred in the macro - is the active document where the function was called from:

Code: Select all

 theDoc   = ThisComponent
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

You may play with the attached demo and study the code in the BASIC editor / debugger.
Please note that I changed a bit:
The code now works on any single cell range (which also still may be a single cell).
The CellFlags defining the details of the action are now passed via a new parameter.
Attachments
aoo90624SideEffects_1.ods
(19.06 KiB) Downloaded 294 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Thanks for your efforts, it looks like you really poured some hard work into that and I appreciate it. Unfortunately I can't quite see how to adapt it to what I'm trying to do, which I don't think is quite as complex as what you've done.

Let me explain it.

I have a random number generator that spits out numbers from 1 to 5. When it gives a number, I have a macro that places the first number into cell A1 and then each following number into the next empty cell down (cell A2, A3, A4, etc.)

Here's what is supposed to happen after each number is placed into the column:

#1: when five "1's" have been placed in column A, the macro I'm hoping to write will delete everything in column A, which will then reset the column for the next set of incoming numbers.

#2: works the exact same way #1 does.

#3: is a number that is placed into the column but is ignored. It doesn't trigger any action, no matter how many of them there are.

#4 and #5: when a #4 or #5 is placed in column A, the macro is triggered immediately.

So heres some examples of column A:

The following random numbers are put in:
1
1
2
2
3
3
3
3
3
1
1
1 (five "1's"-MACRO IS TRIGGERED)

3
3
3
3
3
3
3
3
3
3 (Ten "3's", nothing has happened. But then...)
5 (one "5"-MACRO IS TRIGGERED)

1
2
2
2
2
2 (five "2's"- MACRO IS TRIGGERED)

4 (one "4"-MACRO IS TRIGGERED)

An (exceedingly unlikely) million straight "3's"? The macro is never triggered until some 1's, 2's or a 4 or 5 start appearing.

I'm thinking this problem will likely be the "Mount Everest" of not only my project but of everything I do with spreadsheets and it should be pretty much downhill after this. So if I could get some help with that it would really save me A LOT of time and would be a really big deal. Otherwise I have, for me, a pretty bad struggle ahead.

Thanks again, you guys are really terrific!
Last edited by imdumb on Fri Oct 06, 2017 8:02 pm, edited 1 time in total.
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

How to say? I'm pretty lost now, because I cannot see for what you need the spreadsheet at all. The effect of what you describe will always (if not a run of 1048575 numbers "3" does occur) be that the sheet is blank again. There must be something else the sheet and/or the macro do. Do you want to do some statistics on the results?
What I understood so far would look (in a crude peusocode) as follows:

Code: Select all

Repeat
  Repeat 
    count1 := 0 
    count2 := 0
    n      := Rbtw(1, 5)
    If n=1 Then Inc(count1)
    If n=2 Then Inc(count2)
  Until (n=4) Or (n=5) Or (count1=5) Or (count2=5)
  Case 
    count1=5: dosomething1
    count2=5: dosomething2
    n=4     : dosomething4
    n=5     : dosomething5
    "" If all the dosomethingN is just deleting something, no result at all will be available in the end.
  Endcase
Until somecondition
What did I miss?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Basically, here's what it's for. I hope you're familiar with the sport of American baseball.

Imagine a batter steps to the plate and the pitcher starts pitching to him. 4 balls and he walks, three strikes and he's out, foul balls count as strikes when there are fewer than 2 strikes and are nothing if there are two strikes (but they are still recorded in column A anyway), and a ball that's hit (a "fair ball") is either an out or a hit. The numbers that are in the cells would, in this case, be the results of each pitch.

In every one of those situations the batter's "at bat" is finished, and the result in Column A is the result of each pitch thrown (ball, strike, foul, out or hit). This is where the macro (or function) kicks in, and erases everything in column A and is now prepared for the next batter with a completely empty column, ready to record the pitches. "Batter up!" in this case,= a new and blank Column A.

Hope thats a good enough description for it to make sense.
The effect of what you describe will always (if not a run of 1048575 numbers "3" does occur) be that the sheet is blank again.
That is almost EXACTLY what I want to happen, except I don't want the whole SHEET to be blank, I only want COLUMN A to be blank.
(if not a run of 1048575 numbers "3" does occur)
There's a way better chance of any number of monkeys banging on keyboards and happening to correctly type out the code of this macro then there are of this situation ever happening. :lol: Actually it would probably be a miracle if the column ever got to be more than, say, about twenty numbers long.

I haven't yet tried what you just wrote, but I'm sure it puts me at least way closer to my goal. And that's incredible! :super:
Last edited by imdumb on Fri Oct 06, 2017 7:07 pm, edited 1 time in total.
open office 4.1.2, windows 7
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Ok, now you have me wondering.

Do you think it might be a better idea to start a brand new thread where I describe what I just described (about the baseball situation and the column A idea), then explain that you made the "pseudocode" and put it in there, and then to see if new viewers (with fresh eyes) might might jump in and possibly help turn it into the real thing?

Along with that, I could put a message on this thread saying that this thread has been continued in the new thread.

This is an old thread now and I I'm guessing it being so might not attract so much interest.

What do you think?
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

I don't understand, and this in many ways.

1.Very few Europeans ar interested in Baseball or know much about it (afaik). In my case (as a German) I don't even feel capable of understanding your explanations.
(I watched one Baseball game some time in 1992 next to Milwaukee. My most striking experiences were that there were lots of visitors who didn't pay much attention to the game as far as I could see, and that the players were on vacation most of the time.

2. I obviously did not yet understand a specific aspect concerning your intentions. What about the random numbers? Is the game itself your "real-world random number generator" or are you talking of simulating a game using a technical RNG (software or radiation sensor e.g.)?

3. Why do you want to clear column A? If using spreadsheets at all for the purpose, I surely would prefer to generate something like a protocol of the game in a sheet and to prepare for any kind of evaluations this way. Being next to completely ignorant of the rules I cannot suggest a proper way to do so, however. (I could do it for tennis, e.g.)

4. Concerning the piece of pseudocode I posted and you mentioned: It will not be helpful in creating a solution. I did it to explain how little I had, and for what reason I doubted If using spreadsheets was a good idea regarding the supposed task. I still would need an explanation insofar. Why not use a general-pupose programming tool / IDE? Spredsheets are not good in sequential tasks. They are "thinking" the functional way. Basically they are seeking an equilibrium: The formulas recalculate the sheets to get them into a state where an additional recalculation wouldn't change anything. Changes are induced by editing. This either meaning a new entry into a formerly blank cell or changing the content of a cell by explicit interaction. Any usage of so called "macros" in the background is basically a violation of these principles, and should be restricted to cases of urgent need and clear specification. (This does much less apply, imo, to user functions without side-effects.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Let me see if I can figure out another way to describe it. You probably wont hear from me again today (or at least in a good number of hours).

But quickly here, the spreadsheet itself is the RNG, using the randbetween function.
3. Why do you want to clear column A? If using spreadsheets at all for the purpose, I surely would prefer to generate something like a protocol of the game in a sheet and to prepare for any kind of evaluations this way. Being next to completely ignorant of the rules I cannot suggest a proper way to do so, however. (I could do it for tennis, e.g.)


Not sure what you mean by this (the underlined part) but I could describe it in more detail if it would help. Unfortunately I cant, off the top of my head, think how to relate this to tennis.

Code: Select all

4. Concerning the piece of pseudocode... 
Unfortunately this paragraph means about as much to me as my explanation of baseball does to you. I'm not a programmer, just a guy who knows a little about spreadsheets trying too pull of what he considers a really cool project. Using a general purpose programming tool to me would be like those monkeys banging on a keyboard, and I've never even heard of an "IDE."

Gaah, I'm probably frustrating the stuffings out of you and I really don't want to do that. Your really pouring a lot of effort into this!
open office 4.1.2, windows 7
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

I just sent you a PM with more detail on the baseball explanation. Hope it helps you better understand what I'm trying to say.
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

...the spreadsheet itself is the RNG, using the randbetween function.
That's a technical RNG, and a very specific one: RANDBETWEEN(Nlow; Nhigh) is expected to return any integer number from Nlow through Nhigh (including both poles) with the same probability. What should a scene expecting equal probability for the 5 possible outcomes be good for? Is there any eveidence that this might be the case (approximately) in real baseball? Why should you want to simulate baseball under unrealstic conditions without any element to map the ability of the teams to?
Not sure what you mean by... "like a protocol of the game"...
I probably used the wrong word. Games played in contest, whether chess or football, darts or snooker, are minuted by someone. The result of this I meant by "protocol". Only a protocol sufficiently complete under the rules of the games allows for a subsequent evaluation.
...how to relate this to tennis
It's just another example for a game (with the difference that I understand the rules to a certain degree) - and tennis matches are minuted, of course. Actually I once "played" hundreds of tennis matches by program using a RNG to test some statistical conjectures about the game. I had to map the player's abilities to parameters in the program then.
And a question concerning the usage of spreadsheets: In what way and under what conditions shall the RNG be called to calculate the next random result?

Finally, and after I read your PM: I still do not understand what you want to achieve. Will the teams ever change pitch and bat? Will there be any scoring? ...?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

Lupp, I sent you another PM. Check it out!
open office 4.1.2, windows 7
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Using a macro as a function. Why isn't it working?

Post by Zizi64 »

-4- LibreOffice since (if I'm right) V 5.1 has loosened the same-sheet-restriction. The central properties 'String' and 'Value' of sister cells can be changed now. Concerning the 'Formula' property the only remaining restriction I know of is that it cannot be cleared (set to "").
It is very useful information for me. Thank you!
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using a macro as a function. Why isn't it working?

Post by Lupp »

@Zizi64: Please note, that I am just reporting about my experiences insofar. I do not know any specification telling if the new behaviour is guaranteed to be stable in the long run or even if it was introduced intentionally at all. Other properties (except the mentioned ones) seem to be still locked.

@imdumb and interested visitors:
I suppose you understand clearly now the facts (as I see them) listed below.
-1- You originally posted under the subject "Using a macro as a function. Why isn't it working?"
-2- You now know the same-sheet-limitation. And you were informed of the disadvantages and limitations of recorded macros.
-3- Even if the same-sheet-limitation is loosened, you cannot reliably control all the intended functionality by function calls.
-4- You will need form-control elements. (At least one OK-button in this case.)
-5- The needed code can be designed in very different ways. I cannot think, however, of a way not using the API to some extent.
-6- To maintain / enhance sheets containing such code you will need to study not only BASIC but relevant parts of the API.
-7- I can only give a crudly sketched example I wrote for fun. I wouldn't do it in quite this way for my own use.
-8- Documentation and some helpful texts are linked in below.

Links
An entrance to the LibreOffice API documentation (may be preferable now.)
Here you find the original links to the famous texts on programming for open Office in BASIC using the API.

And finally the above announced example:
Attachments
AsIfBaseball_2.ods
(13.77 KiB) Downloaded 440 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using a macro as a function. Why isn't it working?

Post by imdumb »

WOW.

That's awesome. At least I'm pretty sure it is. The effort you put into this definitely is!

Just one thing. The "TimerTick" thing is not needed. The RNG is triggered by another action that takes part in another part of the spreadsheet, which takes place after one pitch is thrown and, the way it should work is that this code you would be triggered only after that one pitch takes place. It would only be for one pitch at a time, not a never ending string of pitches, although your idea of doing that does bring up another really cool possibility to me.

So I'm wondering. How can this be modified so that when you click the button it only gives the results of one pitch at a time, instead of continuous pitches (and then when one of the limits is reached it will still delete the column then go back to the beginning)?

Thank you so much, and after that, I'm not really quite sure what to say! :bravo:

P.S. Would you be still open for questions on this later?
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using macro as a function. Why isn't it working?

Post by Lupp »

What I attached is neither a product nor a solution. Taken as such it woudn't be high quality due to the fact that it is not clearly structured, and that it uses different means for similar purposes. A global variable is used a as a redundant represantation of a state also represented by the button's label, Cells are used for passing factual parameters or in the role of global variables again in different ways, a DataArray of a range is introduced as a local variable once, and directly accessed as a property owned by a range in in another place...

The sheet and the code are a demonstration of means you can use. And as a demonstration that's of some value partly for the same reasons for what it isn't a good "solution". This, of course only, if you are ready to take it for what it is meant to be. I'm not a programmer or a developer. I'm a teacher.

(I will read your additional questions. If they are to come, I would prefer to see the state of what you are working on. The "real thing". Ask and attach what you have. Saves time. Forums aren't one way. Easy to point with the finger to what's the point. ... Of course, I reserve to leave the thread on my decision.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using macro as a function. Why isn't it working?

Post by imdumb »

Ok. That "same sheet limitation".

I was wondering why your example had all the random number generation, loop, putting numbers in column A, was being done. I think now I'm understanding, it's the "same sheet limitation." That's why I can't have one macro that does all these things in one macro and then another macro that deletes column A, so that's why you including all these extra things into one macro.

Am I correct?

If I am correct, would it be possible to have one macro that would run partially, then pause somewhere in the macro and allow you to input information (in this case, pitches generated by another macro) into it, and then continue running, until Column A filled up, and then the macro would delete Column A at the proper time?
open office 4.1.2, windows 7
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Using macro as a function. Why isn't it working?

Post by Lupp »

I generally am using LibO and there the mentioned limitation is substantally relieved. That's not actually the point.
The recalculation of formulas, and thereby also the execution of the code of user functions is controlled by a core part of the Calc software itself. This even if a recalculation is triggered explicitly by the user (F9, Ctrl+Shift+F9). Any action triggering the execution will not automatically also avoid repetitions under any circumstances. In specific functions with side-effects can easily (and inadvertently) create infinite loops. It's always dangerous to interfere with the recalculation queue. (You may study the threads featuring "time-stamps by formulas" or something alike to learn more about this.)

My central point is that simulations (of a baseball game, of a star-burst, or of an epidemc e.g.) are basically time-sequential. But Spreadsheets are not. Therefore the appropriate means for simulations by software aren't spreadsheets but general-purpose programming tools (language implemented by a compiler and mostly supported by an IDE nowadays). You won't be interested in a recommendation insofar, I'm afraid. And in fact you will need not only the running simulation, but also means to control it interactively to some degree, to present the results in current sate, and to save/reload specific states. A visual-component-library included with your IDE being the state of the art with this respect.

Since you feel familiar with spreadsheets, you may think of them as just a kind of display in the context, and also as a frame for user interaction and as a proper means of saving states. In addition a SheetCellRange or its DataArray may be used as a kind of global variable(s) of which the current values are pesristent in the saved file without the simulation needing to do anything extra. Visibility a gratis advantage where appreciated.

Actually a spreadsheet document, all cell formulas omitted, but FormControls and included user code (IDE: say poor old BASIC e.g.) extensively used, may be seen as a bundle of geeral purpose-programming, visualisation, and user-interface. This supported by some functionality of the Calc frame (represented by the application window).

What I tried for you with my previously attached example (AsIfBaseball_2.ods) was to demonstrate some means usable under this paradigm, and also how to loosen the omission of cell-formulas (slightly and with care!) in the context.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using macro as a function. Why isn't it working?

Post by imdumb »

Lupp, just sent you another PM
open office 4.1.2, windows 7
imdumb
Posts: 29
Joined: Wed Oct 04, 2017 3:31 am

Re: Using macro as a function. Why isn't it working?

Post by imdumb »

And...another PM.
open office 4.1.2, windows 7
HinckleyBob
Posts: 5
Joined: Sat Dec 22, 2018 4:32 pm
Location: Hinckley, OH USA

Re: Using a macro as a function. Why isn't it working?

Post by HinckleyBob »

Lupp, your post of aoo90624SideEffects_1.ods was extremely helpful to my learning, and a VERY VERY robust example, THANKYOU!
OpenOffice 4.1.6, Windows 10
Post Reply