|
Han Raas
Posts: 2
Joined: 2021-02-03
|
Dear Sir/Madam,
First some background.
Let me introduce myself. My name is Han Raas. I am a Dutch chemical engineer and owner of the company Enssim Software, having used Pascal and later Delphi, for development of process engineering software starting from the year 1987. The last ten years I also have deployed Delphi to produce common dll's containing the aforementioned software to be deployed in an Excel environment. These dll's contain functions and procedures that are imported in Excel by the usual VBA Declare statements. However, I want to get rid of VBA-based dll-functions importing interfaces for my dll's. As far as I know this is possible by developing COM-Addin's for which I use AddIn express for VCL (since January 2021)
Problems
I have developed a small and simple COM-addIn dll to test the use of Com-AddIns within Excel. Result up til now: I can use the (COM addIn) functions on a worksheet level but the name of a particular function does not show-up in the functions list created after having typed "= plus one or more characters of the name of a particular function" (problem no. 1)
Another, even worse problem is that the function is not recognised by any VBA code calling the function (or subroutine) This leads to a compilation error in VBA. (problem no. 2)
What actions should I take to solve the problems mentioned above?
(Delphi Version : 10.4.1
Office version: Microsoft 365)
Met vriendelijke groet/ Best Regards,
Han Raas |
|
|
Andrei Smolin
Add-in Express team
Posts: 18829
Joined: 2006-05-11
|
Hello Han,
To use a function in an Excel formula, you must use an Excel add-in, not a COM add-in. Although Add-in Express supports creating an Excel Automation add-in, such add-ins do not show their names in the Excel UI; I assume this is the way Excel works. You may want to use an XLL instead. As far as I know, XLL functions show their names and descriptions but you should be ready to other difficulties. One of them is: we don't recommend using the Excel object model when in the context of an XLL call. That is, when Excel invokes your function you should not use the Excel object model (such as Excel.Range objects) in your code.
To invoke a custom function in VBA, you can:
- use a COM add-in so that the VBA macro instantiates the COM add-in object and invokes the function
- use an Excel add-in so that the VBA macro uses Application.Evaluate to invoke the function as part of a formula
Andrei Smolin
Add-in Express Team Leader |
|
|
Han Raas
Posts: 2
Joined: 2021-02-03
|
Hello Andrei,
My reply:
In the first place thank you very, very much for your swift reply!
Quote 1: " Although Add-in Express supports creating an Excel Automation add-in, such add-ins do not show their names in the Excel UI;"
This is really very, very disappointing. The main reason why I bought a license for AddIn Express was to avoid the VBA Declare statements for importing the process engineering functionality from my Delphi compiled dll's.
Quote 2 :
"To invoke a custom function in VBA, you can:
- use a COM add-in so that the VBA macro instantiates the COM add-in object and invokes the function"
Could you give a simple example for such an instantiating?
Is it possible to use the COM-add-in functions in Excel VBA-code, after such instantiating?
Met vriendelijke groet/Best Regards
Han Raas |
|
|
Andrei Smolin
Add-in Express team
Posts: 18829
Joined: 2006-05-11
|
Hello Han,
Han Raas writes:
Quote 1: " Although Add-in Express supports creating an Excel Automation add-in, such add-ins do not show their names in the Excel UI;"
I've put that incorrectly. Here's a correct version. A function added by an Excel Automation add-in is seen in the Insert Function dialog. What you can't see (and do) is the function description; this is by Excel design. Also, there're no descriptions on the function parameters parameters. Such a function can be used in the VBA code via Application.Evaluate({formula here}).
As to #2, a COM add-in is a COM DLL. A way to get the instance of the add-in in VBA is: Application.COMAddins("progId of your COM add-in here").Object. The last property returns an object representing the public UI of your add-in module. You use late binding to access its properties and methods. To use early binding, you add a reference to that DLL in your VBA project and use it.
I assume VBA Declare is required if you use a non-COM DLL (for example, you use Windows API functions in this way), correct?
Andrei Smolin
Add-in Express Team Leader |
|
Forums
Topics
Search
Help
Login
Register