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

Are you sure that it isn't the line above which is throwing the error? `Item` sounds like a method which requires an argument. — John Coleman, Jul 30 '18 at 10:22
@JohnColeman Yes ! i changed the code as: Set addin = Application.COMAddIns Set automationObject = addin.Item("CssFillTool") Now this does not throw any error. But now i don't know the syntax to call the "ButtonClearRemarks" macro. the next line is now throwing error : object does't suppot this property or method — gowtham, Jul 30 '18 at 10:30
The help says "Required Object. Either an ordinal value that returns the COM add-in at that position in the COMAddIns collection, or a String value that represents the ProgID of the specified COM add-in." And it looks like it returns a `COMAddIn` . This is not VBA but should give advice: https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.core.comaddins.item?redirectedfrom=MSDN&view=office-pia#Microsoft_Office_Core_COMAddIns_Item_System_Object__ — Andrew Truckle, Jul 30 '18 at 10:44
So, shouldn't you assign `addins` as the return value of `Application.COMAddIns()` and then access the one you are interested in via the item index? — Andrew Truckle, Jul 30 '18 at 10:46
Try using “call” use Call automationObject.buttonClearRemarks — , Jul 30 '18 at 12:26
@TanmayGawankar No! this is throwing an error _Object doesn't support this property or method (Error 438)_ . Not only if i use _call_, also if this is used_automationObject.buttonClearRemarks_ — gowtham, Jul 30 '18 at 12:50
@AndrewTruckle As of your first comment, is there any example code in VBA! i couldn't understand it exactly . But still that link looks more promising — gowtham, Jul 30 '18 at 12:52
See if my answer helps. I don't know what environment I need to try it out for you. — Andrew Truckle, Jul 30 '18 at 13:15

Andrew Truckle · Accepted Answer · 2018-07-30T14:05:18.330

0

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.

edited Jul 30 '18 at 14:05

answered Jul 30 '18 at 13:14

Andrew Truckle

17,769
16
66
164

I get the following error _Method or data member not found (Error 461)_ for the last line of the code – gowtham Jul 30 '18 at 13:25
@gowtham Exactly, because that *macro* is not part of the `COMAddin` class. Show us the definition of that macro ... – Andrew Truckle Jul 30 '18 at 13:29
So does the error : **Run-time error '91' :Object Variable or with block variable not set** also means the problem is with the COM Add-ins Created – gowtham Jul 30 '18 at 14:40
I would like to send you the code. Is there any other option to send you than uploading it here directly? – gowtham Jul 31 '18 at 08:22
Not sure. You have not shown us anything about this `ButtonClearRemarks ` method. – Andrew Truckle Jul 31 '18 at 09:41
yes! regarding that i can send you the VSTO file containing the code. Is there any other option available to send you? – gowtham Jul 31 '18 at 09:54
@gowtham If you provide a skype user name I can connect that way and you send me the file. – Andrew Truckle Jul 31 '18 at 11:01

David Zemens · Answer 2 · 2018-07-30T13:57:17.390

0

Assuming the Add-in in question is COM Visible and the method is accessible, according to this other accepted answer (skip the first 2/3 of the answer which mocks up a simple COM Add-in in C# code), you're handling the Add-in incorrectly.

Sub Test()

    Dim addin As Office.COMAddIn
    Dim automationObject As Object

    Set addin = Application.COMAddIns("CssFillTool")
    Set automationObject = addin.Object  '<~~ handle the add-in's Object property

    automationObject.ButtonClearRemarks

End Sub

edited Jul 30 '18 at 13:57

answered Jul 30 '18 at 13:43

David Zemens

53,033
11
81
130

yes, the _automationObject is set to nothing in that line_ i don't know what to do here – gowtham Jul 31 '18 at 07:28
I'd expect a subscript out of range error if CssFillTool isn't a member of the COMAddIns collection, ok. are you sure your AddIn is COM visible? – David Zemens Jul 31 '18 at 11:57
addin is = Cssfilltool, but the : _addin.object = nothing_. which is set to automationobject – gowtham Jul 31 '18 at 12:07
can you refer to the other answer to see how to configure the vsto add-in perhaps? – David Zemens Jul 31 '18 at 12:14

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

2 Answers2