VBA Compile error: Argument not optional when calling a method from a COM AddIn

Question

I have the following code:

Sub CallVSTOMethod()
    Dim addin As COMAddIns
    Dim automationObject As Object
    Set addin = Application.COMAddIns("CssFillTool")
    Set automationObject = addin.Item
    automationObject.ButtonClearRemarks
End Sub

I am trying to find out why i am getting this error.

ButtonClearRemarks is one of many macros in the addin. when i click that macro seperately it works properly.

This macro does not require any argument. This on clicking will format my excelsheet. I tried passing empty arguments and other options as well, but no use. any suggestions are welcome.

After few corrections : Set addin = Application.COMAddIns Set automationObject = addin.Item("CssFillTool")

now i just need to access the macroButtonClearRemarks within this Add-ins

Answer 1

This is not tested so I will delete it if it is not correct.

From the comments I made it would seem like:

Sub CallVSTOMethod()
    Dim addins As COMAddIns
    Set addins = Application.COMAddIns
    Dim addin As COMAddIn
    Set addin = addins.item("CssFillTool")
    addin.ButtonClearRemarks ' cast addin into the correct variable type
End Sub

ButtonClearRemarks is one of many macros in the addin. When I click that macro seperately it works properly.

But I can't test as I don't have your addin. Since ButtonClearRemarks is not part of the base class I assume you need to cast the returned object into your addin so that the method is exposed.

You have not shown us the code for ButtonClearRemarks so we have limited knowledge to go on.

I also came across this where it suggests:

Dim objBaseObject As Object 
Set objBaseObject = _ 
 Application.COMAddIns.Item("CssFillTool").Object

So maybe you can do it that way.