How to call\use DLL via vba macro?

[sagotianitin]

Hello Everyone,

Please can anybody tell how to use DLL in excel (VBA).

I have create a DLL file which has one function as below:

-- Code present in DLL--

[VBA]Public Class Test
Sub FirstDLL()
MsgBox("Hi, I am called from testdll")
End Sub
End Class[/VBA]

Now, I am trying to call the function (FirstDLL) which is there in the DLL via VBA (Macro).

Below is the code I am using to call the function from DLL:

-- Code in VBA--

[VBA]Declare Function FirstDLL _
Lib "C:\Users\Desktop\TestDLL.dll" _
Alias "TestDLL" ()

Sub MyTest()
Call TestDLL.FirstDLL
End Sub [/VBA]


But unfortunately its not working! I am getting "Compile Error".
Kindly let me know where I am wrong!

Thanks,
Nitin

[Aflatoon]

I think your declare line should be more like
[vba]Declare Sub nitin _
Lib "C:\Users\nitin.d.jain\Desktop\TestDLL.dll" _
Alias "FirstDLL" ()[/vba]

then you call it with
[vba]Sub MyTest()
Call nitin
End Sub[/vba]

[Kenneth Hobs]

Depends on the DLL too I would think. I made a short example in vb.net with examples.

https://www.box.com/s/65ekeevb6ol0qg7pld4n

Since you don't have WordPerfect most likely to see how I used it in VBA, here is how the DLL is used in VBA:
[VBA] ' Set Reference by Tools > References > Math Library by Kenneth Hobson > OK
Sub Test_MathLib()
Dim mlm As MathLib.Multiply, mld As MathLib.Division
Set mlm = New MathLib.Multiply
Set mld = New MathLib.Division

MsgBox mlm.MultiplyBy2(7), vbInformation, "7*2=?"
MsgBox mld.DivideBy2(14), vbInformation, "14/2=?"

set mlm = Nothing
set mld = Nothing
End Sub[/VBA]

[sagotianitin]

Hi Kenneth,

The link which you provided is not working! Please send the correct link, thanks.

[Kenneth Hobs]

Works for me.

I get one or two people downloading it every week so it works for them. I guess you could log in to box.net and look for mathlib.zip.

[Kenneth Hobs]

If you want to learn more about creating this sort of DLL in vb.net, I posted instructions at:
http://www.wpuniverse.com/vb/showthr...112-Create-DLL

Once you learn it, it is fairly handy. I think it worth registering there to see a few more notes about how I did it. You can probably gleam most of it from Chip's site: http://www.cpearson.com/excel/creati...nctionlib.aspx

IF you don't want to register at wpu, here is most of what I said there.

VB.NET - 112, Create DLL

A DLL file can make it easier to reuse it's procedures and functions in PerfectScipt, vb.net, VBS, VBA, C#, and several other languages.

Unlike other lessons, this one will be a bit shorter. Due to file size, I uploaded the example files to box.net: http://www.box.com/s/65ekeevb6ol0qg7pld4n

This lesson is mostly based on Chip Pearson's code. While his example was for Excel, most of those concepts and code snippets are what I used. The link is: http://www.cpearson.com/excel/creati...nctionlib.aspx

Another helpful link that details how to create a DLL in c# mostly and some vb.net with similar concepts is at: http://www.geeksengine.com/article/create-dll.html

In the zip file at box.net:
registerdll.bat
unregisterdll.bat
MathLib.wcm
MathLib (a folder with the vb.net files).

Steps:

1. Unzip MathLib.
2. Copy or move the MathLib.dll from the subfolder Release to your c:\Windows\System32.
3. Copy or move the two BAT files to your c:\windows\System32.
4. Right click each of the BAT files and modify the path to RegAsm.exe if needed. This is a vb.net file. Obviously, you need the Microsoft Visual Studio 2010 Express edition installed.
5. Drag and drop the MathLib.dll onto registerdll.bat.
6. Play MathLib.wcm

If you create the DLL from scratch, be sure to make the file COM visible as detailed in the two referenced links.

I used wp12 but you should not need it unless I missed deleting the reference. I did this in Microsoft Visual Studio 2010 Express. Here are the two Classes:
Division.vb:


[vba]Option Compare Text
Option Strict Off
'Imports WP = WordPerfect.PerfectScript
Imports System
Imports System.Runtime.InteropServices
Imports WIN32 = Microsoft.Win32


<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class Division

Public Function DivideBy2(ByVal D As Double) As Double
Return D / 2
End Function

Public Function DivideBy4(ByVal D As Double) As Double
Return D / 4
End Function


<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
WIN32.Registry.ClassesRoot.CreateSubKey(GetSubkeyName(Type))
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
WIN32.Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(type), False)
End Sub

Private Shared Function GetSubkeyName(ByVal type As Type) As String
Dim S As New System.Text.StringBuilder()
S.Append("CLSID\{")
S.Append(type.GUID.ToString().ToUpper())
S.Append("}\Programmable")
Return S.ToString()
End Function

End Class[/vba]

Multiplication.vb:
[vba]Option Compare Text
Option Strict Off
'Imports WP = WordPerfect.PerfectScript
Imports System
Imports System.Runtime.InteropServices
Imports WIN32 = Microsoft.Win32


<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class Multiply

Public Function MultiplyBy2(ByVal D As Double) As Double
Return D * 2
End Function

Public Function MultiplyBy4(ByVal D As Double) As Double
Return D * 4
End Function


<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
WIN32.Registry.ClassesRoot.CreateSubKey(GetSubkeyName(type))
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
WIN32.Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(type), False)
End Sub

Private Shared Function GetSubkeyName(ByVal type As Type) As String
Dim S As New System.Text.StringBuilder()
S.Append("CLSID\{")
S.Append(type.GUID.ToString().ToUpper())
S.Append("}\Programmable")
Return S.ToString()
End Function

End Class[/vba]

If you need to enter a password for MathLibKey.pfx, it is: mathlib. This is set in the Signing tab of the Solution object My Project. You can delete MathLibKey.pfx and recreate it if needed.

[sagotianitin]

Thanks Kenneth

[Kenneth Hobs]

I refer to this every so often so for those that want to see how to use my simple com visible DLL, see the readme.txt file in the zip file or for quick reference:

   ' Set Reference by Tools > References > Math Library by Kenneth Hobson > OK     Sub Test_MathLib()
       Dim mlm As MathLib.Multiply, mld As MathLib.Division
       Set mlm = New MathLib.Multiply
       Set mld = New MathLib.Division
       MsgBox mlm.MultiplyBy2(7), vbInformation, "7*2=?"
       MsgBox mld.DivideBy2(14), vbInformation, "14/2=?"
     set mlm = Nothing
     set mld = Nothing
     End Sub