Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > Pro VB.NET 2002/2003
Pro VB.NET 2002/2003 For advanced Visual Basic coders working .NET version 2002/2003. Beginning-level questions will be redirected to other forums, including Beginning VB.NET.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB.NET 2002/2003 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 13th, 2005, 03:27 PM
Registered User
Join Date: Dec 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calling VB.Net Dll from Excel VBA

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= SYSNR=0 CLIENT=500 USER=INICIAL_CRM5 PASSWD=leiria"
        SAPProxy.CustName = kunnr
        SAPProxy.CustNo = ""
        SAPProxy.Connection = New SAP.Connector.SAPConnection(cs)
        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.

Old December 13th, 2005, 05:16 PM
Friend of Wrox
Join Date: Nov 2004
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts

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).
Old January 5th, 2006, 03:00 PM
Registered User
Join Date: Jan 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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
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.
   <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
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.
    <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
        End Sub

    End Class
Next wrap the the Implement and Class in a Namespace.
This is simple just put
Namespace [Replace with name]
before the Implement and
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.
Old February 2nd, 2006, 09:46 AM
Registered User
Join Date: Feb 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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):

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


    Exit Sub

    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).


Old July 16th, 2007, 08:41 PM
Registered User
Join Date: Jul 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Calling a VB program or DLL from Access JOK Access VBA 1 October 12th, 2007 12:59 AM
Problem while calling a VB COM DLL in ASP ranee Classic ASP Components 0 December 1st, 2005 09:44 AM
Converting VBA Excel to VB.Net. Help help. dinosaur_uk VB.NET 2002/2003 Basics 4 October 5th, 2004 04:58 PM
Calling a .Dll of Vb.NET enggalok C++ Programming 0 March 1st, 2004 06:51 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.