CODEWITHSUNDEEP
c#.netexcelvbatypelib
Extermiknit
Extermiknit

Reputation: 153

Excel 2010 VSE 2012 C# dll and tlb - Can't create reference to specified file

I'm interested in finding how to call C# code from Excel, and have been following the method here https://richnewman.wordpress.com/2007/04/15/a-beginner%E2%80%99s-guide-to-calling-a-net-library-from-excel/.

The C# code is a "Hello World" example, called DotNetClass.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace DotNetLibrary
{
    public class DotNetClass
    {
        public string DotNetMethod(string input)
        {
            return "Hello " + input;
        }
    }
}

The build configuration has "Register for COM interop" and "Make assembly COM-visible" checked. I also have the options ".NET Framework 4.5" and "Any CPU" (I'm working on a 64bit machine). I have tried a few NET versions and CPU options without success. I have cleaned and rebuilt it several times.

In Excel 2010 I cannot create a reference to DotNetLibrary.dll. I can browse to it, and I can even create a reference to a file in the same place - DotNetLibrary.tlb.

The call from Excel is

Private Sub TestDotNetCall()
Dim testClass As New DotNetClass
MsgBox testClass.DotNetMethod(“World”)
End Sub

... and this works - almost. If I run the VBA code (F5), I get a MsgBox (woot!) with the message "Hello " (no "World" - unwoot!). So, it seems to be ignoring the argument ("World"), which appears to defeat the purpose of doing this.

I'd appreciate any explanations. Is it a good thing to reference the tlb file? Why can't I seem to pass the argument to, or get the result from the dll/tlb? Is there a better way to do this?

Upvotes: 2

Views: 1277

Answers (1)

Extermiknit
Extermiknit

Reputation: 153

I got it to work. The problem arises because C# is strongly typed, and VBA is loosely typed.

In the VBA line

MsgBox testClass.DotNetMethod(“World”)

A "thing" (technically a var, I believe) with the value "World" is passed to DotNetMethod. The problem is that DotNetMethod doesn't know what type of "thing" "World" is, so it ignores it.

The solution is to explictly define the type of "World" in VBA, as follows

Private Sub TestDotNetCall()
Dim testClass As New DotNetClass
Dim s As String
s = "World"
MsgBox (testClass.DotNetMethod(s))
End Sub

...and, so, "Hello World"

Upvotes: 1

Related Questions