Calling VB.Net Dll from Excel VBA

zyphax · December 13th, 2005, 03:27 PM

I've got a problema calling a VB.Net DLL from VBA.

The teste.vb used in the DLL is:

Public Class Connect

    Public Shared Function GetClientes(ByVal kunnr As String) As DataTable

        Dim SAPProxy As SAPProxy1 = New SAPProxy1
        Dim s As String

        Dim cs As String = "ASHOST=192.168.1.111 SYSNR=0 CLIENT=500 USER=INICIAL_CRM5 PASSWD=leiria"
        SAPProxy.CustName = kunnr
        SAPProxy.CustNo = ""
        SAPProxy.Connection = New SAP.Connector.SAPConnection(cs)
        SAPProxy.Rfc_Customer_Get()
        Dim saida As DataSet = New DataSet
        Dim tabela As DataTable = New DataTable("Clientes")
        tabela.Columns.Add("ncli", Type.GetType("System.String"))
        tabela.Columns.Add("nomecli", Type.GetType("System.String"))

        tabela.Rows.Add(New Object() {SAPProxy.tblCust.Item(0).Kunnr, SAPProxy.tblCust.Item(0).Name1})
        Return tabela

    End Function

End Class

And the VBA Code is:

Private Declare Function GetClientes Lib "Teste.dll" (ByVal kunnr As String) As DataTable

Private Sub Actualiza_PI_Click()

Dim kunnr As String
Dim dados As DataTable
Dim s As String
Dim lngRet As Long

dados = GetClientes(kunnr)

End Sub

I'm getting a 453 error, function not found in DLL. I've registered the Dll using Regasm.exe but it is still not working.

BrianWren · December 13th, 2005, 05:16 PM

I'm not sure, but I think the thing to do is to provide a reference to the .dll in the projects references rather than trying to set up a Declare statement.

Intelisense will not work on the elements of the dll (which can be unnerving), but it should function nonetheless.
Additionally, you might need to fully qualify the functions. Let's say the dll projects name is prjTeste. You would use prjTeste.GetClientes(kunnr) (for example).

Grimmest · January 5th, 2006, 03:00 PM

Hey zyphax, I know this is about two weeks later, but I was looking around some sites for some code and happened upon this one. If the vb.net dll/class is going to be exposed through COM then here is how I do it. The way I've been doing it recently works great, but I am not sure if it is all needed.

first import InteropServices

Code:

Imports System.Runtime.InteropServices

second make an Interface.
If you don't know what it is or does...
It is where you define the headers for all functions, subs, and properties that will be exposable to COM.

Code:

   <Guid("[Generate a GUID in VS.net and put it here]"), _
   InterfaceType(ComInterfaceType.InterfaceIsIDispatch)> _
   Public Interface _main
      'this is where you declare functionc, subs, and properties
      'something like this
      <DispId(1)> Function GetVolume() As String
      <DispId(2)> Sub SetVolume(ByVal newvalue As String)
   End Interface

the format for the GUID should be

Code:

Guid("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX")

third you create a class that Implements the Interface.
when you Implement an Interface the class handles all the functions, subs and properties in the Interface. Basically it is where you perform operations on the data from the Interface and send data back through the Interface.
This is also ehre you can declear the object name that will be registered to COM so you can call the dll/class.
The ProgId is the object name.
In this example the class Sound is one I created.

Code:

    <Guid("[Generate another GUID in VS.net and put it here]"), _
    ClassInterface(ClassInterfaceType.None), _
    ProgId("[Obj Nmn]")> _
    Public Class main
        Implements _main

        Private snd As New Sound

        Public Function GetVolume() As String Implements _main.GetVolume
            Return snd.GetVolume
        End Function

        Public Sub SetVolume(ByVal newvalue As String) Implements _main.SetVolume
            snd.SetVolume(CInt(newvalue))
        End Sub

    End Class

Next wrap the the Implement and Class in a Namespace.
This is simple just put

Code:

Namespace [Replace with name]

before the Implement and

Code:

End Namespace

after the class.

Last The important one. register the Dll for COM InterOp
I played around for a really, i mean really long time before I found this way of doing it.

It is simple.

Open the Solution Explorer for the current Project.
select the Project Node. then Right Click it. Choose Properties.

when the dialog opens there will be a TreeView with Common Properties and Configuration Properties as the main nodes.

Choose Configuration Properties then the Child Node, Build.
In Build there will be a checkbox that has the text Register for COM Interop. Check it.

Now you can compile the dll/class and it will register it for you.

after that you should be able to call the dll/class from and COM client.

jgoodale · February 2nd, 2006, 09:46 AM

Hi Grimmer,

I am really struggling with this fix for some reason. I have created my class and an accompanying interface exactly as you did, and I'm trying to call the class's method (there is only one at this point for testing purposes) from an Access vba module and although my code recognizes the class it simply does not recognize the method. I have successfully registered my class for COM interop as well. Any ideas? Is there some sort of special setting in Access? Any help would be appreciated. You can talk shop with me I've been a programmer for 10 years. I've poured over this code time and time again and just don't see my mistake. Here is my pertinent vb.net code and my vba code (I generated unique GUIDs):

VB.NET
Imports System.Runtime.InteropServices

<Guid("48D8F350-2C5D-4829-A6D4-9615FD5F4C45"), _
    InterfaceType(ComInterfaceType.InterfaceIsIDispatc h)> _
    Public Interface iUpdatePYEmployee

    <DispId(1)> Function CallStoredProc()

End Interface

<Guid("8FC2418A-210A-4480-AC41-D24D7D6C04D9"), _
ClassInterface(ClassInterfaceType.None), _
ProgId("testProg.CallProc")> _
    Public Class UpdatePYEmployee
    Implements iUpdatePYEmployee

............ my own stuff............

Public Function CallStoredProc() Implements iUpdatePYEmployee.CallStoredProc

........my function guts............

End Function

End Class

VBA This is very basic right now
Option Compare Database

Private Sub cmdbtnClose_Click()

'Declare the COM object
Dim objUpdatePYEmp As New Cianbro_Common.UpdatePYEmployee
Call objUpdatePYEmp.CallStoredProc

MsgBox ("Hello World")

Exit Sub

On Error GoTo Err_cmdbtnClose_Click

    DoCmd.Close

Exit_cmdbtnClose_Click:
    Exit Sub

Err_cmdbtnClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdbtnClose_Click

End Sub

It recognizes CallStoredProc but it's not the function in my .NET class, it's another function that is a mamber of the VBA module that keeps reappearing under a duplicate UpdatePYEmployee class in the VBA as well, and it is an empty shell of a function. The interesting thing is that in Access the "TYPES"(?) defined in the vb.NET dll are recognized but none of the methods are (in my class or any other class in the dll).

Thanks!

rocktremblay · July 16th, 2007, 08:41 PM

Hi all

Here is the solution I'd use.
* Shared methods are not exposed.

<ComClass()> _
Public Class MyClass

Public sub MySub()

End Sub

End Class