VBA Pass arguments with .onAction

Question

this is how my sub looks like:

Sub InsertRowWithContent(rowNo As Long)

This is my .onAction:

.OnAction = "'InsertRowWithContent""" & C & """'"

C is a Long variable declared earlier.

It says macro not found. It worked fine before adding an argument!

Bustamante

I should have asked first. The background of my answer is Access. Djikay answers you from Excel point of view. The tag in your question is Word. So these applications may significantly differ in those details. Which application do you use, and which version? - And another test: can you call InsertRowWithContent(123) from the "immediate window"? Maybe it's in a range not visible (e.g. other sheet/doc and private). – Medick

I'm using word 2010. What do you mean by calling it from the immediate window? – Bustamante

I'm calling it from the same module if that's what you mean. I have a button in a word document which when clicked displays a popup menu which is populated by items from an excel sheet. When one of these menu items is clicked, i.e. when their .onAction is activated, they're supposed to call the insertRowWithContent method while passing an argument corresponding to the item number. – Bustamante

I've found a solution that seems robust. Have a look again. Good luck :-) – Medick

Answer 1

I have sucessfully passed arguments with this syntax:

.OnAction = "=InsertRowWithContent(" & C & ")"

Considerations:

• C is a long. So don't add quotas, just as you wouldn't if you would call the Sub in the code.
• OnAction evaluates an expression. Therefore according to my experience, the expression needs an equal sign, and as far as I now, it must be a function. Only automated local callbacks are subs.

EDIT

My answer above has Access as background. Djikay's answer works fine with Excel. Yet after some digging, I am quiet sure that simply Word doesn't understand either of those syntaxes. Word VBA cannot pass a parameter to a sub in an OnAction statement. At least for the moment it's best to accept this.

But here is what definitively runs with Word (2010):

Create your command bar and the button. For the OnAction, only pass the name of the Sub. But use the button's Parameter property.

' Add bar
Set cdb = Application.CommandBars.Add("SomeTest", , False, False)
cdb.Visible = True

' Add button
Set cbb = cdb.Controls.Add
cbb.Caption = "PressMe"
cbb.OnAction = "TheCallback"
cbb.Parameter = 456

Then, access the parameter by the CommandBars.ActionControl.Parameter expression:

Public Sub TheCallback()

  MsgBox "The parameter passed is: " & CommandBars.ActionControl.Parameter

End Sub

ActionControl is very similar (if not the same) as ActiveControl under Access: it is the control that was clicked last.

Source: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24982922.html

*phuu* :-)

Medick

I don't get it. There's gotta by something else wrong with my project. It doesn't work either. – Bustamante

Seems like this will actually work. Unfortunately I'm not near my PC now but will try it in 30 minutes or so when I get back. I had actually read about this parameter property but I didn't figure that you had to access it exclusively, I thought it was just an alternative to putting the argument between parentheses. – Bustamante

dude you are the maaaaaaaaaaaaaaan!! I've been at this for hours!! I owe you one brother! – Bustamante

Glad to help :-) ...I was also surprised what a maze this event handling is, compared over several Office programs. Good luck... – Medick

Man... You just saved me!!! I called VBA method from a button (in menu) created in .NET. I managed to call it, but... it was called twice instead of once for some reason. Your method just saved me! Now it is called once! :) – Ganister

@Ganister Glad to help :-) – Medick

As it turned out, there's more than that... After that I created new file and passed arguments like btn.OnAction = "'MyMethod 1, ""data""'", and... it worked! However, in another file it didn't work! I mean, in some files it works and in others - it doesn't. I figured it out that it's not connected with Trusted Locations in any way. – Ganister

Answer 2

You need to add a space after your Sub name, like this:

.OnAction = "'InsertRowWithContent " & C & "'"
                                  ^^^^^^^^^^

EDIT

Also, since you're passing a Long parameter, you shouldn't enclose it in quotes.

EDIT 2

OK, this will require some special sauce (code). I've been experimenting and I got the following to work.

In the Sheet1 Excel object:

Option Explicit

Sub DestroyToolbar()

  Application.CommandBars("DoomBar").Delete

End Sub

Sub MakeToolbar()

  Dim C As Long
  C = 100

  With Application
    .CommandBars.Add(Name:="DoomBar").Visible = True
    With .CommandBars("DoomBar")
      .Controls.Add Type:=msoControlButton, ID:=2950, Before:=1
      With .Controls(1)
        .OnAction = "'PressMe " & C & "'"
      End With
    End With
  End With

End Sub

In a new standard code module, add this:

Public Sub PressMe(C As Long)

  With Application.CommandBars("DoomBar")
    With .Controls(1)
      MsgBox "The value of C that was passed to this macro is: " & C
    End With
  End With

End Sub

If you run MakeToolbar, it will create a new toolbar in the "Add-ins" ribbon. To remove it, you can run DestroyToolbar.

Once the toolbar is in place, then clicking the button should display a messagebox with the value of C (in my example, 100).

I've tested the above in Excel 2010 and it worked, in a brand new .xlsm file.

Strobotron

Didn't work. I think I tried that one before actually. – Bustamante

@Mohamed: I've added another update with full code this time. – Strobotron

Well, your code works when I copy it into a new xlsm file, but when I try to use the .onAction line it still gives me a "macro not found error". Possibly there is another problem apart from the code? Both the subs (the one setting the .onAction and the one called by .onAction) are in a standard module. – Bustamante

I didn't take too much risk of adding a toolbar.. but the SPACE thing in first line saved me... Thanks man!! – Atalante