IDTExtensibility2 - Addin, catching the Excel Application

I was revising the topic of writing a VBA addin using the IDTExtensibility2 interface and it struck me that the Excel.Application object is not passed in. Instead, in the argument list for OnConnect the first argument is of type VBE. This makes sense because the addin is likely to add menus to the menu bars of the development environment. However, I think it would have been nice to also pass the Excel.Application object because the addin may well want to add workbooks etc.

I set about remedying the this and hits some problems. I solved this problems along the and I am happy to share working code below.

So acquiring the running instance of Excel turned out to be quite challenging. I had hoped using the System.Diagnostics.Process class would give me easy access but it didn't; even the MainWindowHandle returned the VBE's development window instead Excel's main window. Eventually, I had to grab the process id and then look for Excel windows registered as children of the desktop window and match the owning process id. Once in possession of an XLMAIN handle I grab the XLDESK child handle and then in turn its EXCEL7 child handle. Finally, we can pass the EXCEL7 handle to the AccessibleObjectFromWindow() to acquire an IDispatch interface to an Excel workbook's window and then we navigate to the Excel.Application object. All this extra code is housed in the Accessibility class. N.B. this method is dependent of at least one workbook being present (even if it is a .XLAM hidden workbook).

The C# source code follows, this is a C# Assembly DLL project. It does have some extra registration entries, see underneath.

using System;
using System.Runtime.InteropServices;
using System.ComponentModel;
using Xl = Microsoft.Office.Interop.Excel;  // Add reference to Microsoft Excel type library
using System.Diagnostics;
using Extensibility; // Add reference to 'C:\Program Files (x86)\Common Files\microsoft shared\MSEnv\PublicAssemblies\extensibility.dll'
using Microsoft.Vbe.Interop;

/* In Project Properties Build tab, check the checkbox 'Register for COM Interop' */
/* In Project Properties Debug tab, edit 'Start external program' to C:\Program Files\Microsoft Office 15\root\office15\excel.exe */

namespace VBAIDEAddin
{

    [
        ComVisible(true),
        Guid("1D5F24D3-E24C-4283-AAA9-AAED51B146F7"), 
        ProgId("VBAIDEAddin.Connect"),
        ClassInterface(ClassInterfaceType.None),
        ComDefaultInterface(typeof(IDTExtensibility2)),
        EditorBrowsable(EditorBrowsableState.Never)
    ]
    public class Class1 : IDTExtensibility2
    {
        private VBE _vbe;
        Xl.Application xlApp = null;
        private AddIn _AddIn;

        public void OnConnection(object Application, ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
        {
            try
            {
                _vbe = (VBE)Application; // cast to strongly typed
                _AddIn = (AddIn)AddInInst;

                if (Accessibility.GetCurrentProcess(ref xlApp))
                {
                    xlApp.StatusBar = "addin was here";
                }

                switch (ConnectMode)
                {
                    case Extensibility.ext_ConnectMode.ext_cm_Startup:
                        break;
                    case Extensibility.ext_ConnectMode.ext_cm_AfterStartup:
                        InitializeAddIn();

                        break;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        public void OnDisconnection(ext_DisconnectMode RemoveMode, ref Array custom)
        {
            switch (RemoveMode)
            {
                case ext_DisconnectMode.ext_dm_UserClosed:
                    ShutdownAddIn();
                    break;

                case ext_DisconnectMode.ext_dm_HostShutdown:
                    // some hosts do not call OnBeginShutdown: this mitigates it.
                    ShutdownAddIn();
                    break;
            }
        }

        public void OnAddInsUpdate(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        public void OnStartupComplete(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        public void OnBeginShutdown(ref Array custom)
        { /*throw new NotImplementedException(); */  }

        private void ShutdownAddIn()
        {  }

        private void InitializeAddIn()
        {  }
    }


    public class Accessibility
    {
        [DllImport("user32.dll", SetLastError = true)]
        static extern IntPtr FindWindowEx(IntPtr hwndParent, IntPtr hwndChildAfter, string lpszClass, string lpszWindow);

        [DllImport("oleacc.dll", SetLastError = true)]
        internal static extern int AccessibleObjectFromWindow(IntPtr hwnd, uint id, ref Guid iid,
                                                    [In, Out, MarshalAs(UnmanagedType.IUnknown)] ref object ppvObject);

        [DllImport("kernel32.dll")]
        static extern uint GetCurrentProcessId();

        [DllImport("user32.dll")]
        static extern uint GetWindowThreadProcessId(IntPtr hWnd, out int processId);

        public static bool GetCurrentProcess(ref Xl.Application appRetVal)
        {
            bool bRetVal = false;
            appRetVal = null;
            Process currentProcess = Process.GetCurrentProcess();
            if (currentProcess.MainModule.FileName.EndsWith("excel.exe", StringComparison.CurrentCultureIgnoreCase))
            {
                bRetVal = GetXlMainByProcessId(currentProcess.Id, ref appRetVal);
            }
            return bRetVal;
        }

        static bool GetXlMainByProcessId(int processId, ref Xl.Application appRetVal)
        {
            bool bRetVal = false;
            appRetVal = null;

            IntPtr hWndXlMain = FindWindowEx(IntPtr.Zero, IntPtr.Zero, "XLMAIN", null);
            while (hWndXlMain != IntPtr.Zero)
            {
                int hOwnerProcess = 0;
                GetWindowThreadProcessId(hWndXlMain, out hOwnerProcess);
                if (processId == (int)hOwnerProcess)
                {
                    IntPtr lHwndDesk = FindWindowEx(hWndXlMain, IntPtr.Zero, "XLDESK", "");
                    if (lHwndDesk != IntPtr.Zero)
                    {
                        IntPtr lHwndExcel7 = FindWindowEx(lHwndDesk, IntPtr.Zero, "EXCEL7", null);
                        if (lHwndExcel7 != IntPtr.Zero)
                        {
                            Guid IID_IDispatch = new Guid("{00020400-0000-0000-C000-000000000046}");
                            const uint OBJID_NATIVEOM = 0xFFFFFFF0;
                            object app = null;
                            if (AccessibleObjectFromWindow(lHwndExcel7, OBJID_NATIVEOM, ref IID_IDispatch, ref app) == 0)
                            {
                                dynamic appWindow = app;
                                appRetVal = appWindow.Application;
                                return true;
                            }
                        }
                    }
                }
                hWndXlMain = FindWindowEx(IntPtr.Zero, hWndXlMain, "XLMAIN", null);
            }
            return bRetVal;
        }
    }
}

Here are the extra registry entries. Copy it to notepad and save off as a .reg file then double click on it to write entries to registry. If you are using this code for your own addin then please change the GUIDs (as they are meant to be unique).

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\VBA\VBE\6.0\Addins\VBAIDEAddin.Connect]
"CommandLineSafe"=dword:00000000
"Description"="Cut down version"
"LoadBehavior"=dword:00000000
"FriendlyName"="VBAIDEAddin"


[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7]
@="VBAIDEAddin.Connect"

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\Implemented Categories]

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\InprocServer32]
@="mscoree.dll"
"ThreadingModel"="Both"
"Class"="VBAIDEAddin.Connect"
"Assembly"="YourAssemblyNameFullTypeName"
"RuntimeVersion"="v4.0.30319"
"CodeBase"="file:///C:/Users/Simon/source/repos/VBAIDEAddin/VBAIDEAddin/bin/Debug/VBAIDEAddin.dll"

[HKEY_CLASSES_ROOT\CLSID\1D5F24D3-E24C-4283-AAA9-AAED51B146F7\ProgId]
@="VBAIDEAddin.Connect"

Multiple instances of Excel

Past versions of Excel will create new processes when asked and one can go look at the Task Manager for proof. Of late Excel will merge instances. For most people this is probably a good feature. However if you want to restore process isolation then one has to fiddle in the registry.

This Microsoft Support article shows how to add a registry setting but it does not work for me!

In the meantime I have discovered that holding the ALT key down whilst launching new Excel instance throws a dialog box saying "Do you want to start a new instance of Excel? (Yes/No)", this is shown in this Microsoft article. This is a good workaround for the time being but I would like the registry setting to work.

if you happen to know please add a comment below or tweet me @excel_developer or you can answer the StackOverflow question I have opened .

On StackOverflow I have been advised from that a command line Excel.exe /x – will generate new instance.

VBA - Windows API - Find all windows by class and then filter by process id

I needed some code to find all the top windows of Excel, that is of class XLMAIN and then filter these by process id. This is because elsewhere I am writing some C# code to acquire an instance of Excel via its window handles but all I have been given is a process id. Because I like to test logic in VBA I have written a VBA version. I am happy to share.

To begin, I use FindWindowEx to find all the windows I am interested in. All Excel instances have a main window of a class XLMAIN, and all of these have a parent window of the desktop. So supply zero for the first argument to signify the desktop and supply XLMAIN as the third argument, the fourth argument ignored. The second argument is used but varies, it allows us to loop through multiple results, we supply the previous result to get the next result. So I wrote a function to collect these to a collection, the function is called AllWindowsByClass(). The function is paramterised so that it can find windows of other class types.

Once in possession of a collection of window handles, I want to filter by a process id that I have been given. GetWindowThreadProcessId is the windows API that is best for this. In the function FilterWindowHandlesByProcessId() I loop through the a collection of handles and filter them to a new collection.

Enjoy!

Option Explicit
Option Private Module

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWndParent As Long, _
ByVal hWndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Private Declare Function GetWindowThreadProcessId Lib "user32.dll" (ByVal hwnd As Long, lpdwProcessId As Long) As Long

Private Function AllWindowsByClass(ByVal sClass As String) As VBA.Collection
    Dim colRet As VBA.Collection
    Set colRet = New VBA.Collection
    
    Dim hWndParent As Long
    
    hWndParent = FindWindowEx(0, 0, sClass, vbNullString)
    While hWndParent <> 0
        colRet.Add hWndParent
        hWndParent = FindWindowEx(0, hWndParent, sClass, vbNullString)
    
    Wend
    
    Set AllWindowsByClass = colRet
End Function


Private Function FilterWindowHandlesByProcessId(ByVal colWindowsHandles As VBA.Collection, ByVal lFilterProcessId As Long) As VBA.Collection
    Dim colRet As VBA.Collection
    Set colRet = New VBA.Collection

    Dim lLoop As Long
    For lLoop = 1 To colWindowsHandles.Count
        Dim lWinHandle As Long
        lWinHandle = colWindowsHandles.Item(lLoop)
    
        Dim lProcessId As Long
        GetWindowThreadProcessId lWinHandle, lProcessId
        
        If lProcessId = lFilterProcessId Then
            colRet.Add lWinHandle
        End If
    Next

    Set FilterWindowHandlesByProcessId = colRet
End Function

Here are some test functions but they have hard coded values that were valid for me and I determined whilst looking at Spy++ and other Windows diagnostic tools. Nevertheless they demonstrate how to call the above functions.

'************************************************************************************************
'* TEST FUNCTIONS
'************************************************************************************************

Private Sub TestAllWindowsByClass()
    Dim col As VBA.Collection
    Set col = AllWindowsByClass("XLMAIN")
    Debug.Assert col.Count = 2  'may differ for you!
End Sub

Private Sub TestFilterWindowHandlesByProcessId()
    
    Dim colWinHandles As VBA.Collection
    Set colWinHandles = AllWindowsByClass("XLMAIN")

    Debug.Assert colWinHandles.Count = 2

    Dim lTestProcessId As Long
    lTestProcessId = 24272 ' a process currently running on my PC, probably will differ for you!

    Dim colFiltered As VBA.Collection
    Set colFiltered = FilterWindowHandlesByProcessId(colWinHandles, lTestProcessId)

    Debug.Assert colFiltered.Count = 1
End Sub