There's a number of sites on the web that discuss COM interop, but none too recent (that I can find) and most refer to calling VB.NET code from VBA, and almost all stop at the very basics. So I'm sharing my thoughts on my experience of calling C# code from VBA. I've not needed to call C# code from VBA on very many occasions (certainly single digits of times in my many years of working with VBA) but it can be very helpful in some circumstances … so what are those circumstances, why would you do this (because it definitely adds complexity)?
Performance. That's it. In my personal experience, I've never come across anything I couldn't do in either 'pure' VBA or by (for example) using the Windows API that I could do in C#. And while there are some things you can do in C# that are much simpler than doing the same in VBA (an example is included below in Arrays, sorting a list of strings), the added complexity of COM interop means you're always likely to just persevere with using VBA.
So it's purely about speed … and lots of it … depending on a number of factors (what the code is doing, the device it is running on, how optimised your code is etc), the same task can be achieved 10s or 100s of times faster using C# code compared to VBA code. For a demo, download and run 'Mandelbrot Set viewer' from my Mandelbrot Set viewer - VBA vs C# blog post - in testing, to draw the initial Mandelbrot Set, running multiple times on my device (a fairly standard laptop, nothing special) using VBA took an average of 5.737 seconds and using C# took an average of 0.139 seconds … that's 41 times faster and that's without allowing Visual Studio to optimise my C# code. Try it for yourself and see!
Note that this is a guide to getting up and running with calling C# code from VBA … I'm not trying to cover every aspect of this subject (cause that'd be an entire book) so, yes, I could've covered alternative approaches, different ways of doing things … but I've tried to focus on the main things (with a few extras thrown in … in Appendix 4: Useful info) that will allow you to get some benefit out of COM interop.
What you will need
As well as having basic knowledge of VBA and access to an app that hosts VBA (eg Excel, Word, PowerPoint, Outlook, Access … these same instructions will work for any Office application that hosts VBA), this post assumes:
You have a reasonable knowledge of developing with C#
You have administrator access on your development device as you will need to update Registry keys in HKEY_CLASSES_ROOT
You are using a Windows device with the .NET Framework installed, preferably version 4.6 or greater
You have Visual Studio installed (though you can use other IDEs, this post refers to using Visual Studio … at the time of writing, Visual Studio 2022 version 17.5.0 … if the various paths to menu options don't exist when you're reading this then I apologise, but Microsoft do have a habit of moving everything around every few years)
If you're used to developing with VB.NET then I'm sure this post will be useful to you … but you'll have to do the 'translating' to VB.NET-speak from C#-speak yourself!
Create your C# library
First, you need to select the version of .NET Framework that you will use (always use the .NET Framework for COM interop not any other variant of .NET). The version depends on the devices you are targeting … you need to choose the oldest version that might be installed on any device you want to deploy to … assuming none of those devices is using Windows older than Windows 10, then you're safe using .NET Framework 4.6 (as that, or newer, was the pre-installed version on Windows 10 and Windows 11 comes with 4.8 installed) but I reckon there are very few Windows 10 or newer devices using anything older than 4.8.
So fire up Visual Studio and create a new 'Class Library (.NET Framework)' project. Choose a project name (I'm using 'BlogProject' for this post … Visual Studio will also use this name as your namespace and so it will become the name of the library in the 'References' dialog in the VBE) and choose a version of the .NET Framework and click 'Create'.
Add usings and interface
You should now be staring at an empty class. Rename it to whatever you want, for this post I renamed to MyStuff. The class must be public and it must have a default (ie with no parameters) public constructor (which is added by C# automatically so you don't need to add your own constructor, though you can if required … there is an example in For Each, below for reference).
Add the following using statements:
Next, add an interface … but first, some honesty: you don't NEED an interface … but it is best practice (per MS https://learn.microsoft.com/en-us/dotnet/standard/native-interop/qualify-net-types-for-interoperation). It allows you to use early binding (for Intellisense) in the VBE, stops problems later in development if you re-order or rename your members (so long as you add the DispId attribute) and allows you to add the Description attributes to individual members (so you can see descriptions of them in the VBE's Object Browser). I reckon: add an interface now. Saves problems in the future.
So, add an interface, must be public, I'm calling mine IMyStuff. Add your interface members and so you might have something like this:
… I know, for those of you used to C# I do my braces weird … I blame my time working with Java.
Next, we need to add the interop attributes (example code that includes all of these attributes is below).
First, add a GUID to the interface … in Visual Studio, Tools > New GUID then select the appropriate GUID format (the one with square brackets) then Copy > Exit and paste the copied GUID into the line immediate above the interface. If your Tools menu does not have a New GUID option then see Appendix 1: Getting the New GUID tool. DON'T COPY / PASTE THE SAME GUID AS USED IN MY EXAMPLE CODE … CREATE YOUR OWN UNIQUE GUID!
Second, optionally (though recommended), explicitly allow both early and late binding by adding the [InterfaceType(ComInterfaceType.InterfaceIsDual)] attribute … this allows you, as a developer, to use Intellisense when we finally get working with VBA code
Third, optionally (though strongly recommended), add an explicit DispId to each interface member … these should be numbered uniquely … if you don't add these, they will be added automatically based on the order in the interface … ultimately, COM access .NET members using this number and this can cause problems if you re-order your members at a later date and have not added a DispId. Once you have allocated a DispId to a member, you should not change it.
Fourth, optionally (if you want to see helpful info for each member in the VBE's Object Browser), add a Description attribute to each interface member (though not to the interface itself) eg [Description("This is a description")]
So now you should have something like this:
The class
Make the class implement the interface then add the implementations of the interface members. Then add more interop attributes (again, example code with all of these attributes added is below), all to the class itself:
First, add a GUID to the class … not the same GUID that you used for the interface (but use the same process, above, as for the interface). DON'T COPY / PASTE THE SAME GUID AS USED IN MY EXAMPLE CODE … CREATE YOUR OWN UNIQUE GUID!
Second, add a ProgId to the class … I use a 'namespace.class' convention so in this case the attribute is [ProgId("BlogProject.MyStuff")]
Third, optionally (though recommended), explicitly tell the compiler not to auto-create an interface … as we have explicitly added our own … by adding the [ClassInterface(ClassInterfaceType.None)] attribute
Fourth, optionally (if you want to see helpful info for the class in the VBE's Object Browser), add a Description attribute to the class eg [Description("This is my awesome class")]
So now your class has gone from this:
To this:
At this point, a few checks:
Make sure you've added all of the required attributes ... and I strongly recommend adding the non-required attributes other than the Description which is up to you
Make sure the class implements your interface (otherwise your interface is doing nothing)
Make sure the interface, class and class members are all public
Make sure you have not added a non-public default (ie no-parameters) constructor
Make sure you used your own GUIDs and didn't copy/paste the ones in the example code … remember, the 'GU' in GUID stands for 'globally unique'!!
Make COM-visible
You have two options for this. The simple one: in Visual Studio, select Project > BlogProject Properties (or whatever you called your project) > Application > Assembly Information … tick ‘Make assembly COM-visible’.
Or the ever-so-slightly more complex one: add another attribute to the class (and also to the interface if you want to be able to use early binding): [ComVisible(true)].
I recommend the former ... there's nothing, really, to be gained from the latter.
Add a 'strong name'
This is optional but highly recommended and eliminates potential problems when running / deploying and eliminates the warning you will see if you use the RegAsm tool in the next Register for COM interop step. If you're just doing some initial testing of COM interop then adding a 'strong name' definitely isn't essential.
Again, there are two ways of doing this: using Visual Studio or using the SignName tool … for the latter, see https://learn.microsoft.com/en-us/dotnet/framework/tools/sn-exe-strong-name-tool ... to do this in Visual Studio, select Project > BlogProject Properties (or whatever you called your project) > Signing … tick 'Sign the assembly', then in the ‘Choose a strong name key file’ select ‘<New>’ and type in a 'Key file name' (can be any name as the file is going to be created now) … don’t add an extension, optionally untick ‘Protect my key file with a password’ (or enter a password) then OK … a 'strong name key file' will be added to the C# library project folder.
Register for COM interop
You have two options for this. The simple one that you can do only on the development device (so is good for the testing and development period only), or the less simple one.
The simple but 'development device only' way is to use Visual Studio to register … select Project > BlogProject Properties (or whatever you called your project) > Build and tick 'Register for COM interop' (you may have to scroll down to find this option). The complicating factor with this is that you need to be using Visual Studio 'as an Administrator' for this (as it creates keys in HKEY_CLASSES_ROOT in the Registry) so you may need to log out and back into Visual Studio 'as an Administrator'.
The alternative is to use the RegAsm tool, for which you will refer to Appendix 2: Using the RegAsm tool after the Build step. I'd suggest, initially at least, to use Visual Studio. Note that when you get to Deploying, this step of registering for COM interop is automated as part of the VBScript which runs the RegAsm tool for you.
Set the 'Platform target'
Check the 'Platform target' … in Visual Studio, select Project > BlogProject Properties (or whatever you called your project) > Build > Platform target.
'Any CPU' should, as it sounds, work for any CPU ie x86 (32-bit Office) and x64 (64-bit Office). If you have problems building or deploying to a device with a specific bitness of Office then select 'x86' or 'x64' as appropriate … but then the .dll will only work on that bitness of Office and will fail to install (will not be recognised as 'a valid .NET assembly') on devices with the other bitness of Office.
Build
You can set the config to Release if you wish (I'm not doing so for this demo) but you may want to do so before deploying your code. But note: the settings for 'Platform target', 'COM visible' and 'COM interop' are all per configuration … not global … so if you subsequently change config you will need to come back and review those settings again.
… then build your project/solution, ensuring that Visual Studio reports a successful build.
… then if you are using the RegAsm tool to 'register for COM interop', see Appendix 2: Using the RegAsm tool.
Use the library from VBA
Open up the VBE in Excel, Word or whichever VBA-hosting application you prefer. Add a Project Reference to the C# library:
If you have VBE_Extras installed, right-click on your VBA Project in the Project Explorer window then select: Extras > Update "Project References" and filter the 'Available References' (the lower of the two lists) by the namespace (BlogProject in this post). Put a tick next to its name to add it as a Project Reference then OK to close the dialog.
If you don't have VBE_Extras installed, select: Tools > References then scroll down through the list to find your library which will have the name of the namespace (BlogProject in this post). Put a tick next to its name to add it as a Project Reference then OK to close the dialog.
If you cannot find your library:
Not all of the libraries in the VBE's References dialog are in alphabetical order (the first few are those that the VBE prioritises) … scroll down past these to the bulk of libraries that are in alphabetical order as that is where your library will be listed
Check that you have correctly followed the instructions in Make COM visible and Register for COM interop
Check that you have applied all of the required attributes to the interface and class including the (unique) GUIDs (different GUIDs for the interface and the class), the ProgId and the InterfaceType and ClassInterface attributes
Make sure you have not set a description in the Assembly Information of the C# library (ie in Project > Properties > Application > Assembly Information) … if you do, you will find the library under the name of the description in the References dialog, but it will not work (at all, or correctly)
Once you have added the Project Reference to the C# library, add some code like the following … but if you added the interface and all attributes, I suggest not copy-pasting this, rather type it out to get the full Intellisense experience when you type the '.' following 'BlogProject' and again following Debug.Print oMyStuff
Run your code … congratulations on your first use of COM interop!
Description attributes and the Object Browser
Assuming you added description attributes then you can see these in the Object Browser so long as your project has the reference to the C# library. Go to the Object Browser then in the drop-down of libraries (top left), select the name of your library ('BlogProject' if you are following along with me). Under 'Classes' you will then see 'MyStuff' … select this and in the grey box below you will see the description you applied. Select one of the members of MyStuff (ie either 'Add' or 'Concat') and again in the grey box below you will see their respective description.
Late binding
The above all uses early binding meaning that Intellisense and descriptions are available to you during development. If you want to use late binding, eg when deploying (I'm not going into the pros and cons of late vs early binding here … there are plenty of sites on the internet that go into more detail on this), then, in the VBE, remove the reference to the library (in the References dialog, untick the C# library) and change the code from:
To:
Run your code again to test it still works.
As you would expect with late binding, you will not get Intellisense nor any descriptions in the Object Browser (however, if you are using VBE_Extras, then you might also want to look into the special binding feature that it provides: Intellisense with late binding).
Next steps
At this stage, you should be itching to write your own C# library class members. I really recommend first reading Appendix 3: Start the host application directly from Visual Studio / debugging your C# code from the host application. Then, go for it! Just remember:
Add your new members to the interface including a DispId attribute (remember: must be a unique number) and a Description attribute if you want to see a description in the Object Browser
Then add the members to the class … make them public!
Static members cannot be seen from your VBA code (COM does not support static members)
You can, of course, add multiple classes (and so multiple interfaces, one for each class) to your C# library … remember to add a new GUID for the interface and for the class and remember to update the ProgId attribute to match the class name
Close Excel, Word or whichever VBA-hosting application you are using (if you don't do this but have opened the VBE with a VBA project that has a reference to your C# library then you will find that Visual Studio will not re-build the C# library and, after a number of attempts, it will time-out)
Re-build the C# library in Visual Studio
Go back into your VBA project and use your newly added members!
Some helpful info to assist you is in Appendix 4: Useful info which includes sections on:
Help! It did work but it's stopped working!
I've been here. You did something (or nothing, you reckon!) and suddenly nothing works anymore! Try this:
First, try:
In the VBE, remove the Reference to the C# library from your VBA project
Save and close the VBA project and close the host app (Excel, Word … whichever)
Re-open the host app and re-open the VBA project
Re-add the Reference to the C# library and run your code
If that doesn't work, try:
In the VBE, remove the Reference to the C# library from your VBA project
Save and close the VBA project and close the host app (Excel, Word … whichever) itself
In Visual Studio, 'Clean' your project/solution
Check again that you have correctly followed the instructions in Make COM visible, Register for COM interop and Set the 'Platform target' (or that you haven't inadvertently changed a setting that you originally had set correctly … easily done) … particularly if you have just changed configuration (eg from Debug to Release or vice-versa) as the 'Platform target', 'COM visible' and 'COM interop' settings are all set per configuration, not globally
Check again all of the required attributes are present on the interface and class
Check again that you have not set a description in the Assembly Information of the C# library (ie in Project > Properties > Application > Assembly Information)
Check the interface, class and class members are all public
Check that you have not added a non-public default constructor
If you didn't already, consider Add a 'strong name'
Re-build your project/solution and ensure Visual Studio reports that the build was successful – any errors need to be fixed; if the build 'times out' then ensure all VBA-hosting applications are closed on your device (including checking in the Task Manager for any 'ghost' instances)
Re-open the app and re-open the VBA project
Re-add the Reference to the C# library and run your code
Deploying
So you've created your C# library and also the Workbook / Document etc that will use it (including making a decision about whether to use early or late binding … the considerations are the same as for when using any other external library). Now to get it out to users …
There are a number of ways to go about this … of course, the manual one of copying the .dll file to the target device (along with the Workbook / Document etc) then using the RegAsm tool (as per Appendix 2: Using the RegAsm tool) is one option … that doesn't scale well. The following covers deploying locally (ie not to the Global Assembly Cache … see https://learn.microsoft.com/en-us/dotnet/framework/app-domains/gac for more on the GAC if interested) using a VBScript (ie .vbs) file … of course you can use a dedicated installer tool such as the excellent (and free) Inno Setup (see https://jrsoftware.org/isinfo.php). However, I'm doing this using a VBScript file in this post as that is the same solution I used for 'Mandelbrot Set viewer' in my blog post Mandelbrot Set viewer - VBA vs C# … in fact, with one slight change, it uses the exact same VBScript. The VBScript also includes an uninstall option.
So first, create a directory and put your Workbook / Document etc into it.
Then build your C# library, you should Add a 'strong name' / ideally sign your code with a code-signing certificate. Copy the .dll file into the same directory (don't copy the .tlb file if you have Visual Studio set up to create it: the VBScript will use the RegAsm tool to create the .tlb file dynamically).
Jump to Appendix 5: VBScript deployment script and copy all of the code there. Create a new file using Notepad or whichever text editor you prefer and paste all of the code into it. Change the value assigned to sFILE_NAME in the first line from "BlogProject.dll" to the name of your .dll file (just the filename including extension, not the full path). Save the file as a .vbs file (eg called 'installer.vbs') into the same directory as the .dll file.
You can then zip and distribute the directory (perhaps with a 'read me' text file included to explain to the user what they should do with it ... for an example, see the 'Mandelbrot Set viewer' blog post for the 'read me' file I used with that).
Some assumptions that the VBScript makes about the device on which installation is taking place:
That it has the Scripting.FileSystemObject library installed … true on every viable Windows device
That it has Excel installed in order to test the Office bitness … if you like (if your C# library is going to be used from another host application) then change the VBScript to use that other host application
That it only has one 'bitness' of Excel/Office installed … true on 100% of devices according to Microsoft (true on well over 99.99% based on my estimate … and for the other 0.01%, the user of the device is likely expert enough to know which bitness they want to install for so adjust the VBScript to ask the user which bitness they want!)
Problems on devices that you have deployed-to
Assuming everything works fine on the development device then the key thing is to understand whether it fails on every deployed-to device or just one (or a few) of them.
If it fails on every device, there is a high chance that there is a problem with what you created. Go back and work through the points in Help! It did work but it's stopped working!
If you are deploying to an environment where every device has an identical set-up (eg in a corporate environment), then you might need to discuss with the relevant IT support/team if there are security or other protocols blocking installation or running of your code.
If only one (or a few) devices then there is something about those devices that is causing a problem … a possible candidate problem is that the necessary minimum version of the .NET Framework is not installed on that device ... but really it will be up to you to identify the environmental difference on that / those device(s).
Appendix 1: Getting the New GUID tool
In Visual Studio, Tools > External Tools > Add
Title: New &GUID
Command: browse to the Tools directory of your current Visual Studio install and look for 'guidgen.exe' … on my device, the Tools directory is at "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\Tools\"
Initial directory: same path as above (ie the Tools directory)
Click OK and now you will have the New GUID tool in your Tools menu
Appendix 2: Using the RegAsm tool
The RegAsm tool is used to register a .NET assembly for COM interop. There's plenty of guides elsewhere on the internet describing how to use RegAsm (the official one is here https://learn.microsoft.com/en-us/dotnet/framework/tools/regasm-exe-assembly-registration-tool). First, build your C# library project to create your .dll file. If you want to, copy the .dll file (ie from the projects 'bin/Debug' or 'bin/Release' directory) to your desired location … possibly the same as the Workbook file (if using from Excel … Document for Word etc) that you want to use the C# library from. Open an administrator-level command prompt (it must be administrator-level as RegAsm writes to the HKEY_CLASSES_ROOT key hive in Registry) and navigate to the directory that the .dll is in. Then (adjusting for the actual name of your .dll file if not called 'BlogProject.dll'), in the command window type:
If using 64-bit Office:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regasm /codebase /tlb BlogProject.dll
If using 32-bit Office:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm /codebase /tlb BlogProject.dll
You should see messages 'Types registered successfully' and 'Assembly exported to '<YourPath>\BlogProject.tlb', and the type library was registered successfully'.
Also if you did not Add a 'strong name' then you will see the 'Registering an unsigned assembly with /codebase can cause your assembly to interfere with other applications that may be installed on the same computer' warning.
The two switches tell RegAsm to do the following:
/codebase – tells RegAsm to create 'codebase' keys in the Registry when adding all other keys
/tlb – tells RegAsm to create a type library file from the assembly … having run the ResAsm tool, you will now see this .tlb file in the same directory as the .dll file
If you want to see a list of the Registry keys that RegAsm creates, omit the /tlb switch (keep the /codebase switch) and add a /regfile:keys.txt switch (where 'keys.txt' is the filename that will be created with a list of the keys, the file will be created in the same directory as the .dll file … if you just use a /regfile switch without a colon or filename then a .reg file will be created instead, this is a file that can be used to actually add the keys to the Registry) … for further details, see the table of Parameters at the above link to the RegAsm tool.
Note that if you want to unregister the assembly, replace the /codebase switch with the /unregister switch, so in the command window type:
If using 64-bit Office:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\regasm /unregister /tlb BlogProject.dll
If using 32-bit Office:
C:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm /unregister /tlb BlogProject.dll
Appendix 3: Start the host application directly from Visual Studio / debugging your C# code from the host application
You can start the host application (Excel, Word etc) directly from Visual Studio which eliminates the need to separately do so following building your C# library project. This makes development much quicker when you are going through a cycle of repeated testing. Also, doing this also allows you to debug your C# library code directly from the host application (if, that is, you are running in a Debug configuration … and assuming you are using 'typical' Visual Studio settings):
You can add / use breakpoints in your C# library code
If / when an Exceptions occurs in your C# library code, Visual Studio will show the Exception dialog rather than the Exception flowing down to the VBA to appear as an Error
To do this, in Visual Studio, select Project > BlogProject Properties (or whatever you called your project) > Debug then under Start Action select 'Start external program' and either type in or browse to the executable file for your favoured VBA host application eg, on my device to use Excel, I browse to "C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE".
You can then just use F5 (or the Start button, ie the green triangle in the main Visual Studio toolbar) to build your project and launch the host application in which you can then run your code. And you can use Shift+F5 (or the Stop button, ie the red square) to stop running all code and close the host application (note: you will not be asked to save any changes you may have made in the host application so remember to save first if required).
Appendix 4: Useful info
Types in VBA and C#
Remember to use the right matching types for VBA and C#
In VBA In C#
Boolean bool
Byte byte
Integer short
Long int
LongLong long
Single float
Double double
Date DateTime
String (variable length) string
Variant dynamic *
Array See Arrays
* The dynamic type was added to C# in part to simplify COM interop. However, your C# code needs to know how to handle what it receives as the dynamic will 'wrap' a specific type … if, for example, your C# code tries to perform a mathematical operation on a dynamic that holds a string, then it ain't gonna go well.
Arrays
Pass in to C# from VBA: in VBA, the array must have a lower bound of zero and it must be passed by reference (ie in C# use 'ref' in front of the parameter):
Update an existing VBA array: again, in VBA, the array must have a lower bound of zero and it must be passed by reference (ie in C# use 'ref' in front of the parameter):
Return to VBA from C#: can only assign the returned array to a VBA Variant:
… if testing with these procedures, remember to add the interface members also!
For Each
If your C# class wraps a List, HashSet etc then you can allow VBA to use For Each to iterate over it:
Add the IEnumerable interface to the class declaration
In the class, implement GetEnumerator() eg
Then add the method signature, with a DispId of -4, to the interface eg
And note that the type used in VBA to iterate must be an object or Variant.
So a full example would look like, in C#
And you could use it in VBA like so:
Optional parameters
Are allowed … normal rules apply … just align the member in the interface and in the class.
Enums
Enums added in C# can be accessed in VBA code. For example, in C#:
… remember to use your own GUID, not the above GUID … setting explicit values is optional … you can add Description attributes to the Enum and / or its members … and then the enum can be used in VBA, for example:
Exceptions
As per Appendix 3: Start the host application directly from Visual Studio / debugging your C# code from the host application, what happens when there is an Exception in your C# library code depends on how you are running your code.
If running without Visual Studio attached (ie you build your C# library in Visual Studio then manually start the host application eg Excel, Word etc and run code from that host application) then Exceptions thrown in your C# library code will results in Errors being raised in your VBA code with the Err.Description set to the Exception's Message property. You can then handle Errors in the VBA code as you would any other VBA Error.
However (assuming typical settings), running direct from Visual Studio:
In Debug configuration, then any Exception will result in a Visual Studio Exception dialog
In Release configuration, then Exception are handled the same as when running without Visual Studio attached