with COM Interop

Introduced in Excel 2000.
When you are working with COM Interop you need to define an interface and a class.
Both the interface and the class need to have additional attributes added to them.
Once the C# dll has been buit it needs to be added to the registry using the RegAsm tool.


Creating

Open Visual Studio 2022 as administrator.
New Project, Visual C#, Windows Desktop, Class Library (.NET Framework).
Change the Name to "ExcelCOMInterop".
Change the Location to somewhere on your C drive.
Check the .NET Framework version is correct and press OK.
Rename the Class1.cs file to MyConnect.cs.
Remove the existing (default) class.
Add the following interface to the MyConnect.cs file.

namespace ExcelCOMInterop 
{ 
    [System.Runtime.InteropServices.Guid("186BFFC3-056F-435A-BE5B-4CC5CFAF8D73")] 
    [System.Runtime.InteropServices.InterfaceType(ComInterfaceType.InterfaceIsDual)] 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public interface IMyConnect 
    { 
        string GetMyString(); 
    } 
}

Add the following class underneath the interface in the MyConnect.cs file.

namespace ExcelCOMInterop 
{ 
    [System.Runtime.InteropServices.ProgId("ExcelCOMInterop.MyConnect")] 
    [System.Runtime.InteropServices.ComDefaultInterface(typeof(IMyConnect))] 
    [System.Runtime.InteropServices.Guid("DE3911A1-C82F-42F1-BEFB-F3802E1B6D34")] 
    [System.Runtime.InteropServices.ClassInterface(ClassInterfaceType.None)] 
    [System.Runtime.InteropServices.ComVisible(true)] 
    public class MyConnect : IMyConnect 
    { 
        public string GetMyString() 
        { 
            return "some text"; 
        } 
    } 
}

Make sure the 2 Guid Ids are replaced using (Tools > Create GUID).
Select (Build > Build Solution).
At this point no registry entries are added in the registry.
Select (Project > ExcelCOMInterop Properties).
Display the Build tab.
Change the "Platform target" drop-down to "x86" for 32 bit machines.
Change the "Platform target" drop-down to "x64" for 64 bit machines.
Tick the "Register for COM Interop" checkbox (requires admin rights).

alt text

Save and close the Properties window.
Select (Build > Build Solution).
When the solution is built a "TLB" file appears in the "\bin\Debug\" folder.
When the solution is built the RegAsm tool automatically runs.

C:\Windows\Microsoft.NET\Framework\v4.0.30319\ RegAsm /codebase ExcelCOMInterop.dll

The RegAsm tool adds the necessary COM Interop class and interface information to the registry.


Using Early Binding

We can use Early Binding if we want to see what properties and methods are available in the VBA Object Browser.
Open Excel, Create a blank workbook.
Display the Visual Basic Editor (Alt + F11).
Add a new Code Module (Module1).
Select (Tools > References) to display a list of all the object libraries that are registered.
Find "ExcelCOMInterop" in the list.
Tick this entry and select OK.

alt text

Add the following code to Module1.
When you step through this code, you will see "some text" appear in the Immediate window.

Sub Call_EarlyBinding() 
Dim objAddin As ExcelCOMInterop.MyConnect 
    Set objAddin = New ExcelCOMInterop.MyConnect 
    Debug.Print objAddin.GetMyString() 
End Sub

Using Late Binding

We can also use Late Binding if we do not want a reference added to the project.
Open Excel, Create a blank workbook, Add a new Code Module.
Add the following code to Module1.
When you step through this code, you will see "some text" appear in the Immediate window.

Sub Call_LateBinding() 
Dim objAddin As Variant 
    Set objAddin = CreateObject("ExcelCOMInterop.MyConnect") 
    Debug.Print objAddin.GetMyString 
End Sub

Important

Both the interface and the class should have unique Guid Ids (rather than relying on the default values).
The class should have a unique ProgId (rather than relying on the default value which is namespace and class name).
You will not be able to access the C# DLL without the corresponding TLB file that was automatically generated.
Both Late Binding and Early Binding rely on the TLB file being available.
The ExcelCOMInterop.dll cannot be browsed to, from the Excel Add-ins dialog box or the COM Add-ins dialog box.


Registry Entries - Interface GUID

The following registry keys are added for the Interface GUID:
An identical key is added in 5 different places.
HKEY_CLASSES_ROOT\Interface\{ interface_GUID }
HKEY_CLASSES_ROOT\Wow6432Node\Interface\{ interface_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Interface\{ interface_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\Interface\{ interface_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\Interface\{ interface_GUID }

(String)(Default) - IMyConnect 

\ ProxyStubClsid32 
(String)(Default) - {00020424-0000-0000-C000-000000000046} 

\ TypeLib 
(String)(Default) - { assembly_GUID }  // from AssemblyInfo.cs 
(String)Version - 1.0                  // from AssemblyInfo.cs

Registry Entries - Class GUID

The following COMInterop registry keys are added for the Class GUID:
HKEY_CLASSES_ROOT\ExcelCOMInterop.MyConnect
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ExcelCOMInterop.MyConnect

HKEY_CLASSES_ROOT\ExcelCOMInterop.MyConnect 
(String)(Default) - ExcelCOMInterop.MyConnect 

HKEY_CLASSES_ROOT\ExcelCOMInterop.MyConnect\CLSID 
(String)(Default) - { class_GUID } 

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ExcelCOMInterop.MyConnect 
(String)(Default) - ExcelCOMInterop.MyConnect 

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\ExcelCOMInterop.MyConnect\CLSID 
(String)(Default) - { class_GUID }

The Class GUI is also added to 3 other locations.
An identical key is added in 3 different places.
HKEY_CLASSES_ROOT\Wow6432Node\CLSID\{ class_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\CLSID\{ class_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\CLSID\{ class_GUID }

(String)(Default) - ExcelCOMInterop.MyConnect 

\ Implemented Categories 
(String)(Default) - (value not set) 

\ Implemented Categories\ {62C8FE65-4EBB-45e7-B440-6E39B2CDBF29} //new generated GUID 
(String)(Default) - (value not set) 

\ InprocServer32 
(String)(Default) - mscoree.dll 
(String)Assembly - ExcelCOMInterop, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null 
(String)Class - ExcelCOMInterop.MyConnect 
(String)CodeBase - file:///C:/Personal/ExcelCOMInterop/ExcelCOMInterop/bin/Debug/ExcelCOMInterop.dll 
(String)RuntimeVersion - v4.0.30319 
(String)ThreadingModel - Both 

\ InprocServer32\ 1.0.0.0 
(String)(Default) - (value not set) 
(String)Assembly - ExcelCOMInterop, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null 
(String)Class - ExcelCOMInterop.MyConnect 
(String)CodeBase - file:///C:/Personal/ExcelCOMInterop/ExcelCOMInterop/bin/Debug/ExcelCOMInterop.dll 
(String)RuntimeVersion - v4.0.30319 

\ ProgId 
(String)(Default) - ExcelCOMInterop.MyConnect

Registry Entries - Assembly GUID

The following registry keys are added for the Assembly GUID:
An identical key is added in 4 different places.
HKEY_CLASSES_ROOT\Wow6432Node\TypeLib\{ assembly_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\TypeLib\{ assembly_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Wow6432Node\TypeLib\{ assembly_GUID }
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Classes\TypeLib\{ assembly_GUID }

(String)(Default) - (value no set) 
\ 1.0 
(String)(Default) - ExcelCOMInterop 
\ 1.0 \ 0 
(String)(Default) - (value no set) 
\ 1.0 \ 0 \ win32 
(String)(Default) - C:\Personal\ExcelCOMInterop\ExcelCOMInterop\bin\Debug\ExcelCOMAddin.tlb 
\ 1.0 \ FLAGS 
(String)(Default) - 0 
\ 1.0 \ HELPDIR 
(String)(Default) - C:\Personal\ExcelCOMInterop\ExcelCOMInterop\bin\Debug
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext