0

I'm trying to find a way to pass params to a macro that's assigned to a button in Excel (2010). I've seen a number of proposed solutions in this thread but none of them work for me - maybe due to my setup?

I have a macro defined in an Add-In file (macros.xlam) that takes no parameters. I have added a button in my *.xlsm file and attached the macro to it. When I click the button macro gets called and it works fine. My macro is called Export and it's inside CSV module so in the button's property I'm calling CSV.Export.

I have added an int parameter to the macro so the definition looks like this now:

Sub Export(a As Integer)
    ....
End Sub

Now I want to make the button pass 1 as the parameter. I set a breakpoint in first line of the Sub and tried various approaches but none worked:

'CSV.Export 1' - when I click on the button nothing happens, breakpoint doesn't catch. Additionally when I edit the macro again it turns out that what I entered was automatically replaced by 'sheet_name.xlsm'!'CSV.Export 1'. If I leave it at that it still does not work.

'CSV.Export(1)' - when I click on the button nothing happens, breakpoint doesn't catch. This one does not get automatically replaced.

'macros.xlam'!'CSV.Export 1 ' - when I click on the button nothing happens, breakpoint doesn't catch. Upon later edit it turns out that it gets replaced by 'sheet_name.xlsm'!'CSV.Export 1'

'macros.xlam'!'CSV.Export(1)' - when I click on the button nothing happens, breakpoint doesn't catch. Upon later edit it turns out that it gets replaced by 'CSV.Export(1)'

CSV.Export 1 - when trying to close button property window displays an error that the formula contains an error and won't allow to leave it like that

CSV.Export(1) - this return an error saying that "Formula is too complex to be assigned to object".

I want my macro to be customizable (users being able to pass parameters to it). At the same time I want to avoid hacky hard-coded solutions like calling the sub with no params and then always assuming that cell A1 has first parameter, A2 has second and so on.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
ilozen
  • 139
  • 1
  • 1
  • 10
  • I don't understand so much your requirement. But how do you catch the parameter? You click the button and ask for the parameter? Or you set it by code the call of the macro? – Fernando Madriaga Jun 22 '17 at 17:02
  • You can't pass parameters. What you *can* do is read a value from a cell on the sheet, so your macro can retrieve parameters from a cell. – Ken White Jun 22 '17 at 17:23
  • Why can't you just use a click handler for the button? – Cody Ｇ Jun 22 '17 at 20:37
  • Use a global variable – Absinthe Jun 22 '17 at 21:29
  • @ Fernando Madriga - I want the parameter to be set in the button property along with the call to the macro. @ Cody G. - I don't understand what you mean by a 'click handler'. Do you mean an event sent when button is clicked? If so, how could I pass a parameter to that event and read it on the other side? – ilozen Jun 23 '17 at 06:30

0 Answers0