In the typical work environment, it is very common for non-programmer to try to use VBA to create process automation. And it is no doubt a fast way to implement automation with the least computing knowledge. However, when the complexity is high and coupled with high volume, this will significantly slow down the PC and creates bad user experience. Especially when we are doing it for another user, we might wonder if there is a better solution?
In this post, I explore how I can use C# to code automation functions and to implement it onto VBA. This is nothing new so all the information required was obtained from other experts online. Specifically, I got most of the information
here.
Step 1:
Using Visual Studio Community 2015, create a class library:
Step 2:
Communication with VBA require the code to be in "COM Library", so to do this
a) You have to check "Make Assembly COM-Visible" checkbox under
Project -> Properties -> Assembly Information
b) Next, we will need to register it so that VBA will be able to recognize this when we start coding in VBA. But you also won't want to register all the library you developed on the developing machine as it will make your PC very messy. So I will come back to this setting again later to explain how to remove it.
Under the same property window, go to the build tab, Project -> Properties -> Build.
Check the ‘Register for COM interop’ checkbox.
Step 3:
The setup is completed and you can start writing your code. The following is a simple code to output a string:
using System;
using System.Collections.Generic;
using System.Text;
namespace DotNetLibrary
{
public class DotNetClass
{
public string DotNetMethod(string input)
{
return "Hello " + input;
}
}
}
Step 4:
Compile and start using in VBA! For example, we now try to use Excel to call the above method.
In Excel, enter the VBA programming window by pressing
ALT+F11. You will find that library you created under
Tools -> Reference. In this case, the library is called
blog.
To access the DotNetMethod, you can invoke by using the following VBA Code:
Sub test()
Dim clest As New DotNetClass
Dim test As Variant
MsgBox (clest.DotNetMethod("ABC"))
End Sub
Run the macro ("F5") and you will see the msgbox:
Step 5:
Up till now, what you have seen how to get to use functions in VBA in your own PC. But suppose you want to develop it on your own PC and implement it onto another PC, how can you do it?
So starting from Step 2b, we don't really need to check the "Register for COM Interop" because what we really need is just the DLL file. But now that we checked it, how can we remove it? This is simple, we just need to uncheck and compile your project again and it will be unregistered.
So once you unregistered, whatever you compile will not appear on VBA Reference. You will need to take the DLL and copy to the target PC and start the registration manually.
Register:
To register you will need to use "regasm" function from Visual Basic Command Prompt. You can easily access this by typing "Dev" after pressing the "Window" key:
Navigate to your project directory containing the DLL file and now you are ready to use the "regasm" function (assuming the name of your dll is "dllname"). Type the following to create the ".tlb" required for VBA to recognize your DLL file.
regasm /tlb dllname.dll
After typing the above command, you will see that the library will appear in the same place mentioned in Step 4,
If you want to unregister, you can type the following command:
regasm /u dllname.dll
As you can see, all these registering are not very transparent to the developer on what exactly is happening. So there will be times where you are not able to unregistering. And when that happens, a quick way is to open your project and repeat what is mentioned at the beginning for Step 5, i.e. check option -> compile, uncheck option -> compile. This will let Visual Studio do the unregistering for you.
If you want a quick start, you can use the sample project
here. Enjoy coding!
