Interfacing VB.Net and VBA with Excel-DNA (no intellisense support)

[Paul Wolff]

I'm trying to interface vb.net objects with Excel VBA with Excel DNA.  I based it on converting the example provided by Michael Katajamaki (http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html) from c# to vb.net.

I can't quite make it work, the ExcelInterface2.xll file loads in Excel, but when running the vba code I get an error "ActiveX component can't create object."  I'm using VS2010 to create the dll.

The complete code is provided below.  I really appreciate any help.

Paul

Here's the vb.net code

'************************************************************

Imports System

Imports ExcelDna.Integration

Imports ExcelDna.ComInterop

Imports System.Runtime.InteropServices

<ComVisible(True)>

    <ClassInterface(ClassInterfaceType.AutoDispatch)>

    <ProgId("HelloWorld")>

Public Class InterfaceFunctions

    Public Sub New()

        ComServer.DllRegisterServer()

    End Sub

    Public Function add(ByVal x As Double, ByVal y As Double) As Double

        Return x + y

    End Function

    Protected Overrides Sub Finalize()

        MyBase.Finalize()

        ComServer.DllUnregisterServer()

    End Sub

End Class

'**************************************************************

ExcelInterface2.dna file

'******************************************************************

<DnaLibrary Language="VB" RuntimeVersion="v4.0">

  <ExternalLibrary Path="ExcelInterface2.dll" />

</DnaLibrary>

'******************************************************************

VBA code

'**************************************************************************

Option Explicit

'

Sub tester()

    '

    Dim lib As Object: Set lib = CreateObject("HelloWorld")

    Debug.Print lib.Add(12, 13)

    Set lib = Nothing

End Sub

'*******************************************************************

'Here's a listing of files in the directory with the xll file

ExcelInterface2.dll

ExcelInterface2.dna

ExcelInterface2.xll

[Govert van Drimmelen]

Hi Paul,

You're nearly there.

1. As Mike shows, in the .dna file, you need to specify that the library is to be used as a ComServer:

<DnaLibrary Language="VB" RuntimeVersion="v4.0">

  <ExternalLibrary Path="ExcelInterface2.dll" ComServer="true" />

</DnaLibrary>

2. Your class to be exposed via COM (only instantiated when the VBA code runs) should be separate from the class implementing IExcelAddIn (created by Excel-DNA when the add-in is loaded) which does the COM Registration. Your VBA file might look like this:

Imports System

Imports ExcelDna.Integration

Imports ExcelDna.ComInterop

Imports System.Runtime.InteropServices

<ComVisible(True)>

<ClassInterface(ClassInterfaceType.AutoDispatch)>

<ProgId("HelloWorld")>

Public Class InterfaceFunctions

    Public Function add(ByVal x As Double, ByVal y As Double) As Double

        Return x + y

    End Function

End Class

Public Class AddIn

    Implements IExcelAddIn

    Public Sub AutoOpen() Implements IExcelAddIn.AutoOpen

        ComServer.DllRegisterServer()

    End Sub

    Public Sub AutoClose() Implements IExcelAddIn.AutoClose

        ComServer.DllUnregisterServer()

    End Sub

End Class

Let us know if that works.

Regards,

Govert

[Paul Wolff]

Thank you Govert,

I really appreciate the blazing fast reply.

However, it still doesn't work.  I modifed the dna file as shown below.  I recompiled then double clicked on the xll file to open excel, it opened without problem.  But still got the activeX component can't create object error.

Paul

<DnaLibrary Language="VB" RuntimeVersion="v4.0">

  <ExternalLibrary Path="ExcelInterface2.dll" ComServer="true" />

</DnaLibrary>

[Paul Wolff]

Oops, hold on, it works.  Thanks.

I was a bit hasty with the reply and didn't implement all the changes that you specified.

Thank you,

Paul

On Friday, July 25, 2014 5:35:34 PM UTC-4, Paul Wolff wrote: