Custom Search
 


Create a DLL by CSharp or VB.Net for VBA



I was updating an application I created in Access VBA and came across a scenario that I needed to encapsulate some of the functionalities in a DLL. I used to use VB (Visual Basic 5 and 6) to create DLLs many years ago. The best way nowadays is to use C# or VB.Net to create the DLL.

Creating a DLL in C# or VB.Net is not that hard as long as you have object oriented programming experience or knowledge. After creating the DLL, I tried to reference it from Access VBA editor but kept getting the message "Can't add a reference to the specified file.". After some research on this issue, I learned some new tricks about (1) how to correctly create a DLL for Access or Excel (or any Microsoft Office applications or VB6 applications), (2) how to correctly register a C# or VB.Net DLL, and (3) how to correctly reference the DLL within Access VBA or Excel VBA.

In this article, I'm going to show you how to correctly create a C# or VB.Net DLL in Visual Studio and use it inside MS Access, Excel VBA, or VB6 applications. Hope the tips can save you a couple of hours or days of headaches.

1. Create a new C# (or VB.Net) project and select Class Library as the template type.

Save the project (and solution) as SimpleCalc.

Below I created a simple calculation class for testing. This class has two variable members and one Add() method. The method adds two integer numbers and returns the result.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace SimpleCalc
{
    public class Calc
    {
        private int numberOne = 0;
        private int numberTwo = 0;

        public void SetNumberOne(int number)
        {
            numberOne = number;
        }

        public void SetNumberTwo(int number)
        {
            numberTwo = number;
        }

        // Add two integers
        public int Add()
        {
            return numberOne + numberTwo;
        }
    }
}

2. Configure project properties to make it COM visible.

Open project properties dialogue window. Go to menu Project -> SimpleCalc Properties.

When the project properties window is opened, click Application tab, and then click Assembly Information button.

On the Assembly Information window, check Make assembly COM-Visible.

Applications created in VB or VBA are COM-based applications. So the class library you created in C# or VB.Net must be created as a COM visible assembly. This allows COM-based applications to call the class library's member variables and methods.

3. Register for COM Interop.

In addition to making the DLL COM-visible, we also need to register the assembly as a COM component in the Windows registry. There are a few ways to do it based on your circumstances.

For a development machine, we can check "Register for COM Interop" setting in Visual Studio. On the project properties window, click Build tab. Then check Register for COM Interop (Based on your version of Visual Studio, it could be on a different tab). This makes Visual Studio do two things automatically when the project is compiled. First, it will generate a .tlb type library file. Second, it will register class information for the COM component in Windows registry.

The Register for COM interop property specifies whether your application will expose a COM object to client applications so that they can interact with your COM object transparently.

Here is what is registered in Windows registry for the compiled assembly so that COM clients can use the .NET class transparently. Note that the path to the DLL is stored in CodeBase entry.

To register the assembly on other computers such as production machines, you can register the assembly by using RegAsm.exe. See this article for details.

4. Compile the project.

Build the solution. Then go to bin folder. Depends on whether your build is a Debug or Release build, you can find the DLL and .tlb type library file in either Debug or Release folder under the bin directory.

In our case, it's D:\CSharp\SimpleCalc\SimpleCalc\bin\Release

5. Copy the type library file to Windows system folder.

Windows system folder C:\WINNT\system32 is the default location for DLLs and type libraries so we copy type library SimpleCalc.tlb to it.

6. Reference the type library from Access VBA editor.

First, create a new Access database and open Visual Basic code editor. In the menu cross the top, click Tools -> References...

When the References window is opened, click the button Browse.

Then browse to folder C:\WINNT\system32 and select file SimpleCalc.tlb and click Open.

After done, SimpleCalc will appear in your reference list and you need to move down in the list to find and check it. Then click OK to close the Reference window.

You may remember in the registry screenshot above, there is a DLL path value in CodeBase entry. VBA will use this registry information to find which DLL to call. In our case, it's D:\CSharp\SimpleCalc\SimpleCalc\bin\Release\SimpleCalc.dll

7. Use the DLL in your VBA code.

To use the variables and methods in the C# DLL, we need to call the DLL inside VBA. Add a button to the Access form and then add a click event to it. In the click event we call a function called test() which is created within a VBA module.

Below is a sample VBA to call SimpleCalc.dll. test() function invokes the .Net DLL by creating a new object from Calc class and then call its methods.

Public Function test()
    Dim lngResult As Long
    
    Dim objCalc As SimpleCalc.Calc
    Set objCalc = New SimpleCalc.Calc
    
    objCalc.SetNumberOne (3)
    objCalc.SetNumberTwo (6)
    
    lngResult = objCalc.Add()
        
End Function

If we debug and step through the test function, we will see the result is 9 as it's calculated inside the C# DLL and returned to VBA. You can see the result value either in Immediate window or by moving mouse over the lngResult variable.

In this article, we have gone through the steps to create a .Net C# DLL and then use it inside Access VBA. It brings the power of .Net into Microsoft Office applications. The limitation in this article is that Visual Studio registered our DLL and type library automatically for our development machine only. We haven't covered how to deploy the DLL to a production machine. In this case, check out this article about how to use Regasm.exe (Assembly Registration Tool) to register DLL on a target machine or production computer. Note that you can also create a setup package for your application and add regasm.exe as a custom command to the installer package.

Happy Coding!


Copyright© GeeksEngine.com



Related Articles:

1.How to correctly reference and call a DLL
2.How to register a C# or VB.Net DLL


Other Recent Articles from the MS Access category:

1.How to easily get a list of field names in MS Access
2.How to count distinct records in MS Access
3.How to do transaction based processing in MS Access
4.How to open a document (local/network file or web page) from MS Access
5.How to use ADOX to create unique composite index - the VBA approach
6.How to do cross-table update queries in MS Access - the right way
7.Three efficient ways to get the number of records by using VBA
8.How to create a composite unique index (not as a primary key) in MS Access
9.Use VBA to get the correct number of records in a Recordset object
10.Disable Access Prompt when a record is changed, table deleted, or action queries run
11.How to hide and unhide a MS Access object
12.How to return multiple values from a VBA function (Part 3)
13.How to return multiple values from a VBA function (Part 2)
14.How to return multiple values from a VBA function (Part 1)
15.Three ways to programmatically duplicate a table in MS Access by VBA
16.How to correctly reference and call a DLL
17.How to register a C# or VB.Net DLL
18.Email address validation by Regular Expressions using VBA
19.Fix MS Access error: Query must have at least one destination field
20.How to unselect radio buttons in MS Access after it has been selected
21.How to Change Query Timeout Value for MS Access SQL Queries
22.What is Northwind Traders database

Copyright © 2014 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy