Exporting C function from C# and using it in VBA

Question

I use the solution from Robert Giesecke http://sites.google.com/site/robertgiesecke/Home/uploads/unmanagedexports to export functions from managed code to unmanaged code. The solution works pretty good, but there is a problem using the solution with office (excel).

I tried to develop a DLL which

connects to SQLServer
by using SQLAuthentication
passing the name of the database
passing the SQL-Statement
and returning the result

so the user of the DLL cannot see the password, I know it can be done by using special tools. This way to do it is sufficient for our requirement.

The code in C#:

using System;
using System.Collections.Generic;
using System.Text;
using RGiesecke.DllExport;
using ADODB;
using System.Xml;
using System.IO;
using System.Security.Cryptography;
using System.Runtime.InteropServices; 
using System.Windows.Forms;

namespace SqlConRVT
{
public static class SqlConRVT 
{ 
    [DllExport("SqlConRVT", CallingConvention = CallingConvention.StdCall)] 
    [return: MarshalAs(UnmanagedType.IDispatch)] 
    public static Object OpenRecordset ([MarshalAs(UnmanagedType.AnsiBStr)] string databaseName, 
    [MarshalAs(UnmanagedType.AnsiBStr)] string commandText) 
    {
    if (String.IsNullOrEmpty( databaseName)) throw new ArgumentNullException("databaseName"); 
    if (String.IsNullOrEmpty( commandText)) throw new ArgumentNullException("commandText");
    try 
    { 
        var connection = new ADODB.Connection();
        var intConnectionMode = (int) ConnectModeEnum.adModeUnknown;
        var username = Crypto.DecryptMessage("XEj0PC2lMIs=", "FinON");
        var password = Crypto.DecryptMessage("7YIDPO7eBoFAhskAX6JGAg==", "FinON");
        connection.Open("Provider='SQLOLEDB';Data Source='PETER-PC\\SQLEXPRESS'; Initial Catalog='" + databaseName + "';", username, password, intConnectionMode);
        var rs = new Recordset();
        rs.Open(commandText, connection, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic, -1);
        return rs; 
    } 
    catch (Exception ex) 
    { 
        // an exception in a DLL will most likely kill the excel process 
        // we really dont want that to happen 
        MessageBox.Show(ex.Message, ex.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error); 
        return null; 
    } 
} 
} 

public partial class Crypto
{
    public static string DecryptMessage(string encryptedBase64, string password)
    {
        TripleDESCryptoServiceProvider des = new TripleDESCryptoServiceProvider();
        des.IV = new byte[8];
        PasswordDeriveBytes pdb = new PasswordDeriveBytes(password, new byte[0]);
        des.Key = pdb.CryptDeriveKey("RC2", "MD5", 128, new byte[8]);
        byte[] encryptedBytes = Convert.FromBase64String(encryptedBase64);
        MemoryStream ms = new MemoryStream(encryptedBase64.Length);
        CryptoStream decStream = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);
        decStream.Write(encryptedBytes, 0, encryptedBytes.Length);
        decStream.FlushFinalBlock();
        byte[] plainBytes = new byte[ms.Length];
        ms.Position = 0;
        ms.Read(plainBytes, 0, (int)ms.Length);
        decStream.Close();
        return Encoding.UTF8.GetString(plainBytes);
    }
}
}

My Code in VBA:

Declare Function SqlConRVT Lib _
"C:\Users\Administrator\Documents\Visual Studio 2008\Projects\SqlConRVT\SqlConRVT\bin\Debug\x86  \SqlConRVT.dll" (ByVal databaseName As String, ByVal commandText As String) As Object

Sub SQLCon()
Dim x As Object
x = SqlConRVT("Adressen", "Select * from tblAdressen")
End Sub

In the C# DLL and in all client applications I reference "Microsoft ActiveX Data Object 2.8 Library".

I tried to use the exported 64bit DLL with C#, works fine. I tried to use the exported 64bit DLL as a static class with C#, works fine. I tried to use the exported 32bit DLL with VB6, the application crashes. I tried to use the exported 32bit DLL with VBA (Excel), the application crashes.

I checked the existence of the exported function in the 32bit DLL with dependency walker.

Why can't I use the 32bit DLL with office (Excel)?

Of course I have 32-bit Office!

Your "simplified example" works fine, the class is given back correctly!

I reduced my example:

using System;
using System.Collections.Generic;
using System.Text;
using RGiesecke.DllExport;
using ADODB;
using System.Xml;
using System.IO;
using System.Security.Cryptography;
using System.Runtime.InteropServices;
using System.Windows.Forms;

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]

static class SqlConRVT
{
    [DllExport(CallingConvention = CallingConvention.StdCall)]
    [return: MarshalAs(UnmanagedType.IDispatch)]
    //[return: MarshalAs(UnmanagedType.I4)]
    //[return: MarshalAs(UnmanagedType.AnsiBStr)]

    static Object GetNewObject([MarshalAs(UnmanagedType.AnsiBStr)] String databaseName,
    [MarshalAs(UnmanagedType.AnsiBStr)] String commandText)
    {
        var test = new StreamReader("C:\\lxbu.log");
        return test;
        //var rs = new Recordset();
        //return rs;
        //int A = 1;
        //return A;
        //String A = commandText;
        //return A;
     }
 }

My Code in VBA:

Declare Function GetNewObject Lib "C:\Users\Administrator\Documents\Visual Studio 2008\Projects\An\An\bin\Debug\x86\An.dll" (ByVal databaseName As String, ByVal commandText As String) As Object

Sub An1()
Dim x As Object
Set x = GetNewObject("Adressen", "Select * from tblAdressen")
End Sub

If I try to return an int-value -> works correct! If I try to return a string-value -> works correct! If I try to return an object (e.g a recordset-object or a streamreader-object) Excel crashes? There must be a stupid little error!

Thank you Robert - as everytime your code is perfect! I can see the content of the streamreader object if I use the following code in VBA

MsgBox instance.ReadtoEnd()

and the result is:

"abc Äö ~éêè @dkfjf -> Added fro VBA"

The problem is definitly the ADODB.connection!!!!!

    [DllExport(CallingConvention = CallingConvention.StdCall)]
[return: MarshalAs(UnmanagedType.IDispatch)]
static Object GetNewObject([MarshalAs(UnmanagedType.LPStr)] String databaseName, [MarshalAs(UnmanagedType.LPStr)] String commandText)
{
    //if (String.IsNullOrEmpty(databaseName)) throw new ArgumentNullException("databaseName");
    //if (String.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
    {
        var connection = new ADODB.Connection();
        //var rs = new Recordset();
        StreamReader sr = new StreamReader("C:\\lxbu.log");
        //var intConnectionMode = (int)ConnectModeEnum.adModeUnknown;
        //var username = "...";
        //var password = ".........";
        //connection.Open("Provider='SQLOLEDB';Data Source='PETER-PC\\SQLEXPRESS'; Initial Catalog='" + databaseName + "';", username, password, intConnectionMode);
        //rs.Open(commandText, connection, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockOptimistic, -1);
        return sr;
    }
}

If I use "var connection = new ADODB.Connection();" Excel crashes. The problem is using ADODB in 32bit DLL (C# and using the 64bit-DLL no problem). There's no problem (!!!) with your solution!

Why don't you use VSTO or COM. Taking a trip into unmanaged land makes things way more complicated than you need. — David Heffernan
– David Heffernan, Commented May 27, 2011 at 19:35
@David: COM is unmanaged, but yeah for accessing C# libraries from VBA I definitely would advise the COM route. — Ben Voigt
– Ben Voigt, Commented May 27, 2011 at 21:18
COM needs to be registered, wich requires administrative rights. Sometimes it's not possible... — aurel
– aurel, Commented Mar 4, 2014 at 14:14

to StackOverflow · Accepted Answer · 2011-05-27 20:11:15Z

I can't answer your question, but maybe the 3rd party code you are using has undocumented restrictions or assumptions that don't work when running in Office.

However there are other ways to export a managed API to Excel VBA. The solution I use is the following:

Define a set of dual COM Interfaces in IDL for the API you want to expose from .NET. There should be one principal Factory interface that can be used as the main entry point from VBA. The Factory must be able to directly or indirectly instantiate any of the objects you want to expose (the technique described below does not allow VBA to create objects directly, so you need to do so using the Factory class).
Generate a TypeLib from the IDL using MIDL.EXE, and use TlbImp to expose it to .NET
Create a .NET Class Library project that references the COM Interop assembly generated by TlbImp, and write classes that implement the API.
Create a VSTO project. In the ThisWorkbook.Workbook_Open event handler, instantiate the main Factory object and pass it as a parameter to a VBA macro:
```
IMyMainFactory factory = // ... create factory
ThisApplication.Run("RegisterFactory", factory, Type.Missing, ...);
```

In the VSTO workbook, create the macro RegisterFactory and save the factory class instance in a global variable:

Option Explicit
Private objFactory As Object

Public Sub RegisterFactory(Factory As Object)
    Set objFactory = Factory
End Sub

Build the VSTO application, and convert the VSTO workbook to an xla add-in. You can do this using VB, VBA or VBScript code something like:
```
Set w = Application.Workbooks.Open("MyVstoWorkbook.xls", ...)
w.IsAddin = True
w.SaveAs "MyVstoAddIn.xla", 18, ...
```

The upshot of the above is that whenever you load the add-in MyVstoAddIn.xla, it will instantiate your factory and store it in a global variable in a VBA module. You can access this from VBA code (which will also have a reference to the TypeLib generated above) and you're up and running.

There are a number of advantages over standard COM Interop - not least of which is that your VSTO add-in has its own AppDomain and application configuration file, so you don't conflict with other managed code.

I am the author of the solution that Peter mentioned, and I can assure you that I went through quite some length to not rely on any assumptions. However, your solution looks cool. :-)

Robert Giesecke · Accepted Answer · 2011-05-30 08:15:22Z

1

As I asked you in our mail conversation: Do you really use a 64Bit office?
This is very unlikely, that's why I wanted to check that upfront.

Even if you do use a 64Bit Office, it should still work. One thing you have to remember: When you call a DLL function from VBA, the string type that is passed will be an LPStr (pointer to an Ansi Char). AnsiBStr should do it as well. But the classes you define will use the BStr, which is standard for COM.

Here's a simplified sample, which does neither require the MSSQL client libs nor ADODB. (So less points of failure) Disclaimer: While I do have a 64Bit Windows, I only have an x86 Office (2007) installed:

[ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
public class Sample
{
   public string Text
   {
      [return: MarshalAs(UnmanagedType.BStr)]
      get;
      [param: MarshalAs(UnmanagedType.BStr)]
      set;
   }

   [return: MarshalAs(UnmanagedType.BStr)]
   public string TestMethod()
   {
      return Text + "...";
   }
}

static class UnmanagedExports
{
   [DllExport(CallingConvention = CallingConvention.StdCall)]
   [return: MarshalAs(UnmanagedType.IDispatch)]
   static Object CreateDotNetObject([MarshalAs(UnmanagedType.LPStr)] String text)
   {
      try
      {
         return new Sample { Text = text };
      }
      catch (Exception ex)
      {
         MessageBox.Show(ex.Message, ex.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
         return null;
      }
   }
}

And this is how to use it from anything VBA-ish (e.g. Excel or Access)

Declare Function CreateDotNetObject Lib "The full path to your assembly or just the assembly if it is accessible from Excel" (ByVal text As String) As Object
Sub test()

  Dim instance As Object

  Set instance = CreateDotNetObject("Test 1")
  Debug.Print instance.Text

  Debug.Print instance.TestMethod

  instance.text = "abc 123" ' case insensitivity in VBA works as expected

  Debug.Print instance.Text
End Sub

If this is working for you, we can get from there to wherever you want to go. But it would be important to know what kind of office version (CPU platform) you have, and whether this simple sample works first.

edited May 30, 2011 at 8:15

answered May 30, 2011 at 8:09

Robert Giesecke

4,31424 silver badges22 bronze badges

2 Comments

Robert Giesecke Over a year ago

Peter, I haven't checked the docs, but I kinda doubt that StreamReader is ComVisible(true).

Peter Sapl Over a year ago

I hate Microsoft! Use "Microsoft ActiveX Data Object 2.5 Library" instead of "Microsoft ActiveX Data Object 2.8 Libraray" and everything is ok, on WinXP aswell as Windows 7 (64-bit). Thanks a lot to you Robert, your solution is great!

Robert Giesecke · Accepted Answer · 2011-05-31 10:46:21Z

I will ask you some points again, since you kinda dodged some questions (here and via email before) and I am not really sure what you actually tried:

You did not set the CPU platform of your project to x86?
If not, you pick the assembly on the x86 sub folder?
You tried to use UnmanagedType.LPStr for your string parameters?
While I have had my fair of managed/unmanaged Interop, I never had to handle VBA before, so I am not sure about AnsiBStr.

Don't even try loading anything else than the x86 DLL, cannot work.
Do yourself the favor and change the CPU Platform of your project to x86 and delete the current output folder. I am afraid all those different versions got mixed. After a rebuild, you should then only have the x86 one, which should work just fine.

And just to be sure about that working fine part: Try this one, which is a variant of the one you stated that it did not work. And please, try to not do anything else. Best way to test it is a new project created from my template and paste the code below in the sample export class.

C#

[DllExport]
[return: MarshalAs(UnmanagedType.IDispatch)]
static Object CreateDotNetObject([MarshalAs(UnmanagedType.LPStr)] String text)
{
   try
   {
      var testFileName = Path.Combine(Path.GetTempPath(), "VbaTestFile.txt");
      if (!File.Exists(testFileName))
         File.WriteAllText(testFileName, "abc Äö ~éêè @dkfjf", Encoding.UTF8);

      using (var writer = File.AppendText(testFileName))
         writer.WriteLine(text);

      return new StreamReader(testFileName);
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message, ex.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
      return null;
   }
}

VBA

Declare Function CreateDotNetObject Lib "Full path to your assembly" (ByVal text As String) As Object

Sub Test()

  Dim instance As Object

  Set instance = CreateDotNetObject("-> Added fro VBA")
  Debug.Print instance.ReadToEnd()
  instance.Close
End Sub

What did you see in VBA's immediate window?

Collectives™ on Stack Overflow

Exporting C function from C# and using it in VBA

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related