Creating a COM addin

[lukedays]

@cunnane, Another question!

Is it possible to create a COM addin with Python functions? I confirmed that using Application.Run works, but this doesn't offer Intellisense.

Thanks in advance!

[cunnane]

xlOil creates a COM addin on-the-fly when you call xloil.ExcelGUI because the Ribbon/Task Panes are not available from the XLL interface used to declare worksheet functions. Intellisense doesn't use COM - in fact the pop-up intellisense you see for built-in functions is not supported by Excel for user-defined functions, even if they are declared in a COM add-in. Govert of ExcelDNA fame built a C# addin which beautifully mimics Excel's built-in intellisense but can display help for user-defined functions. If you have this addin loaded, xlOil should find it and tell it about the functions you register. Let me know if this part isn't working!

[lukedays]

Alright! Can this xlOil COM interface be called through CreateObject in VBA?

[cunnane]

It could be, but I can't see how it would do anything useful.... I'm not clear why you want a COM interface or to CreateObject things?

[lukedays]

Oh, it's just to have a cleaner syntax, instead of doing:

Application.Run "Func1", 1, 2, 3
Application.Run "Func2", 1, 2, 3

Do:

Dim addin as Object
Set addin = CreateObject("XlOil")
addin.Func1(1, 2, 3)
addin.Func2(1, 2, 3)

In ExcelDna for instance, the first usage doesn't display the errors/exceptions correctly. But I think xlOil wraps the function to display Python exceptions. So it's just to have a cleaner syntax.

[cunnane]

Ah.... I see what you're after now. The "intellisense" I was referring to is the one that pops up in the worksheet, not the VBA one

Note the object referred to in CreateObject doesn't have to be an Excel COM add-in, any COM library would do: a suitable COM library could be created without xlOil by using pywin32.

I think it would be possible to have xlOil create a suitable add-in and make all the registered (synchronous) worksheet functions available. It will be a bit of work though, since using COM is rarely straightforward! I'll look into it....

[lukedays]

Ok I see! No big issue, using Application.Run is alright. It's just to know if I'm missing something. The purpose of this is to attach the functions to Forms/ActiveX buttons. And yes, messing with COM seems scary... Thanks a lot!! Em qua, 24 de ago de 2022 09:40, Steven ***@***.***> escreveu:

…

Ah.... I see what you're after now. The "intellisense" I was referring to is the one that pops up in the worksheet, not the VBA one Note the object referred to in CreateObject doesn't have to be an Excel COM add-in, any COM library would do: a suitable COM library could be created without *xlOil* by using *pywin32*. I think it would be possible to have *xlOil* create a suitable add-in and make all the registered (synchronous) worksheet functions available. It will be a bit of work though, since using COM is rarely straightforward! I'll look into it.... — Reply to this email directly, view it on GitHub <#4 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAC6OU53HQZTXPQHMFZ4TR3V2YJ3NANCNFSM57LPEBXA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[cunnane]

I think it's possible to have the syntax you want but the intellisense may not work as expected. If you write Dim foo as Object: Set foo = CreateObject(...) you wont get auto-complete, you need to write Dim foo as SpecificObject: Set foo = .... VBA then provides auto-completion by looking up the type library for SpecificObject in the Windows registry. See the discussion here

For xlOil, this type library would not exist as the exposed functions are dynamic, so whether the auto-complete works comes down to how exactly VBA is doing the lookup. If you are prepared to accept the limitation that the exposed functions are static for the duration of the Excel session, it should be possible for xlOil to use pywin32 to generate a suitable type library at start-up.

[lukedays]

This could be interesting! Yes you're right, for VBA autocomplete to work we need the type library. What about a ribbon button (like xlwings has) to refresh both the Python functions and the COM types? Maybe it could have other configuration options as well. About the COM types, I'm not sure if it's possible to dynamically regenerate the types (found this from Govert https://groups.google.com/g/exceldna/c/-eF16GS4PZA). But a static library at startup seems good enough. Em qui, 25 de ago de 2022 07:23, Steven ***@***.***> escreveu:

…

I think it's possible to have the syntax you want but the intellisense may not work as expected. If you write Dim foo as Object: Set foo = CreateObject(...) you wont get auto-complete, you need to write Dim foo as SpecificObject: Set foo = .... VBA then provides auto-completion by looking up the type library for SpecificObject in the Windows registry. See the discussion here <https://docs.microsoft.com/en-us/previous-versions/office/troubleshoot/office-developer/binding-type-available-to-automation-clients> For xlOil, this type library would not exist as the exposed functions are dynamic, so whether the auto-complete works comes down to how exactly VBA is doing the lookup. If you are prepared to accept the limitation that the exposed functions are static for the duration of the Excel session, it should be possible for xlOil to use *pywin32* to generate a suitable type library at start-up. — Reply to this email directly, view it on GitHub <#4 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAC6OU2XZIUE3IGWJJHIQITV25CRDANCNFSM57LPEBXA> . You are receiving this because you authored the thread.Message ID: ***@***.***>