For today I was thinking to present an algorithmic problem, but somehow I have decided to show something more useful. It is a Step-by-step instruction for the creation of a C# object in VBA. You know, that references, which you can add through Tools>References from the VB Editor.
Let’s start with it. First, make sure that you run Visual Studio as an administrator like this:
Then select File>New Project> Class Library. And paste the following code in the new class:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
namespace LibraryVBA { public class clsInformation { private int years = 31; private int days = 300; public int age = 99; public string strInformation() { return "My web site is vitoshacademy.com"; } public int Days { get { return days; } } public int Years { get { return years; } set { if ((value>0 ) && (value<10)) { years = value * 2; }else { years = value; } } } public string strInformationMore() { return this.strInformation() + " and my second site is herecomesthecode.com"; } public string strInformationMoreMore() { return this.strInformationMore() + "Number of days: " + Days; } } } |
Pretty much, it has 3 fields, 2 of which are private. “Days” is read only, Years – not. We have also 3 other string functions, just to have something. Once we are ready, select “Project” and then ProjectName Properties. In my case it is LibraryVBA Properties, because my project is named LibraryVBA.
Now the magic starts. Make sure to click on Assembly Information and to give meaningful description, it would be the one showing in the libraries in Tools>References, once we go to the VB Editor. Last but not least here – check on “Make assembly COM-Visible”.
Then on “Build”. There check “Register for COM interop” and change bin\Debug\ to bin\Release01 (or bin\ReleaseN)
So far so good. Press F6 and hope for the magic to start. Usually nothing should happen, just a small notice on the lower left corner of your visual studio, saying “Build started” and the “Ready”. And yup, in the specified folder we should have a LibraryVBA.tlb file. (or something similar).
Now, open the beloved VBEditor and go to Tools>References. Press on Browse and navigate to that *.tlb file. Now “OK”. Now, you may refer the object by its namespace and class. Like me -> LibraryVBA.clsInformation. The VB Editor does not support you with IntelliSense for the new object, but still it is quite ok. This is what you can do with it:
The VBA code is here:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Option Explicit Public Sub Librarian() Dim objLib As New LibraryVBA.clsInformation Debug.Print objLib.age Debug.Print objLib.Days Debug.Print "-----------------------" Debug.Print objLib.Years objLib.Years = 100 Debug.Print objLib.Years objLib.Years = 9 Debug.Print objLib.Years Debug.Print "-----------------------" Debug.Print objLib.strInformation Debug.Print objLib.strInformationMore Debug.Print objLib.strInformationMoreMore End Sub |
Pretty much, that’s all! Now you may proudly say all over the world, that you are able to create your own libraries in C# and use them in VBA. Enjoy it! 🙂