VBA error 'Wrong number of arguments or invalid property assignments' when running macro via custom button

Question

I have a macro that I call via a tab/group/button added by the Custom UI Editor -

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab id="tabCustomActions" label="Custom ActionsXXX" insertAfterMso="TabDeveloper">
                <group id="GroupTLA" label="TLA Actions">
                    <button id="buttonFormatTLA" label="Format as TLA" image="TLALogo" size="large" onAction="start_tla" />
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

The button shows up just fine, with my custom logo, but when ever I click the button I get the follow error -

VBA Error

VBA is not opened following this error, as it usually is, and no code within VBA is indicated as the problem if I open the developer console and then try and click the button.

Strangely though, if I try and run the macro manually, it works fine with no errors. Does anybody have any ideas how to solve this?

Here is my full code in a Pastebin, should you wish to view it. Thanks.

The VBA IDE doesn't open because you're not running VBA, this is a macro, right? What code is running in VBA when you 'run it manually'? — Brad
Brad: in Excel and the other office products, VBA = macro. Same thing. — Steve Rindsberg

Olle Sjögren Olle Sjögren · Accepted Answer · 2013-03-14T12:50:29

You have the wrong call signature for the start_tla callback in your VBA code.

If you open up your file in Custom UI Editor, there is a button called Generate Callbacks to the right in the menu. If you press it, it will give you the correct callbacks for your VBA code to match the ribbon xml in your file:

'Callback for buttonFormatTLA onAction
Sub start_tla(control As IRibbonControl)
    'Your code goes here
End Sub

According to the Pastebin link, your sub looks like this, without the control As IRibbonControl parameter:

Public Sub start_tla()

VBA error 'Wrong number of arguments or invalid property assignments' when running macro via custom button

1 Answers