0

I wrote a macro that originally had 0 arguments, and called the macro from a button on my Excel sheet. The design changed, and now I added two optional arguments to the macro, like this:

Function ButtonClick(Optional prop1 As String, Optional prop2 As String)
    `Do stuff here
End Function

I am now trying to pass both arguments to the macro from the button, but get an error each time.

First I tried:

Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))

as was suggested in this question, but got an error for That function isn't valid.

I noticed that in the question, the top answer also called for surrounding the entire macro name in single quotes, so I did so:

'Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))'

And this time got there error Formula is too complex to be assigned to an object.

What am I doing wrong?

Instant Breakfast
  • 1,383
  • 2
  • 14
  • 28
pavuxun
  • 411
  • 2
  • 15
  • 2
    Why have `ButtonClick` be a *function* rather than a *sub*? You are calling it as a sub (so using the outer parentheses in `Sheet1.xlsm!ButtonClick(EVALUATE("A3"), EVALUATE("A4"))` is a syntax error, it should be `Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4")`. In general, it seems that you might benefit from researching the difference between functions, subs, and how they are called. – John Coleman Mar 07 '19 at 18:33
  • Dont make it a function, make it a subscript, make the optional a range, and only 1 not two. All you have to do is, select the two cells and open the macro menu and click run. thats one way around it. – Ricardo A Mar 07 '19 at 18:34

1 Answers1

2

This worked for me:

'Sheet1.ButtonClick Evaluate("A3"), Evaluate("A4")'

With ButtonClick as a Sub. The single quotes are required.

Note this also works:

'Sheet1.ButtonClick [A3], [A4]'

since [] is a shortcut for VBA's Evaluate()

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • I tried this: `'Sheet1.xlsm!ButtonClick EVALUATE("A3"), EVALUATE("A4")'` with the same `Formula is too complex to be assigned to an object` error. When I tried `'Sheet1.ButtonClick EVALUATE("A3"), EVALUATE("A4")'` there was no error message, but the macro was never called – pavuxun Mar 07 '19 at 19:07
  • I got it to work by using just `'ButtonClick EVALUATE("A3"), EVALUATE("A4")'`. For some reason it didn't like the prefix of the sheet – pavuxun Mar 07 '19 at 19:26
  • 1
    Needs a period not `!` – Tim Williams Mar 07 '19 at 20:01