.NET

How to tie a C# .NET GUI to a callable C# .NET class library dll from Excel VBA: Guided Minimum Framework Example

Dev

Summary:

A minimum working example of how to tie a C# .NET GUI to a callable C# .NET class library dll from Excel VBA or another C# application.

 

This is a follow up to the non GUI example posted earlier

 

Project: AutomationComExample.zip

 

  • 1 Summary:
  • 2 Framework Structure
    • 2.1 ExampleTargetApp (GUI Application)
      • 2.1.1 MainWindow.xaml.cs
      • 2.1.2 HelloWorldService.cs
    • 2.2 ServiceDefinition
      • 2.2.1 IHelloWorldService.cs
    • 2.3 CallingDll
      • 2.3.1 ServiceProxy.cs
      • 2.3.2 ICallTargetApp.cs
      • 2.3.3 CallTargetApp.cs
  • 3 Example access from VBA

Framework Structure

The goal is to connect the piping features of WCF named piping class with the GUI features of the MainWindow class. C# does not support multiple inheritance, but that is fine. We only need an interface and C# supports that of course. We can create an interface from the GUI that the DLL will utilize to allow changes to the GUI from another application.

The framework consists of three projects within visual studio: ExampleTargetApp, ServiceDefinition, and CallingDll.

  • ExampleTargetApp contains the information for the GUI and handles what happens when elements of the GUI interact with the user, presumably via mouse and keyboard but could also be via function piping. This project also contains a service to make use of the pipes interface.
  • ServiceDefinition contains an interface for the functions to go through WCF pipes.
  • CallingDll uses the service interface to access functionality contained and defined in the ExampleTargetApp project

ExampleTargetApp (GUI Application)

MainWindow.xaml.cs

Contains the elements of the GUI such as Button1_Click() and SelectRadio1()

HelloWorldService.cs

Contains the service class using the service interface

ServiceDefinition

IHelloWorldService.cs

Contains the service interface definition

CallingDll

ServiceProxy.cs

Set up the service to work using the service contract defined in the ServiceDefinition project

ICallTargetApp.cs

Interface for calling DLL

CallTargetApp.cs

Functions for calling DLL through the proxy

 

Example access from VBA

vbacallingdll

Load the dll “CallingDll” by selecting browse then going to the project build directory: C:\dev\AutomationComExample\CallingDll\bin\x86\Debug\CallingDll.tlb

Excel VBA Code 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub SelectHello()
    Dim targetApp As Object
    Set targetApp = CreateObject("CallingDll.CallTargetApp")
    targetApp.SelectHello
End Sub
Sub SelectGoodbye()
    Dim targetApp As Object
    Set targetApp = CreateObject("CallingDll.CallTargetApp")
    targetApp.SelectGoodbye
End Sub
Sub SelectButton()
    Dim targetApp As Object
    Set targetApp = CreateObject("CallingDll.CallTargetApp")
    targetApp.SelectButton
End Sub

How to call a C# .NET class library from Excel VBA: Guided Example

Dev

I have made a GUI in C#. Now I want to create an API to use in vba to ease automation and make use of the team’s existing vba code. This guide gives a basic example of how to do that. Examples I found online were overly complicated and/or not so useful. This short guide should get you started in calling your C# .NET functions from Excel VBA. If you plan on using a GUI, see the other guide as this approach will run into problems. Please let me know if you have trouble.

 

Update: As it turns out, this does not effortlessly lead into a COM accessible GUI as I originally suspected. This documentation below works great until you try to add in the actual GUI application. For the GUI application, see this guide: How to tie a C# .NET GUI to a callable C# .NET class library dll from Excel VBA: Guided Minimum Framework Example

After making different “HelloWorldGUI via VBA/C#” style minimal working solution examples, I picked Named Pipes for now because it was the easiest solution I found (outside of COM exposed DLL solution below for non-gui applications. .NET 3.5+ required

Possible solutions included:

  • Run the GUI from the VBA – No, I want a standalone GUI that can be attached to
  • Use AutoItX3.dll to literally click the buttons and type using virtual keyboard and mouse – No, doable but overly difficult and complicated
  • Use .NET’s UI automation to control UI elements found through Windows SDK inspect tool – Pretty good, but since we have the GUI source code, there should be a more direct solution\
  • Use UDP port on local machine to link programs
  • Communicate through set memory location on computer – too low level. Something should already exist that does this safely without problems
  • Use WCF (Windows Communication Foundation) Named Pipes to communicate between VBA/C# and WPF (Windows Presentation Foundation) – Looks the cleanest and easiest way

Step-By-Step Guided Example

  • Step-By-Step Guided Example
  • 1. Create C# Library Class
  • 2. Make Library Class Visible as COM Library
  • 3. Call Public Functions from Excel VBA
  • 4. Deployment
    • Unblock the DLL (Windows Security)
    • Registering the Assembly
    •  Related articles
  • References

1. Create C# Library Class

Put this code in a new C# file in a new Visual Studio project. It is bare bones with one simple function that will later be called from Excel vba.

I named the project and solution both ZZTEST_EXCEL_LIB for easy finding in the excel references list.

Sample Code Collapse source
1
2
3
4
5
6
7
8
9
10
11
12
13
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
namespaceZZVISIBLE_FROM_EXCEL_LIBRARY
{
    publicclassZZVISIBLE_FROM_EXCEL_CLASS
    {
        publicDouble DotNetMethod_SQR(Double input) //Return the square of the input
        {
            returninput * input;
        }
    }
}

2. Make Library Class Visible as COM Library

In the project properties, change the output type to Class Library

In Assembly Information, check “make assembly COM-Visible”

In the project properties under the build tab, check “Register for COM interop”

3. Call Public Functions from Excel VBA

In Excel, enable the developer tab and launch visual basic (google this step if unclear)

From visual basic, select tools and then references. Select the desired library, in this case “ZZTEST_EXCEL_LIB”

Run the VBA code
Collapse source
1
2
3
4
SubTest()
    Dimtest1 AsNewZZVISIBLE_FROM_EXCEL_CLASS
    MsgBox test1.DotNetMethod_SQR(5) 'The function made in C# should return the square the input, 5*5 = 25
EndSub

Voila

 

4. Deployment

This is great, but it will only work on your computer. You must follow these steps to register the DLL on another computer. TK I am looking into building an installer to do this

Everything in the C:\Program Files (x86)\Maxim\GUI_BRIDGE path must be placed in this exact path on the target computer.

Unblock the DLL (Windows Security)

Registering the Assembly

Start the command prompt as administrator

In the terminal window, run regasm with /codebase option on the dll.

“C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe” /codebase “C:\Program Files (x86)\Maxim\GUI_BRIDGE2\gui_bridge2_project\gui_bridge2_project\bin\Debug\ZZ_GUI_BRIDGE2_PROJECT.dll”

Regasm will warn about RA0000 ~ please give your assembly a strong name and re-register it. Types registered successfully.

References

http://msdn.microsoft.com/en-us/library/bb608604.aspx

http://www.codeproject.com/Articles/555660/Extend-your-VBA-code-with-Csharp-VB-NET-or-Cpluspl

http://msdn.microsoft.com/en-us/library/tzat5yw6(v=vs.110).aspx

http://www.geeksengine.com/article/create-dll.html

http://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/

http://msdn.microsoft.com/en-us/library/bb608613.aspx – debugging deployment issue

http://tech.pro/tutorial/855/wcf-tutorial-basic-interprocess-communication