COM seems not to be registered

Add-in Express™ Support Service
That's what is more important than anything else

COM seems not to be registered
 
Subscribe
ewessely




Posts: 25
Joined: 2019-01-31
Hi,
it seems I'm having troubles with COM registration in "Per User" installation.
On my development PC everything is fine, but when you deploy the Excel Addin and you look in VBA Code, the reference is not available in the list. It seems that's not a problem of a specific project.

Steps to reproduce:

Take the sample project ComAddinWithEvents from Andrei
Add installer and create setup project (in my case I use the Microsoft Visual Studio Installer Projects)
Create setup (e.g MSI)
Deploy to different PC
-> no error in logfile
-> the Execl file comming with the sample will fail because of missing reference

what I'm missing in my setup?

BR
Erich
ew
Posted 08 Nov, 2019 08:42:49 Top
Andrei Smolin


Add-in Express team


Posts: 16607
Joined: 2006-05-11
Hello Erich,

I confirm the issue.

For this to work out of the box, the .TLB file of your add-in (it's created when you register the add-in project on your development machine) must be located on the same folder path on all machines. This is hardly possible. The only way to solve this is to modify the references of the VBA project. To do this, you must start Excel (as this example is an add-in for Excel), and construct this call (VBA):

Dim rf As VBIDE.Reference
Set rf = {a Workbook object}.VBProject.References.AddFromFile({the .TLB file})


This requires that the VBA project executing this call has reference to "Microsoft Visual Basic for Applications Extensibility 5.3". You may also execute this call from an add-in. In this case, the add-ins needs to have a reference to Microsoft.Vbe.Interop.dll.

This isn't all, though. To be able to execute that call, you must have this check box selected: File | Options | Trust Center | Trust Center Settings | Trust access to the VBA project object model.

For completeness. On the web, I've found a suggestion to use VBProject.References.AddFromGuid rather than VBProject.References.AddFromFile. This doesn't work in this case, though. I suppose the reason is: that method expects to deal with ActiveX libraries. Such an ActiveX DLL would be created by VB6 should we be able to use it. In this case though the type library is provided as a .TLB file, not as part of the ActiveX DLL. Also, in this case the Guid points to adxloader.dll, not to .TLB. Finally, you can't register the .TLB file (say, as part of deployment). So, VBProject.References.AddFromFile seems to be the only way you have.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Nov, 2019 04:38:04 Top
ewessely




Posts: 25
Joined: 2019-01-31
Hello Andrei,
many thanks for your reply!
Please correct me if I'm wrong, but without having the reference it's not possible to declare a public variable WithEvents as we don't have the type. So running the registration in the workbook is too late.

What exactly happens in IDE when I "register" / "unregister" the addin. One step is to create the tlb, but how do you register it? Is there no way to do the same registering of the tlb in code during any of the startup events of the Addin?
In ExcelDNA it's no problem, so it seems there should be a way to do so.

br
Erich
ew
Posted 11 Nov, 2019 07:20:12 Top
Andrei Smolin


Add-in Express team


Posts: 16607
Joined: 2006-05-11
Hello Erich,

The problem is not in the add-in registration. The problem is in the path to the type library. I suppose it would work with no issue if the add-in was an ActiveX library; in this case the type library is part of the ActiveX DLL and you can use AddFromGuid(). In your case, the type library is in a .TLB file and you can only use AddFromFile() in this way introducing a dependence on the actual path used.

ewessely writes:
Please correct me if I'm wrong, but without having the reference it's not possible to declare a public variable WithEvents as we don't have the type. So running the registration in the workbook is too late.


Sorry, I don't understand. If a variable is of the type Object, you can't declare it WithEvents. What is "running the registration in the workbook"?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Nov, 2019 08:12:55 Top
ewessely




Posts: 25
Joined: 2019-01-31
Hello Andrei,
is the idea to call the References.AddFromFile to be called within the AddIn? If so, how can I do that? And further - if so - when starting excel I don't have a worksheet (it's only grey background) so I would not have a worksheet object which I could use.
I'm confused :~|

Maybe you could modify your sample "com-addin-with-events" and the included xls to show your suggestions?

br
Erich
ew
Posted 11 Nov, 2019 09:58:52 Top
Andrei Smolin


Add-in Express team


Posts: 16607
Joined: 2006-05-11
Hello Erich,

ewessely writes:
is the idea to call the References.AddFromFile to be called within the AddIn?


Not THE add-in, but AN add-in! Actually, you can even use an EXE automating Excel and adding that reference. One more way is to use OpenXML to modify the .xlsx file directly (without starting Excel); I assume this is possible to do but I don't know any detail.

ewessely writes:
when starting excel I don't have a worksheet (it's only grey background)


Workbook, not worksheet. And we don't talk about what Excel displays. We talk about the object that you operate. Say, you may set Excel.Application.ScreenUpdating = false, open any workbook, and use the corresponding Workbook object to perform that call.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 11 Nov, 2019 11:45:29 Top
ewessely




Posts: 25
Joined: 2019-01-31
Hello Andrei,
many thanks for your reply.
Sure you are right - workbook not worksheet....

But I think having requirements in the trustcenter is a showstopper in a commercial AddIn...

Can't we do the same as you do when registering the Addin via Visual Studio? If doing so, the library shows as available reference in VBA.
I think there should be a solution, because ExcelDNA has no problem with the registration.

What's exactly done when registering via VS?

br
Erich
ew
Posted 12 Nov, 2019 02:54:42 Top
Andrei Smolin


Add-in Express team


Posts: 16607
Joined: 2006-05-11
Hello Erich,

ewessely writes:
But I think having requirements in the trustcenter is a showstopper in a commercial AddIn...


If we talk about a VBA project referencing anything, you have to deal with the references. The default security settings make this very unpleasant and often impossible. Note that the whole reference thing takes place only if you need to have events. But is this really so? Do you really need to have a VBA class getting events from the add-in? Does your ExcelDNA-dased solution handles such events?

ewessely writes:
Can't we do the same as you do when registering the Addin via Visual Studio?


I suppose I don't understand you. In the IDE or in the add-in installer (where adxregistrator.exe is started), registry keys are created for Office to locate and load the add-in; check section Locating COM add-ins in the registry; see the PDF file in the folder {Add-in Express}\Docs on your development PC.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Nov, 2019 04:49:49 Top
ewessely




Posts: 25
Joined: 2019-01-31
Hello Andrei,
yes, I need exposing events for vba - the clients worksheets deal with that...(The AddIn exposes functions calling a dataservice which sends back the data in form of multiple events I have to forward)

It seems that there is a little bit confusion in describing my problem. I'll try to explain it from the root.
Taking your sample ComAddinWithEvents.

Build and register via IDE
-Open Excel with a new blank worksheet
-go to Developer->Visual Basic
-go to tools -> references

You will see "ComAddInWithEvents" in the list of available references (as expected)
The client can check this reference and use it if he like.

Now create a setup and install it on a different PC
-Installation without error
-Open Excel with a new blank worksheet
-go to Developer->Visual Basic
-go to tools -> references

There you DO NOT have "ComAddInWithEvents" in the list of available references therefore the client cannot check it and use it...And that's exactly my problem. (Same if you also include the tlb file)


I hope this explanation brings a little bit light onto my problem. But maybe I missunderstand the complete registration process...

br
Erich
ew
Posted 12 Nov, 2019 06:34:19 Top
Andrei Smolin


Add-in Express team


Posts: 16607
Joined: 2006-05-11
Erich,

ewessely writes:
There you DO NOT have "ComAddInWithEvents" in the list of available references


I see. This works if you deploy a per-machine add-in. In this case, the installer works with elevated permissions and it can register the .TLB; to register the TLB if Visual Studio Installer is used, set Register=vsdrfCOM on the TLB item.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 12 Nov, 2019 08:55:24 Top