Calling .Net Assembly from VBA

Stuck in VBA?  You don’t have to be.  You can move all your business logic code from VBA to a .Net assembly.  It’s easier than you might think and this post will show you how.

First you need to create a new Class Library project and below is the basic structure of a COM-callable wrapper for your .Net assembly.

// need this so we can decorate our classes with ClassInterface
//  and ComVisible attributes
using System.Runtime.InteropServices;

// namespace should be the same as assembly name
//  so when VBA calls it via New or CreateObject() it will use same reference name
//   and prog ID
//   ie. New YourNetAssembly.TestFunc or CreateObject("YourNetAssembly.TestFunc")
namespace YourNetAssembly
{
    // need to decorate class with following attributes
    //  so we can access its members using intellisense in VBA editor
    [ClassInterface(ClassInterfaceType.AutoDual), ComVisible(true)]
    public class TestFunc
    {
        public int Add(int num1, int num2)
        {
            return num1 + num2;
        }
    }
}

 

And when you are ready to compile or build, the next thing you need to do is check the Make assembly COM-Visible in your project’s Properties –> Application –> Assembly Information….

Now if you are developing your VBA application (be it MS Access or MS Excel) locally or on the same computer where you build your .Net assembly, you can have Visual Studio register your COM automatically by checking the Register for COM interop in your project’s Properties –> Build.

But if you are developing your VBA application remotely or on a different computer, then you will need to copy your .Net assembly to that computer and manually register it using regasm.exe.  You might want to create a batch file containing the following command so you don’t have to type it every time.

c:\Windows\Microsoft.NET\Framework\v4.0.30319\regasm.exe YourNetAssembly.dll /codebase /tlb

Note the correct .Net Framework version that your .Net assembly is created in.  Each version of the .Net Framework will have its own folder.  Also this command is run from the VBA application folder where the .Net assembly resides.  Yours might have a different setup so take note.

/codebase option adds the .Net assembly’s path on the disk to the Windows registry.  /tlb option generates the type library and saves it on the same folder as your .Net assembly.

Note that you need administrator privileges on the computer to generate a type library, be it via Visual Studio or regasm.exe.  If you already have an administrator privilege, you need to open Visual Studio or the command prompt as admin by right-clicking and selecting Run as administrator.

Once registered, you then add a reference to it from your VBA application.  From the VBA editor, go to Tools –> References… and look for your .Net assembly in the list of Available References.  Its name should be the assembly name.  You can check the assembly name in your project’s Properties –> Application under Assembly name.  Once added, you can now start calling your .Net assembly from VBA.

There are two ways you might want to call your .Net assembly.  Using the New keyword, or using CreateObject().

Dim objTestFunc As New YourNetAssembly.TestFunc
' OR ...
Dim objTestFunc As Object
Set objTestFunc = CreateObject("YourNetAssembly.TestFunc")

Dim intResult As Integer
intResult = objTestFunc.Add(1, 2)
MsgBox intResult

 

Great, but how about debugging or stepping into the .Net assembly?  Well if you have both Visual Studio and VBA development environments on one machine, then it would be easier.  You just set a breakpoint inside your .Net assembly in Visual Studio.  Then in your project’s Properties –> Debug, enter the full pathname to the program that runs your VBA application (maybe MS Access or MS Excel) in Start external program.  Then in the Command line arguments, enter the full pathname to your VBA application.  Save changes then press F5 to run.  This will run MS Access or MS Excel (whichever one you are using) which in turn runs your VBA application.  When your VBA application calls your .Net assembly, it will break into the Visual Studio debugging environment.

What about if your VBA development environment is on a different machine?  Well hope is not lost yet.  Assuming you have a debugger program installed on that machine, you can place a Debug.Assert(false); or Debugger.Break(); in your .Net assembly and this will force it to go into debugging mode when it hits that code and open the debugger program.  Be sure to copy the PDB (debug) file for your .Net assembly plus the source code so you can step through .Net code.

        public int Add(int num1, int num2)
        {
            System.Diagnostics.Debugger.Break();
            // OR ...
            System.Diagnostics.Debug.Assert(false);

            // if Debugger.Break() does not work properly i.e. it does not return back
            //  to the VBA application after debugging, 
            //  then use Debug.Assert(false) instead

            return num1 + num2;
        }

 

So that’s all there is to it.  Easy eh?  So start coding away then!

EDIT:

Actually you don’t need to check the Make assembly COM-Visible in your project’s Properties –> Application –> Assembly Information…. unless you want to expose all your public classes in the assembly.  And besides since you still need to decorate your public classes with COM attributes, might as well just use COM attributes to select only those ones you want to be COM-visible.

I found this great article that summarizes what you need to do: Best Practice in Writing a COM-Visible Assembly (C#).  I didn’t bother adding the ProgId attribute to my COM-visible classes though, but the rest I did.  Plus I created an interface for each, as was suggested in the article.  Nice thing having an interface is you can control which methods in your COM-visible classes you want to expose, giving you a more granular level of control.

Access: Type Mismatch Run-time Error After Converting to ACCDB Format

You might receive a Run-time error ’13’: Type mismatch after converting a Microsoft Access database from .mdb to .accdb format.  The solution might be as easy as removing the reference to the ADO object library, if you are not using it.

  • Open the Access database in question.
  • Open the Visual Basic Editor (VBE) by pressing ALT+F11.
  • On the Tools menu, click References.
  • In the References dialog box, uncheck Microsoft ActiveX Data Objects, and click OK.

Access VBA: How To Call A Windows Form Application with Arguments

To call a Windows Form application (*.exe) with arguments in VBA, you use the Shell() function.

Shell "C:\Dev\SampleWinFormWithArgs.exe arg1", vbNormalFocus

 

Inside the Windows Form application, you get the command line arguments using Environment.GetCommandLineArgs().

string[] args = Environment.GetCommandLineArgs();
// you can call GetCommandLineArgs() anytime, anywhere
foreach(string arg in args)
{
    // do stuff
    // note that the first argument string is the application pathname
}

SQL Server: How to Linked Server an Access Database

Below is the script to create a linked server to an Access database:

EXEC master.dbo.sp_addlinkedserver 
@server = N'MyLinkedServerAccessDB',
@srvproduct = N'Access',
@provider = N'Microsoft.Jet.OLEDB.4.0',
@datasrc = N'C:\MyAccessDB.mdb'

 

Plus you also need to create the linked server login.  What’s important to note here is that the password for the admin account in the Access database is blank by default, unless you have set the password.

EXEC master.dbo.sp_addlinkedsrvlogin 
@rmtsrvname = N'MyLinkedServerAccessDB',
@useself = N'False',
@locallogin = N'sa',
@rmtuser = N'admin',
@rmtpassword = ''

General: On a 64-bit OS/machine, what will be the issues in running your application that uses Access database directly or indirectly using SQL Server’s Linked Server object

If you have an application, say in .NET, that uses an Access database in some way or the other, or uses SQL Server that in turn links to an Access database through the Linked Server object, and you want to run your application and also the SQL Server on a 64-bit OS/machine, you might be in for a lot of surprises.

 

Here is why:

  • The Microsoft.Jet.OLEDB.4.0 is only available on 32-bit.  Same with Microsoft.ACE.OLEDB.12.0 if you are using a later version of Access like 2007 and up.  You can still run them on a 64-bit OS/machine in WoW64 (a subsystem in 64-bit Windows that allows 32-bit applications to run on them).  Only problem is that the application that uses them need to run in 32-bit mode.  So much for running your application on 64-bit eh.  But fear not, there is a solution, although many of you might stay away from it, and that is linking your Access database in SQL Server and  having your application connect to the SQL Server instead.  So now you can run your application in 64-bit mode.

 

  • If you have a SQL Server that links to an Access database through the Linked Server object, you cannot install a 64-bit version of SQL Server on a 64-bit OS/machine or else your linked server to the Access database will not work.  You have to install a 32-bit version of the SQL Server.  Yes, that is the only solution, for now, until Microsoft comes up with a 64-bit version of the Access database engine, if that will still come.

 

  • If you have created a link to your Access database from your SQL Server, you might encounter a similar collation problem below when running your T-SQL queries involving joins to the linked server Access database:

Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

 

One way to solve this problem is by adding the COLLATE DATABASE_DEFAULT to every text fields that you are comparing with either on the WHERE or JOIN clause, such as the example below:

SELECT *
FROM 
    SQLServerTable1 s
    INNER JOIN LinkedServerAccessDB...AccessDBTable1 AS a
        ON s.DateField1 = a.DateField1
        AND s.IntField1 = a.IntField1
        AND s.TextField1 COLLATE DATABASE_DEFAULT = a.TextField1 COLLATE DATABASE_DEFAULT
        AND s.TextField2 COLLATE DATABASE_DEFAULT = a.TextField2 COLLATE DATABASE_DEFAULT

 

 

  • Be aware that in using a linked server to an Access database that any DELETE statements that you execute within a transaction (where you can commit or rollback) will not work (at least for me it did not work) and will give you the below error message:

The requested operation could not be performed because OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “LinkedServerAccessDB” does not support the required transaction interface.

 

Solution to this is to bring all DELETE statements out of the transaction and execute them after committing the transaction.

 

So that’s it for this topic.  If you have any questions, feel free to comment :).