Calling C# code from VBA (COM interop)

Update October 2024:

• This post uses Visual Studio and/or RegAsm to 'statically' register your C# library before it is loaded and used by your VBA code ... doing this requires your end-users to perform some level of installation (for the C# library) and also requires your end-users to have administrator permissions on their device. This is the "correct" way to go about COM interop.

• However, you may also be interested in my Dynamically loading a DLL "on-the-fly" post which (as it sounds) eliminates the need for your end-users to perform installation (installation is done by the VBA code) and also has the benefit of not requiring your end-users to have administrator permissions on their device.

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 (or PowerShell script) 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 (i.e. either 'Add' or 'Concat') and again in the grey box below you will see their respective description.

If you move on to write your own C# class library and want to add a description attribute for a Property, then see the Description attributes and Properties section.

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:

• Types in VBA and C#

• Arrays

• For Each

• Optional parameters

• Enums

• Exceptions

• Description attributes and Properties

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!)

Update November 2024:

• As Microsoft is now deprecating VBScript, I have added Appendix 6: PowerShell deployment script.

• However, a better solution all around, depending on your needs, may be to load the DLL on-the-fly, for which see my October 2024 update comments at the top of this post.

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

Description attributes and Properties

To allow Descriptions for Properties to appear in the Object Browser, the Description attribute has to be added within the curly braces instead of above the Property (whereas other Attributes, such as the DispId in the following, should still be added above). For example:

Appendix 5: VBScript deployment script

Appendix 6: PowerShell deployment script

This appendix was added November 2024.

As Microsoft is deprecating VBScript (see https://learn.microsoft.com/en-us/windows/whats-new/deprecated-features#deprecated-features), I thought I should add an alternative deployment script. I should be clear here: I don't consider myself to be anything other than an amateur with PowerShell ... so use the following with caution. However I have, of course, tested the following and it appears to work well. If you're wondering why I didn't just get some AI tool to just re-write the above VBScript for me: I did. Two of them. And neither of them produced a workable PowerShell script.

So, to use PowerShell for deployment is very similar to using VBScript. 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 PowerShell script will use the RegAsm tool to create the .tlb file dynamically).

Copy all of the code below. Create a new file using Notepad (you can use the PowerShell ISE if you wish) or whichever text editor you prefer and paste all of the code into it. Change the value assigned to $FileName (at the top of the script) from "BlogProject.dll" to the name of your .dll file (just the filename including extension, not the full path). Save the file as a .ps1 file (eg called 'installer.ps1') into the same directory as the .dll file.

An extra 'challenge' with PowerShell script files (ie .ps1 files ... compared to .vbs files for VBScript) is that, by default, you cannot just double-click them to run them. You can change this behaviour but it is designed to be this way ... and you certainly shouldn't get your end-users (that you are deploying to) to change this behaviour.

So the 'normal' way to run a PowerShell script is:

• Open PowerShell at the folder containing the PowerShell script … either

  • Open PowerShell at the Windows button (open just PowerShell … not 'Windows PowerShell ISE') then navigate to the folder that contains the installer.ps1 file, or

  • Navigate to the folder that contains the installer.ps1 file using File Explorer, click in the 'path' text box then type 'powershell' (without the quotes, not case sensitive)

• Type '.\installer.ps1' (that is dot, backslash, filename) without the quotes then press enter

... which is a bit of a pain to be getting your users to do. So a simpler alternative is to create a shortcut file to the installer.ps1 file which will run the script:

• Create a normal shortcut to the installer.ps1 file (in this example, where the shortcut is in the same folder)

• Edit the Properties of the shortcut (right-click on it and select Properties) and change the Target to: powershell.exe -command "& '.\installer.ps1'"

• ... that's a lot of fiddly punctuation characters so see the 'code box' just below

• Save

Note that powershell.exe will be auto-expanded to e.g. "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe".

The shortcut can then be double-clicked to run the PowerShell script.

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).