Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using a C# dll inside EXCEL VBA

Tags:

c#

vba

dll

I am running into a little problem here and need you guys' help.

I have a C# DLL exposed through COM interop. It is working alright, but apparently the deployment of C# interop object is a disaster and you need to regasm every time you update the DLL.

So I am wondering how I can use the functions from this C# DLL like the following: Or anything that I can call the functions by just putting the DLL and the spreadsheet together.

Declare Function getString Lib "<PATH of my DLL>" () as string

sub test()
   range("A1").value = getString
End Sub

Syntax might be wrong.

like image 395
Johnyy Avatar asked Jun 14 '11 06:06

Johnyy


People also ask

What is the most efficient way to use air conditioning?

The US Department of Energy recommends that you keep your thermostat setting to 68 degrees while you're awake and lower when away or sleeping in winters. While in summer, keep it at 78 degrees when at home.

Is it better to leave the AC on all day?

Your AC will actually run longer overall if it is left on all day instead of being shut off. If you turn it off for part of the day, it runs less and results in more energy savings for you. In almost all cases, it will save you money to shut off your AC while you are away from home.

How do you use AC for the first time?

For most central air systems, the process is simple. Simply move the switch on your thermostat from “Heat” to “Cool”. If your system was off entirely, you may need to move the switch from “Off” to “Cool” instead. Once you turn your system on, be sure to close any open windows to conserve energy.


2 Answers

You can do that, but you have to be aware of the differences of VBA and .Net.
First, you have to create an actual DLL (.Net assemblies are not), to do that, use this project template. Then again, you have to be aware of how to marshal stuff.
VBA only supports stdcall as calling convention and it can't really deal with Unicode for DLL functions. This isn't bad per se, as the default marshaling for String in .Net is what VBA is expecting (a pointer to an Ansi char). Also, stdcall is the default calling convention that I use for exports.

I'll reuse a sample I've create recently for another SO thread:

Put this in a project you created using my template:

[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]
   [return: MarshalAs(UnmanagedType.IDispatch)]
   static Object CreateDotNetObject(String text)
   {
      return new Sample { Text = text };
   }
}

This is how to call it from VBA:

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
like image 157
Robert Giesecke Avatar answered Sep 27 '22 23:09

Robert Giesecke


These are the using statements to put at the top of your class which are key:

using System.Diagnostics; using RGiesecke.DllExport;

Also ensure that you have a project started before the Nuget PM statement to install the template above. I am new at this - I am sure there are others as well. I am using AutoCAD VBA and got another error since it is 64 bit - I had to use PtrSafe (etc) in the Declare statement for VBA to continue without errors (see MS docs for this http://support.microsoft.com/kb/983043)

It worked btw!

My final code (based on the above)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
using System.Diagnostics;
using RGiesecke.DllExport;

namespace ClassLibrary3
{
    [ComVisible(true), ClassInterface(ClassInterfaceType.AutoDual)]
    public class Class1
    {
        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]
        [return: MarshalAs(UnmanagedType.IDispatch)]
        static Object CreateDotNetObject(String text)
        {
            return new Class1 { Text = text };
        }
    }
}

and my vba code:

#If VBA7 Then
    Private Declare PtrSafe Function CreateDotNetObject Lib "G:\gitRepository\VS\ClassLibrary3\ClassLibrary3\bin\Debug\ClassLibrary3.dll" (ByVal text As String) As Object
#Else
    Private Declare Function CreateDotNetObject Lib "G:\gitRepository\VS\ClassLibrary3\ClassLibrary3\bin\Debug\ClassLibrary3.dll" (ByVal text As String) As Object
#End If

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
like image 22
Etienne Bley Avatar answered Sep 27 '22 23:09

Etienne Bley