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

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.

edit retag close merge delete

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.

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

Sort by » oldest newest most voted

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.

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.

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.

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

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

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

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

Does the help page on Using Procedures and Functions help?

more