1

I have a COM automation server hosted by a VB6 exe.

The COM API is used from Excel VBA:

Dim o as MyCOMAPI.MyCOMType
Set o = new MyCOMAPI.MyCOMType
o.DoSomething

When I create objects in VBA the exe is started along with COM automation and VBA can use the API.

But the exe is closed quickly and "randomly" by Excel, I guess when Excel decides it doesn't need the COM API anymore.

This behaviour is causing random errors.

The simple solution is to start the exe before running the VBA code ; in this case all is working fine as the exe won't stop running until closed by the user.

Have you some information/documentation about the way Excel VBA manages calls to hosted APIs?

Is there a way to avoid this behaviour and have the exe kept open until the VBA code decides to stop it?

Pragmateek
  • 13,174
  • 9
  • 74
  • 108

2 Answers2

4

This would be the default behavior for a COM automation server when the last object is dereferenced, meaning that the variable that points to the server is set to nothing.

Now, if your code looks something like this today:

Sub MyFunction()
...
Dim o as MyCOMAPI.MyCOMType
Set o = new MyCOMAPI.MyCOMType
o.DoSomething
End Sub

Then, the server life is connected to the life of the o variable. That variable gets set to nothing when the function is finished, and then the server will be shut down (unless there are other variables keeping it alive).

To make sure that your COM server is kept alive for a longer time, simply define the variable as a Public variable as in the sample below.

This sample will start and show Excel and keep it open until the ShutdownExcel function is called.

Public o As Excel.Application

Sub MakeSureExcelIsRunning()
    If o Is Nothing Then
        Set o = New Excel.Application
        o.Visible = True
    End If
End Sub

Sub ShutdownExcel()
    Set o = Nothing
End Sub
GTG
  • 4,914
  • 2
  • 23
  • 27
  • Thanks for your answer. I've tried a similar pattern by exposing a public object in a dedicated module but I guess even in this case the object can be dropped by Excel. So maybe I should keep a reference to it in all my modules but I'm not sure it will be enough. Anyway you get my +1 and if there is no other answer I'll accept it. – Pragmateek Jul 03 '15 at 11:56
  • Excel will *not* close the EXE if there is an outstanding variable referring to an object from your EXE. And notice that just doing a global `Dim v As New Libname.myClass` will *not* work - that doesn't do what most people think it does (http://stackoverflow.com/q/2478097/2230). You have to `Set` it by hand from a Function or Sub somewhere. – Euro Micelli Jul 05 '15 at 00:24
1

From COM docs.

 **Component Automation**

Shutting Down Objects

ActiveX objects must shut down in the following way:

If the object's application is visible, the object should shut down only in response to an explicit user command (for example, clicking Exit on the File menu) or the equivalent command from an ActiveX client.

If the object's application is not visible, the object should shut down only when the last external reference is gone.

If the object's application is visible and is controlled by an ActiveX client, it should become invisible when the user shuts it down (for example, clicking Exit on the File menu). This behavior allows the controller to continue to control the object. The controller should shut down only when the last external reference to the object has disappeared.

© Microsoft Corporation. All rights reserved.

When you write an COM server exe the first thing you do it take a reference to yourself when starting as a normal exe else the exe shuts down as soon as initialisation is over.

user5071892
  • 147
  • 2