Shared VB6 *and* VBA extensibility add-in with OnConnect and OnDisconnect handling

Question

Rubberduck works well enough when the VBE is of the VBA variety, but it currently only works when the Rubberduck is configured to load at VBA start-up, and it has a tendency to crash the host application or fail to shutdown when exiting. There's a number of outstanding issues around being able to load Rubberduck after the IDE is already loaded, and to unload Rubberduck without closing the IDE.

There's also a number of requests for adding the ability to work with VB5/VB6. While VB and VBA both have VBIDE libraries that are very similar, and they both support the IDTExtensibility2 interface, they are not interchangeable.

I've relied heavily on the MZ Tools documentation of Carlos Quintero of MZ-Tools, and the Microsoft documentation in coming up with a bare-bones solution that attempts to:

1. Allow for the add-in to be loaded/unloaded as the user requires.
2. Allow for the add-in to work under VB5/VB6 and 32/64-bit VBA.

I've created my own interop assemblies for the respective VBEs:

• VB6's VBIDE - Fubaa.Interop.VB6Ext
• VBA's VBIDE - Fubaa.Interop.VBA6Ext

Connect.cs

using Extensibility;
using Fubaa.Interop.VB6Ext;
using Fubaa.Interop.VBA6Ext;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace MyAddin
{
 [ComVisible(true)]
 [Guid(ClassId)]
 [ProgId(ProgId)]
 public class Connect : IDTExtensibility2
 {
 private const string ClassId = "506EC676-068F-497D-BEBB-B8EE42A34573";
 private const string ProgId = "MyAddin.Connect";
 private IVBE _vbe;
 private object _Addin;
 private bool isLoaded;
 public void OnAddInsUpdate(ref Array custom)
 {
 Debug.WriteLine("Begin Event: OnAddInsUpdate");
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 Debug.WriteLine("End Event: OnAddInsUpdate");
 }
 public void OnBeginShutdown(ref Array custom)
 {
 Debug.WriteLine("Begin Event: OnBeginShutdown");
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 DoCleanup();
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 Debug.WriteLine("End Event: OnBeginShutdown");
 }
 public void OnStartupComplete(ref Array custom)
 {
 Debug.WriteLine("Begin Event: OnStartupComplete");
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 DoInit();
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 Debug.WriteLine("End Event: OnStartupComplete");
 }
 public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
 {
 Debug.WriteLine("Begin Event: OnConnecton " + ConnectMode.ToString());
 try
 {
 if (Application is Fubaa.Interop.VBA6Ext.VBE)
 {
 _vbe = new VBAVBE((Fubaa.Interop.VBA6Ext.VBE)Application);
 _Addin = (Fubaa.Interop.VBA6Ext.AddIn)AddInInst;
 }
 else if (Application is Fubaa.Interop.VB6Ext.VBE)
 {
 _vbe = new VB6VBE((Fubaa.Interop.VB6Ext.VBE)Application);
 _Addin = (Fubaa.Interop.VB6Ext.AddIn)AddInInst;
 }
 }
 catch (Exception exception)
 {
 }
 switch (ConnectMode) {
 case ext_ConnectMode.ext_cm_Startup:
 // The add-in was marked to load on startup 
 // Do nothing at this point because the IDE may not be fully initialized 
 break;
 case ext_ConnectMode.ext_cm_AfterStartup:
 // The add-in was loaded after from the Addins Dialog
 DoInit();
 break;
 }
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 Debug.WriteLine("End Event: OnConnecton " + ConnectMode.ToString());
 }
 public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
 {
 Debug.WriteLine("Begin Event: OnDisconnecton " + RemoveMode.ToString());
 _Addin = null;
 _vbe = null;
 switch (RemoveMode)
 {
 case ext_DisconnectMode.ext_dm_HostShutdown:
 // The add-in was disconnected when the host application began closing.
 // Hopefully, we already cleaned up in the OnBeginShutdown event
 break;
 case ext_DisconnectMode.ext_dm_UserClosed:
 // The add-in is disconnected by the end user or an Automation controller
 DoCleanup();
 break;
 }
 Debug.WriteLine(" Addin is Loaded:" + isLoaded.ToString());
 Debug.WriteLine("End Event: OnDisconnecton " + RemoveMode.ToString());
 }
 public void DoInit()
 {
 Debug.WriteLine(" Add-in is initializing");
 isLoaded = true;
 Debug.WriteLine("The host VBE is {0}, Version {1}", _vbe.Name, _vbe.Version);
 Debug.WriteLine(" Add-in is intialized");
 }
 public void DoCleanup()
 {
 Debug.WriteLine("Add-in is terminating");
 isLoaded = false;
 Debug.WriteLine("Add-in is terminated");
 }
 }
}

IVBE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace MyAddin
{
 interface IVBE 
 {
 string Name { get; }
 string Version { get; }
 }
}

VB6VBE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Fubaa.Interop.VB6Ext;
namespace MyAddin
{
 public class VB6VBE : IVBE
 {
 private VBE _vbe;
 public VB6VBE(VBE vbe)
 {
 _vbe = vbe;
 }
 public string Name
 {
 get { return _vbe.Name; }
 }
 public string Version
 {
 get { return _vbe.Version; }
 }
 }
}

VBAVBE

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Fubaa.Interop.VBA6Ext;
namespace MyAddin
{
 public class VBAVBE : IVBE
 {
 private VBE _vbe;
 public VBAVBE(VBE vbe) 
 {
 _vbe = vbe; 
 }
 public string Name
 {
 get 
 {
 //VBA doesn't support Name, get the name from the MainWindow caption
 string caption = _vbe.MainWindow.Caption;
 return caption.Contains(" - ") ? caption.Substring(0,caption.IndexOf(" - ")) : "VBA";
 }
 }
 public string Version
 {
 get { return _vbe.Version; }
 }
 }
}

And here's the output when run with VB6 and the following steps:

1. Configure addin to load at startup
2. Startup VB6 and create a standard exe project
3. Unload the add-in using the Add-ins dialog
4. Reload the add-in using the Add-ins dialog.
5. Exit VB6

Output:

Begin Event: OnConnecton ext_cm_Startup
 Addin is Loaded:False
End Event: OnConnecton ext_cm_Startup
Begin Event: OnAddInsUpdate
 Addin is Loaded:False
End Event: OnAddInsUpdate
Begin Event: OnStartupComplete
 Addin is Loaded:False
 Add-in is initializing
The host VBE is Microsoft Visual Basic, Version 6.00
 Add-in is intialized
 Addin is Loaded:True
End Event: OnStartupComplete
Begin Event: OnDisconnecton ext_dm_UserClosed
Add-in is terminating
Add-in is terminated
 Addin is Loaded:False
End Event: OnDisconnecton ext_dm_UserClosed
Begin Event: OnConnecton ext_cm_AfterStartup
 Add-in is initializing
The host VBE is Microsoft Visual Basic, Version 6.00
 Add-in is intialized
 Addin is Loaded:True
End Event: OnConnecton ext_cm_AfterStartup
Begin Event: OnAddInsUpdate
 Addin is Loaded:True
End Event: OnAddInsUpdate
Begin Event: OnBeginShutdown
 Addin is Loaded:True
Add-in is terminating
Add-in is terminated
 Addin is Loaded:False
End Event: OnBeginShutdown
Begin Event: OnDisconnecton ext_dm_HostShutdown
 Addin is Loaded:False
End Event: OnDisconnecton ext_dm_HostShutdown
The program '[21000] VB6.EXE' has exited with code 0 (0x0).

And when run under Excel 2016 VBA:

Begin Event: OnConnecton ext_cm_Startup
 Addin is Loaded:False
End Event: OnConnecton ext_cm_Startup
Begin Event: OnAddInsUpdate
 Addin is Loaded:False
End Event: OnAddInsUpdate
Begin Event: OnStartupComplete
 Addin is Loaded:False
 Add-in is initializing
The host VBE is VBA, Version 7.01
 Add-in is intialized
 Addin is Loaded:True
End Event: OnStartupComplete
Begin Event: OnDisconnecton ext_dm_UserClosed
Add-in is terminating
Add-in is terminated
 Addin is Loaded:False
End Event: OnDisconnecton ext_dm_UserClosed
Begin Event: OnConnecton ext_cm_AfterStartup
 Add-in is initializing
The host VBE is Microsoft Visual Basic for Applications, Version 7.01
 Add-in is intialized
 Addin is Loaded:True
End Event: OnConnecton ext_cm_AfterStartup
Begin Event: OnAddInsUpdate
 Addin is Loaded:True
End Event: OnAddInsUpdate
Begin Event: OnBeginShutdown
 Addin is Loaded:True
Add-in is terminating
Add-in is terminated
 Addin is Loaded:False
End Event: OnBeginShutdown
Begin Event: OnDisconnecton ext_dm_HostShutdown
 Addin is Loaded:False
End Event: OnDisconnecton ext_dm_HostShutdown
The program '[44828] EXCEL.EXE' has exited with code 0 (0x0).

VBA and VB6 (I don't have VB5 to test with) both handle add-ins loaded at and after start-up, and unloaded before and during shut-down. And, in both instances, VBA and VB6 exit cleanly.

Am I correctly handling the add-in connections and disconnections?

Am I crazy to use an IVBE interface, or should I just use dynamic?

• ThunderFrame: hmm, I just noticed the VBA Name property varies according to when the add-in is loaded.

Answer 1

OnConnection and OnStartupComplete

I can forsee a problem arising. Assume the addin is started with ext_ConnectMode.ext_cm_AfterStartup then in the OnConnection() handler you are calling DoInit(). Afterwards the OnStartComplete() handler will be called and you call DoInt() again no matter if it had been called already. You should have at least

if (isLoaded) { return; } 

at the top of the OnStartComplete() handler.

While we are at `OnConnection ́...

• methdod parameter should be named using camelCase casing.
• swallowing an exception is bad style unless you have a very good reason for it. If you have a very good reason you should state this reasing as a comment so future developers know why you did what you did. In addition in your code if an exception is thrown the default flow just continues and you could see a Addin is Loaded:True but that wouldn't be the truth.
• if Application is neither Fubaa.Interop.VBA6Ext.VBE nor Fubaa.Interop.VB6Ext.VBE you should return from the handler.

DoInit() and DoCleanup()

Why are these methods public ? Make them private because thats the only scope they need.

---

Am I crazy to use an IVBE interface, or should I just use dynamic?

IMO the interface is good as it is. You have implemented it easy and it serves its purpose.

Using dynamics instead can lead to compilable code which will throw at runtime. IMO its better to have compiler type checking.

• ThunderFrame: Your foreseen problem isn't a realistic scenario (although there's no harm in adding the sanity check). The IDTExtensibility2 events are confusingly designed: The OnStartupComplete only occurs when the VBE is starting up, not when each add-in starts-up, so it only occurs when the VBE is loading (the documentation suggests it isn't necessarily safe to do anything with the VBE until this startup event has fired). The debug outputs involve an add-in loaded at startup, then unloaded, then loaded again, and the events occur in the desired order, without calling DoInit more than required.
• ThunderFrame: Nice spots on the OnConnection method. I'd based the signature and try block on a VB.NET example, so the casing wasn't my own, but I should have picked it up when converting it to C#. The exception handling definitely needs fleshing out. DoInit and DoCleanUp should definitely be private.
• ThunderFrame: The interface felt like the right way to go, particularly as the VBA VBIDE model is essentially a subset of the VB6 VBIDE model. I've only implemented Name and Version properties, but in reality, I'd presumably need to rewrite each object in the VBIDE hierarchy as an interface and then add VBE specific implementations. Are there any tools that can auto-generate the interfaces and implementations? Maybe I'd be better using a facade or adapter pattern?