How to call a function of an user defined DLL from Excel?

[Davide]

Hi,
I want to call a DLL defined by me ( a simple one that just return a
string) from the vba of excel but the point is that it doesn't work.
I don't know what I wrong... I did an example to call a system DLL
(kernel32.dll) and it works (see the following code, I'm using
VisualStudio 2005 and Excel 2002 sp1)

Private Declare Function GetTempPathA Lib "kernel32" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Public Sub showcollection(colCollection As Collection)
Dim s As String
Dim i As Integer
i = GetTempPathA(0, "")
s = " "
Call GetTempPathA(i, s)
MsgBox (" Temp-Path:" + s)
End Sub


in my example I worte the C# DLL code:

public class Class1
{

public string messaggioEsempio(string mess)
{

return mess + " messaggio ricevuto ";
}
}

and in the properties project I checked to register for COM interop,
in excel I defined a module where the code is:

Private Declare Function messaggioEsempio Lib "DllEsempioCS.dll" _
(ByRef valore As String) As String


Public Sub showcollection(colCollection As Collection)

Dim val, val1 As String
val1 = "ciao Dav"

' below there is comment code I tryed... but nothing is working...
' ' Dim myObject3 As DllEsempioCS.Class1
' ' Set myObject3 = New DllEsempioCS.Class1

Call messaggioEsempio(val1)

' val = messaggioEsempio(val1)

' MsgBox (messaggio(val1))

' Call messaggioEsempio (val1)
' Dim objFunction As Object
' If objFunction Is Nothing Then
' Set objFunction = CreateObject("DllEsempioCS.messaggioEsempio")
' End If

End Sub

from excel I added the reference to the DllEsempioCS.tlb (as the DLL
can't be loaded)

can someone tell me what is wrong or write me a working example of user
defined DLL (in C# or VB) that is called from excel?
Thanks a lot
Davide

 

[Paul]

Hi, I am doing something similar right now. here is what I know. Try using
the following link below.
http://www.windowsdevcenter.com/pub/a/windows/2005/04/26/create_dll.html
This next example I got off the web. I don't have the addess but we can
discuss this offline. if want further help.


Title Make a standard DLL
Description This example shows how to make a standard DLL in Visual Basic 6.
Keywords DLL, ActiveX DLL
Categories ActiveX, Windows
This examples builds a standard DLL that you can call by using the normal
DLL calling conventions. For full details, see the article Creating a Windows
DLL with Visual Basic.
Thanks to Luke Emmet for pointing this article out.

The basic staps are:


Hack the linking process.
Make an executable program to call the linker. Reomve the default Form1 and
create the following Sub Main.



Public Sub Main()
Dim SpecialLink As Boolean, fCPL As Boolean, fResource _
As Boolean
Dim intPos As Integer
Dim strCmd As String
Dim strPath As String
Dim strFileContents As String
Dim strDefFile As String, strResFile As String
Dim oFS As New Scripting.FileSystemObject
Dim fld As Folder
Dim fil As File
Dim ts As TextStream, tsDef As TextStream

strCmd = Command

Set ts = oFS.CreateTextFile(App.Path & "\lnklog.txt")

ts.WriteLine "Beginning execution at " & Date & " " & _
Time()
ts.WriteBlankLines 1
ts.WriteLine "Command line arguments to LINK call:"
ts.WriteBlankLines 1
ts.WriteLine " " & strCmd
ts.WriteBlankLines 2

' Determine if .DEF file exists
'
' Extract path from first .obj argument
intPos = InStr(1, strCmd, ".OBJ", vbTextCompare)
strPath = Mid(strCmd, 2, intPos + 2)
intPos = InStrRev(strPath, "\")
strPath = Left(strPath, intPos - 1)
' Open folder
Set fld = oFS.GetFolder(strPath)

' Get files in folder
For Each fil In fld.Files
If UCase(oFS.GetExtensionName(fil)) = "DEF" Then
strDefFile = fil
SpecialLink = True
End If
If UCase(oFS.GetExtensionName(fil)) = "RES" Then
strResFile = fil
fResource = True
End If
If SpecialLink And fResource Then Exit For
Next

' Change command line arguments if flag set
If SpecialLink Then
' Determine contents of .DEF file
Set tsDef = oFS.OpenTextFile(strDefFile)
strFileContents = tsDef.ReadAll
If InStr(1, strFileContents, "CplApplet", _
vbTextCompare) > 0 Then
fCPL = True
End If

' Add module definition before /DLL switch
intPos = InStr(1, strCmd, "/DLL", vbTextCompare)
If intPos > 0 Then
strCmd = Left(strCmd, intPos - 1) & _
" /DEF:" & Chr(34) & strDefFile & Chr(34) & _
" " & _
Mid(strCmd, intPos)
End If
' Include .RES file if one exists
If fResource Then
intPos = InStr(1, strCmd, "/ENTRY", vbTextCompare)
strCmd = Left(strCmd, intPos - 1) & Chr(34) & _
strResFile & _
Chr(34) & " " & Mid(strCmd, intPos)
End If

' If Control Panel applet, change "DLL" extension to
' "CPL"
If fCPL Then
strCmd = Replace(strCmd, ".dll", ".cpl", 1, , _
vbTextCompare)
End If

' Write linker options to output file
ts.WriteLine "Command line arguments after " & _
"modification:"
ts.WriteBlankLines 1
ts.WriteLine " " & strCmd
ts.WriteBlankLines 2
End If

ts.WriteLine "Calling LINK.EXE linker"
Shell "linklnk.exe " & strCmd
If Err.Number <> 0 Then
ts.WriteLine "Error in calling linker..."
Err.Clear
End If

ts.WriteBlankLines 1
ts.WriteLine "Returned from linker call"
ts.Close
End Sub



This program does roughly the same thing that Visual Basic does when it
creates a DLL except it adds the /DEF flag to the command.
Compile the executable.
Rename the normal Visual Basic linker from Link.exe to LinkLnk.exe. On my
system, it's at C:\Program Files\Microsoft Visual Studio\VB98.
Copy the executable program that you compiled into this directory and name
it Link.exe. When Visual Basic links the DLL, it calls this program, which
calls the renamed LinkLnk.exe program, adding the new /DEF parameter.
Export the DLL's routines.
Create a file named .def where is the name of the DLL. In this example, the
DLL is named Fibonacci.dll so this file is called Fibonacci.def.
Add code to this file similar to the following:



NAME MathLib
LIBRARY MathMod
DESCRIPTION "Add-on Library of Mathematical Routines"
EXPORTS DllMain @1
Fibo @2



This tells the linker about the main entry point DllMain and this example's
function Fibo, both of which are created shortly.
Build the DLL.
Create a new ActiveX DLL project.
Leave the default Class1 class alone. You will not use it but Visual Basic
needs it to it has something to compile into the ActiveX DLL.
Add a code module and insert this code:



Public Const DLL_PROCESS_DETACH = 0
Public Const DLL_PROCESS_ATTACH = 1
Public Const DLL_THREAD_ATTACH = 2
Public Const DLL_THREAD_DETACH = 3

Public Function DllMain(hInst As Long, fdwReason As Long, _
lpvReserved As Long) As Boolean
Select Case fdwReason
Case DLL_PROCESS_DETACH
' No per-process cleanup needed
Case DLL_PROCESS_ATTACH
DllMain = True
Case DLL_THREAD_ATTACH
' No per-thread initialization needed
Case DLL_THREAD_DETACH
' No per-thread cleanup needed
End Select
End Function

' Return a Fibonacci number.
Public Function Fibo(ByVal N As Integer) As Long
If N <= 1 Then
Fibo = 1
Else
Fibo = Fibo(N - 1) + Fibo(N - 2)
End If
End Function



DllMain is the DLL entry point. Fibo is a function that the DLL is exporting.
Compile the DLL. This should invoke the new Link.exe you built. If you look
in that program's directory, you should see the log file it generates.
Build a test program to call the DLL.
Make a standard Visual Basic EXE.
Declare the routine exported by the DLL as in the following code:



Private Declare Function Fibo Lib _
"C:\WebSite\HowToSrc\a2\Fibonacci.dll" (ByVal N As _
Integer) As Long



Insert the path to the DLL on your computer.
Run the program.
That should do it. Watch out for typos. If the .DEF file doesn't spell the
function's name correctly, the DLL won't compile and the error messages are
not very good.

See the article mentioned at the beginning for more detail and some
information about how the original author figured all this out.

 

[kounoike]

I'm not the original questioner, but i'm also interested in this subject
and had tried these referenced here a few weeks ago. What i did was like
this.

My code In VB6 is like this and make mylibtest.dll in C:\

Public Const DLL_PROCESS_DETACH = 0
Public Const DLL_PROCESS_ATTACH = 1
Public Const DLL_THREAD_ATTACH = 2
Public Const DLL_THREAD_DETACH = 3

Public Function DllMain(hInst As Long, fdwReason As Long, lpvReserved As
Long) As Boolean
Select Case fdwReason
Case DLL_PROCESS_DETACH
' No per-process cleanup needed
Case DLL_PROCESS_ATTACH
DllMain = True
Case DLL_THREAD_ATTACH
' No per-thread initialization needed
Case DLL_THREAD_DETACH
' No per-thread cleanup needed
End Select
End Function

Public Function Increment(ByVal var As Integer) As Integer
If Not IsNumeric(var) Then Err.Raise 5
Increment = var + 1
End Function

Public Function Decrement(ByVal var As Integer) As Integer
If Not IsNumeric(var) Then Err.Raise 5
Decrement = var - 1
End Function

Public Function Square(ByVal var As Long) As Long
If Not IsNumeric(var) Then Err.Raise 5
Square = var ^ 2
End Function

Public Function mytrim2(ByVal str As String) As String
Do While InStr(1, str, " ", vbBinaryCompare) > 0
str = Replace(str, " ", " ", 1, -1, vbBinaryCompare)
Loop
mytrim2 = str
End Function

Public Function myrepeat(ByVal str As String, ByVal n As Long) As String
Dim tmp As String
Do While n > 0
tmp = tmp & str
n = n - 1
Loop
myrepeat = tmp
End Function

and decleare these in VB6 and in VBA

Public Declare Function Increment Lib "C:\mylibtest.dll" ( _
ByVal value As Integer) As Integer

Public Declare Function Decrement Lib "C:\mylibtest.dll" ( _
ByVal value As Integer) As Integer

Public Declare Function Square Lib "C:\mylibtest.dll" ( _
ByVal value As Long) As Long

Public Declare Function mytrim2 Lib "C:\mylibtest.dll" ( _
ByVal str As String) As String

Public Declare Function myrepeat Lib "C:\mylibtest.dll" ( _
ByVal str As String, ByVal n As Long) As String

these functions work without any troubles in VB6. so, i tried these
functions in VBA. but only Increment, Decrement, Square work fine and
when i tried myrepeat or mytrim2, Excel always crashes. I can't
understand why these work well in VB but not in VBA. i apreciate any
advice or workaround to work myrepeat and mytrim2 in VBA.

Thanks in advance.

keizi

 

[Davide]

Thanks a lot,
I'll try the solution you wrote.
As I don't know how to do a DLL in C#, I'm going to exchange data
between C# and VB iin some way