PDA

View Full Version : How to call\use DLL via vba macro?



sagotianitin
02-13-2013, 09:25 AM
Hello Everyone,

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

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

-- Code present in DLL--

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

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

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

Sub MyTest()
Call TestDLL.FirstDLL
End Sub


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

Thanks,
Nitin

Aflatoon
02-13-2013, 09:42 AM
I think your declare line should be more like
Declare Sub nitin _
Lib "C:\Users\nitin.d.jain\Desktop\TestDLL.dll" _
Alias "FirstDLL" ()

then you call it with
Sub MyTest()
Call nitin
End Sub

Kenneth Hobs
02-13-2013, 10:12 AM
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:
' 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

sagotianitin
02-13-2013, 11:02 AM
Hi Kenneth,

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

Kenneth Hobs
02-13-2013, 11:29 AM
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
02-14-2013, 02:29 PM
If you want to learn more about creating this sort of DLL in vb.net, I posted instructions at:
http://www.wpuniverse.com/vb/showthread.php?33828-VB.NET-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/creatingnetfunctionlib.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 (http://www.cpearson.com/excel/creatingnetfunctionlib.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:
Unzip MathLib.
Copy or move the MathLib.dll from the subfolder Release to your c:\Windows\System32.
Copy or move the two BAT files to your c:\windows\System32.
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 (http://www.wpuniverse.com/vb/#) Visual Studio 2010 Express edition installed.
Drag and drop the MathLib.dll onto registerdll.bat.
Play MathLib.wcmIf 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:


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

Multiplication.vb:
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

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
03-26-2013, 11:37 AM
Thanks Kenneth
:beerchug:

Kenneth Hobs
10-29-2016, 06:47 AM
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