Create a DLL by CSharp or VB.Net for VBA--摘自网络
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!
--------摘自网络
