Ask Your Question

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

asked 2014-03-14 02:10:26 +0200

mathematicool gravatar image

updated 2014-03-14 23:16:52 +0200

oweng gravatar image


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.

edit retag flag offensive close merge delete



(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 @Alex Kemp did, was not correct, however. I therefor reopen the thread.

Lupp gravatar imageLupp ( 2018-04-16 15:37:32 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2018-04-16 17:03:02 +0200

Lupp gravatar image

updated 2018-05-20 22:35:06 +0200

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 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 where the "&?" splits off the part actually used to pass parameters.
If needed I canj supply an example .ods.
See also:

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.)

edit flag offensive delete link more


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.

erAck gravatar imageerAck ( 2018-04-16 19:04:41 +0200 )edit

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).

Lupp gravatar imageLupp ( 2018-04-17 21:52:48 +0200 )edit

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.

erAck gravatar imageerAck ( 2018-04-18 12:23:34 +0200 )edit

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.

Lupp gravatar imageLupp ( 2018-04-18 12:32:53 +0200 )edit

answered 2020-05-17 17:10:18 +0200

torreone gravatar image

updated 2020-05-17 21:40:39 +0200

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. 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
   oVC = oD.currentController.viewCursor (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 ...
edit flag offensive delete link more

answered 2014-03-14 05:28:11 +0200

oweng gravatar image

Does the help page on Using Procedures and Functions help?

edit flag offensive delete link more


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.

mathematicool gravatar imagemathematicool ( 2014-03-14 05:44:35 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2014-03-14 02:10:26 +0200

Seen: 2,950 times

Last updated: May 17