[Calc] How to start a macro with user-specified parameters?

[mathematicool]

Hi,

Got a Calc question:

I’ve just started creating macros and I was wondering how you can run a macro with parameters.

For instance, I have created a macro which copies a cell into a specific column and the parameter of the macro is a reference to the cell you wish to copy. Now I have created some buttons and I want it so that when I click a button a cell specific to each button is copied, e.g. if I click button 1 then A1 is copied, button 2 then C4 is copied. However, I can’t find a way to assign the macro to the buttons and pass a parameter (i.e. the cell) to the macro. Currently, the only way I can see to do this is to create a separate macro for each and every button (which would take a lot of time) and each macro would then call the copy macro with the correct parameters. Is there a better way to do this.

Thanks in advance.

[Lupp]

(Coming accross this thread accidentally:)
The question is more than 4 years old now.
To close it based on an answer explicitly (and rightly) not accepted by the questioner as @AlexKemp did, was not correct, however. I therefor reopen the thread.

[Lupp]

How to pass parameters to a Sub if not called from user code is a reoccurrent task.
I did not perform an extensive research, but just list some ways I go now and then myself.

-1- Wherever possible I call the Sub via a user Function called by the formula of a cell.
The function can then take the needed parameters directly and pass them to secondary routines.
The body of the function may even do everything needed then without calling a Sub.

-2- Calling a Sub via Tools > Macros > Run Macro... or via a controll added to a toolbar or to a menu does not offer any way to pass parameters.
The only ways I know to do something similar:
-2a- The Sub uses a dedicated cell range (a kind of a cache) to take the factual parameters from.
-2b- A previously called Function or Sub deposited the factual parameters in Global Variables. (A cache again.)

-3- Calling a Sub by clicking an OK-Button (or a different form control) and just wanting to choose one of the ways through the Sub depending on the specific one of some similar buttons that was clicked I use either the Label or the Tag (‘Additional information’) or both of these properties of the Control to manage the action (by a Select Case … e.g.). In this case the underlying parameter is the Event Object passed automatically to the called Sub.

-4- Finally (?) a Sub can be called using a hyperlink either created directly or with the help of the HYPERLINK() function.
The href of the link itself looks like the example vnd.sun.star.script:Standard.Module1.mySub?language=Basic&location=document where location can also be application instead of document. The Sub should then have one formal parameter which gets passed the complete href as a string. The href may then have an appendix passing the parameters you actually want to evaluate. In the few experimental cases where I used this way I chose a counstruct like
vnd.sun.star.script:Standard.playGround.tellLink?language=Basic&location=document&?p1=100&p2=3.14E0 where the “&?” splits off the part actually used to pass parameters.
If needed I canj supply an example .ods.
See also: [Solved] Calc basic macro to add a button to a cell (View topic) • Apache OpenOffice Community Forum

Edit1:
Concerning the method -3- I now supply this example containing a tiny toolbox of functions for the purpose and a Sub demonstrating how to use it.
(A simpler version I had made as an attchment to an answer on a recent question.)

[erAck]

Regarding -1- let me point out that in a formula expression calling a user defined spreadsheet function macro that attempts to modify the document while being interpreted, specifically if it is about to modify cell content that is used elsewhere in calculations, is almost never a good idea. In such cases better let a macro obtain its arguments from cell values as in -2- or by other means.

[Lupp]

See this answer in another forum for a case where the way -1- should not be expected to cause problems (though you can force some).

[erAck]

That example doesn’t modify cell content that could interfere with calculation chains, applying attributes usually is ok. The OP case copies a cell into a specific column sounds more like one of those may work by accident cases.

[Lupp]

You are right. I was referring to the more general aspects of my answer. In fact I may link to this answer if there be similar questions with different specialties in the future. Don’t want to write a precisely targetted answer in every case. In addition there are many cases where -1- is applicable. User has to take responsibility, of course. I don’t know the context to enough detail mostly to base an answer-to-the-point on. Users asking for macros should expect complications anyway.

[torreone]

In this discussion the problem arises in the context of calc, but there is another option that can certainly be used also in writer, namely to execute a user macro by customizing the toolbar and / or the contextual menu.

The context menu as well as being able to insert new entries in the root has the additional advantage of being able to create a hierarchy of submenus, each of which can be associated with the call of a user macro.

Furthermore, being the contextual menus different according to the context in which they are open (text, table, print preview, toolbar, etc.), the possibilities to customize each menu with different user functions is remarkable.
Customizing the context menu in writer recently solved a problem on which I had opened a discussion that remained unanswered, namely the management of mouse events in writer.

The initial purpose was to run different user macros according to the position of the mouse in the document, the context, the function to be performed

Apparently neither from the toolbar nor from the context menu it is possible to execute macros by passing parameters, or, at least, the event object.

However, the most important parameters, for example the mouse coordinates and the area of ​​the current document, it is not possible to execute on call neither parameters nor the event object without causing this scripting error.

vnd.sun.star.script: Standard.ggWriter.wrtDocEv? language = Basic & location = application.

Message: The following Basic script could not be found:
library: ‘Standard’
module: ‘ggWriter’
method: ‘wrtDocEv’
location: ‘application’

But if you access the viewCursor in the macro called, you will reconstruct both the coordinates of the point where the contextual was called with a right click and, consequently, the portion of the surrounding text (sections, tables, text, etc.)
This simple user macro, for example, selects the paragraph in which the contextual menu has been activated

sub wrtDocEv ()
   dim a, oD, oVC
   oD=ThisComponent
   oVC = oD.currentController.viewCursor
   oD.currentController.select (oVC.textParagraph)
end sub

an interesting use can be to use user macros in a submenu of the contextual menu to

1. initialize the contents of an array or a collection declared as global

2. add an item (e.g. section, table, paragraph) associated with the current view cursor in array / collection

3. process the content of the elements saved

A second use is to search additional info on the current selection (e.g. the meaning, a translation, etc.) possibly using an input box in the macro to further specify the function to be performed

But the applications can be many, facilitated by the immediacy of the use of the contextual menu and by the possibility to customize it

Thanks to @lupp for having reopened this discussion with related contributions, very interesting and thanks to those who opened it and participated in it

Edit: mini-tutorial more accurate

In the following we will call PX the point in the document where the context menu was opened

• Use a wrtEv user function that initially contains the statement
  oVC = thisComponent.currentController.viewCursor
  oVC is the reference to the viewCursor
• Enter wrtEv both in the contextual menu (hereinafter mcText) associated with the “text” (the contextual that opens with a right click on a text segment at one point) and in the contextual menu (hereinafter mcTable) associated with the “table” , the specific menu contextual with right click inside a table

This done, you can now access everything a viewCursor has access to:

1. with oVC. textParagraph directly accesses the paragraph containing the PX point

2. with oVC.location the X and Y coordinates of PX are obtained

3. the section (if any) containing PX is located in oVC.textSection

4. the table and the cell containing PX (if existing) are in oVC.textTable and oVC.cell

There is therefore no need for a keystroke to call up the macro, it is activated directly from the context menu which, if desired, can contain submenus with hierarchies of user macros, each of which can perform different functions in relation to the position of PX and the relative text Content

[oweng]

Does the help page on Using Procedures and Functions help?

[mathematicool]

Unfortunately not. It only says how to call macros with parameters from within other macros. I basically just need to find some way of user specified input to the macro.