[Solved] Create new function with recorded macro?

[robbyn]

I find it very difficult to follow some of the guidance on basic macros. I end up being very muddled and adverse to continuing to read the advice.

I wondered if there is a template that is available for me to copy and paste a recorded macro into a draft function so that it could be called by other functions in a spreadsheet.

If you ask me to give an example of the macro. any recorded macro will do. I just want to be able to run the recorded macro via a function. Reason: other functions can be easily set to trigger the macro if it is wrapped in a (new) function eg

Rem new function = runontime()
=IF( a>b, runontime(), 0)

[FJCC]

Functions cannot modify cells other than the one from which they are called. I think this will prevent using a function in the way you want. I ran a quick test to confirm this but may be someone knows a way around that problem.

[Villeroy]

runontime.ods

(12.3 KiB) Downloaded 184 times

[Lupp]

I also can hardly imagine a case where it might be reasonable to call a recorded macro from a user function.
However I reworked the example by @Villeroy (not quite slightly) to exemplify real-world problems occurring in the context.

Explanation: From my point of view using functions for calling user SubRoutines mainly (exclusively?) lies in the convenient way to pass parameters. The relevant limitation is that cells of the calling sheet can't be manipiulated this way.
Some versions of LibreOffice had very much relieved the prohibition of such action, but recent versions again are strict concerning this rule. I don't know for sure what caused this change of mind.

aoo105682CallSubByFunction.ods

(17.76 KiB) Downloaded 156 times

[Villeroy]

He wants to run a macro (which performs a paste-special) once a day at a given time of day (in order to save a single click per day). Much trouble about nothing.

Edit: If I remember correctly, the sheet content is locked until the entire calculation cascade is finished, so the whole thing will not work with his recorded paste-special macro.

[Lupp]

He wants to run a macro (which performs a paste-special) once a day at a given time of day...

How do you know? Where would the clipboard content come from?

Anyway, autodidactic studies might better start with some reading. Realistically it is more likely to be done the example-based way, and studying the fundamentals comes failure-induced.

Remains the question if attempts to learn that way should be supported here.

As far as I am concerned, It's a strange thing, because I feel a kind of "need to write a little program" now and then nearly independent of any intention (not to speak of need) to use it. A mental disorder probably.

[Villeroy]

The drama started here: viewtopic.php?f=9&t=105554

It's all my fault. I should not mention macros when there is no need to run some.

[robbyn]

Villeroy:
It took me some time to understand how to bring in a recorded macro, but I have it working well.

The logic in your sample spreadsheet is really important for many people who can not program in openoffice basic but need actions to take place in response to a change in their spreadsheet. It avoids the need to understand how to put a macro together in detail, and also can easily make a macro conditional.

Thank you, and all who have commented on this thread.