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

2 Answers

Sort by » oldest newest most voted

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

Lupp gravatar image

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:

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

1 follower


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

Seen: 1,172 times

Last updated: Apr 16