PDA

View Full Version : Solved: How to create a DLL via VBA editor?



Erdin? E. Ka
12-14-2006, 04:16 PM
Hi everyone:hi:

I want to learn create a DLL via VBA editor. For example i have a FileCount function. How i can do it a DLL via VBA and how i can determine this DLL for Excel?


Function FileCount(PathName As String) As Long
Dim FSO As New FileSystemObject
Dim Fldr As Folder
If FSO.FolderExists(PathName) Then
Set Fldr = FSO.GetFolder(PathName)
FileCount = Fldr.Files.Count
End If
End Function

Bob Phillips
12-14-2006, 04:26 PM
You need a developer edition of VBA to create COM VBA add-ins.

Erdin? E. Ka
12-14-2006, 04:33 PM
You need a developer edition of VBA to create COM VBA add-ins.

:hi: Hi xld,

How can i find a developer edition or is there a way to upgrade my current edition?

Bob Phillips
12-14-2006, 05:55 PM
:hi: Hi xld,

How can i find a developer edition or is there a way to upgrade my current edition?

You should understand that the COM VBA addins are very basic, there is not much you can do. If you are still interested in looking at it, I would just search eBay for say an Excel 2000 Developer edition. Usual caveats about being careful about licenses, and don't pay over the odds.

If you are serious, then invest in a copy of VB6. You should be able to pick that up quite cheaply, after that there is VB.Net, but that is a whole different ball-game. But you can get VB2005 free from MS now.

BTW, what is wrong with a regular add-in?

Zack Barresse
12-14-2006, 06:18 PM
VB6 will probably be the easiest (and best IMHO) best. They are very simple and straight forward. If you're only looking for an Excel add-in (as opposed to an Office add-in) I would mirror Bob's last question.

stanl
12-15-2006, 04:53 AM
If cost is a problem, or the VB2005 Express Edition is too large a download you might want to check out this freeware.

http://people.freenet.de/gborn/WSHBazaar/WSHVB5CCE.htm (http://people.freenet.de/gborn/WSHBazaar/WSHVB5CCE.htm)

The VB5CCein.exe is no longer on the Microsoft site, but you can Google other archives and download a copy. If you are just looking to bundle functions or subs you can paste them into the interface and create an OCX in seconds. The interface registers it so you can test with CreateObject() from VBA code/macros. It is simple and once you get used to it it is easy to move on to more sophisticated interfaces.
.02 Stan

stanl
12-15-2006, 07:17 AM
Oops.. I just realized you wanted to create a dll not an ocx (but often that is 6 of 1).

For fun I modified your Function for compatibility and created an ocx with the vb5ccein, and created an Excel function to call it. You can open the .ctl file in Notepad to see the code, and you must register the ocx prior to using the workbook. I have Studio 6.0 and Express 2005, but find them cumbersome for small tasks - for example converting a .bas class for Credit Card validation into an ocx.

Anyway, I thought I'd zip and post it, since it only took a few minutes, and your posts indicate you want to learn as much as possible.:content: Stan

Erdin? E. Ka
12-15-2006, 08:18 AM
You should understand that the COM VBA addins are very basic, there is not much you can do. If you are still interested in looking at it, I would just search eBay for say an Excel 2000 Developer edition. Usual caveats about being careful about licenses, and don't pay over the odds.

If you are serious, then invest in a copy of VB6. You should be able to pick that up quite cheaply, after that there is VB.Net, but that is a whole different ball-game. But you can get VB2005 free from MS now.

BTW, what is wrong with a regular add-in?

Hi Bob,:hi:

I already have VB6 and MS Visual Studio 2005 Version 8.0.5072742. But i don't use yet. Because i don't know how to i can use. I will try to learn these and develope an easy little project.

And, there is no any wrong with a regular add-in, i just wondered and wanted about that:what is this?

Now i learn about it. Thanks a lot Bob. :friends:

Erdin? E. Ka
12-15-2006, 08:20 AM
VB6 will probably be the easiest (and best IMHO) best. They are very simple and straight forward. If you're only looking for an Excel add-in (as opposed to an Office add-in) I would mirror Bob's last question.

Hi Zack, :hi:

So, for a beginner VB6 is the correct begining. Is my comment true?

Erdin? E. Ka
12-15-2006, 08:24 AM
If cost is a problem, or the VB2005 Express Edition is too large a download you might want to check out this freeware.

http://people.freenet.de/gborn/WSHBazaar/WSHVB5CCE.htm (http://people.freenet.de/gborn/WSHBazaar/WSHVB5CCE.htm)

The VB5CCein.exe is no longer on the Microsoft site, but you can Google other archives and download a copy. If you are just looking to bundle functions or subs you can paste them into the interface and create an OCX in seconds. The interface registers it so you can test with CreateObject() from VBA code/macros. It is simple and once you get used to it it is easy to move on to more sophisticated interfaces.
.02 Stan

Hi Stan, :hi:

Thank you for your kindly support as i said above, i already have VB6 and Visual Studio Professional Edit.

I will take note of your VBA suggestions. :friends:

Thank you very very much.:yes

Zack Barresse
12-15-2006, 08:32 AM
For a great example of how to create a COM add-in with VB6 for Excel, check out Mark Rowlinson's website: http://www.thecodenet.com/articles.php?id=38

Erdin? E. Ka
12-15-2006, 09:00 AM
Oops.. I just realized you wanted to create a dll not an ocx (but often that is 6 of 1).

For fun I modified your Function for compatibility and created an ocx with the vb5ccein, and created an Excel function to call it. You can open the .ctl file in Notepad to see the code, and you must register the ocx prior to using the workbook. I have Studio 6.0 and Express 2005, but find them cumbersome for small tasks - for example converting a .bas class for Credit Card validation into an ocx.

Anyway, I thought I'd zip and post it, since it only took a few minutes, and your posts indicate you want to learn as much as possible.:content: Stan

Hi again,

Stan thanks a lot. I inconvenienced you. I downloaded your sample. I will analysis it. You are so good man. :friends: Thanks again.

Bob Phillips
12-15-2006, 09:09 AM
Erdin?,

Seeing as you already have VB, I will post a small DLL VB6 project that I use in a VB course that I do. It's on another machine so I will get it and upload it later.

Erdin? E. Ka
12-15-2006, 04:06 PM
Erdin?,
Seeing as you already have VB, I will post a small DLL VB6 project that I use in a VB course that I do. It's on another machine so I will get it and upload it later.

Ok Bob, i am waiting your post. But please feel free to posting; as you know it is not an urgent something needed. Thanks a lot.:friends:

Bob Phillips
12-16-2006, 10:52 AM
As promised

Erdin? E. Ka
12-16-2006, 04:16 PM
As promised

Hi Bob, :hi:
I donwloaded your example.
Thank you very much to your wonderful help. :thumb

tstom
12-16-2006, 05:30 PM
Erdin?,
You should note the difference between a COM addin and a simple ActiveX.dll server. IMHO, in many cases, a simple ActiveX.dll is a better solution than a COM addin. On the other hand, a COM addin is just an ActiveX.dll template with an included AddInDesignerObject and a bit of template code. The advantages of using a simple ActiveX.dll server are as such. You have at your disposal the added flexibility of the full Visual Basic language and the enhanced design functionality of the VS design environment. Your code, being packed into a dll instead of the workbook itself is a bit more secure than simply storing your VBA code within your workbook. Done correctly, your code is easier to maintain, ie. updates, error corrections, ect... You will not usually need to touch the code in the workbook but only the ActiveX.dll itself will usually need to be updated. You have compile options using the VB compiler that are not available to you in VBA and your code is precompiled. This can provide a big performance gain in some instances.

The downfalls are the inability for client edits and compatibility problems. On occasion, I have had customers that had no probem troubleshooting code for me. If your code is in a dll, this will not usually be an option. Do a search for "dll hell" for the compatability issue. For the most part, this problem is overblown. It's a favorite rock to throw at MS by some developers. Personally, I have had few problems. Another issue is the need to distribute extra files along with your workbook. Sometimes, though rarely enough, this is an issue and it just makes more sense to use VBA alone. You will also need some code written in your workbook unless you will only be calling the function from cells.

All of your forms, classes, code, and designers, ect. can be developed in VS instead of VBA. If my project contains a significant amount if code at all, I almost always pack it into a dll if it works for the customer.

To place your function from your OP into an ActiveX dll...
Open VB
Newproject
ActiveX DLL
Rename "Class1" to "MyFunctions"
MyFunctions will be the type that you will create.
Paste in your function making sure that it is Public.
Public Function FileCount
Set a reference to Microsoft Scripting Runtime by way of Project>References
From Project>Properties, change the project's name to "MyFunctionLibrary". This will be the name of the Library.
Choose File>Make MyFunctionLibrary.dll...
VB will register the library for you.

Open a workbook and set a reference to "MyFunctionLibrary"

Use in code.
Sub Example()
Dim mfl As MyFunctionLibrary.MyFunctions

Set mfl = New MyFunctionLibrary.MyFunctions
MsgBox mfl.FileCount("C:\Documents and Settings\Tom\Desktop")
End Sub

If you want to call these functions from your worksheets, from Excel 2002 or later, select Tools>Addins>Automation. Select "MyFunctionLibrary.MyFunctions". This will provide a behind the scenes interface with your public class, public function.

To use in a cell.
=FileCount("C:\Documents and Settings\Tom\Desktop")

Distribution is for another post. HTH

Erdin? E. Ka
12-16-2006, 06:38 PM
Hi tstom, :hi:

Your help is very understandable like Bob. Thank you very much. I tried it and it works perfect.

Thank you very very very very very much. :yes

Loves from Bursa. :hi:

malik641
12-16-2006, 09:00 PM
I've been following this thread since Erdin? started it...I couldn't post anything since I have no experience with this.

This whole thread was a great learning experience, and opened my eyes to lots of new ideas (and something new to play around with ;) ). Tom's quick tutorial is awesome :thumb and I'll be looking out for the distrubition post :)


I just want to say thanks to everyone who contributed to this thread :clap: I have learned a lot :yes 5 star rating!

Bob Phillips
12-17-2006, 01:16 PM
Continuing this thread (I agree with Joseph, it is one of the more interesting), attached is another example, calling a VB form from within a VBA app.

This instructions are in the ReadMe.txt file.

Dave
12-17-2006, 04:08 PM
I don't get it.. if your going to use VB6 why not just create an executable and add the XL wb to the project as a custom resource file? It can be packaged and installed and used like any XL wb as an .exe on any computer WITHOUT macros enabled and the security seems obvious. Thanks Tom for the info. Dave

Bob Phillips
12-17-2006, 05:11 PM
Because you can then use it in many spreadsheets.

Dave
12-18-2006, 07:29 AM
Thanks XLD that is an important difference. Have a nice day. Dave

tstom
12-18-2006, 01:03 PM
I don't get it.. if your going to use VB6 why not just create an executable and add the XL wb to the project as a custom resource file? It can be packaged and installed and used like any XL wb as an .exe on any computer WITHOUT macros enabled and the security seems obvious. Thanks Tom for the info. Dave


http://www.mrexcel.com/board2/viewtopic.php?t=201189&postdays=0&postorder=asc&highlight=resource&&start=10

Dave We kinda discussed this at the link above (AKA Right_Click), though not at all in depth. Storing small workbooks (64k or under) as resource files is ok in limited circumstances. I usually pack templates into an exe if it's reasonable. Saving data can be a pain seeing that you cannot repack a changed workbook into a compiled exe. At least I don't know how you could do that. What XLD mentioned is the big one. For example: You have distributed a workbook to 30 workstations. You decide to pack the shared code into a dll instead of using VBA. The dll resides on a network share or even a remote internet location. You have a small procedure in your workbooks that checks for an updated version on each open. If the dll version is more recent, you UnRegister the current dll server, copy or download the current dll to the local system, Register it. All of this is done by code. You have updated all of the code for all of the workbooks from one location. Depending on security policy, you will usually have to use latebinding unless you are given access to the VBA project. I even have several libraries on my own server that I can update from any PC on the net. Any workbooks that refer to these dlls are upated automatically from any PC that has internet access. Another huge plus is remoting using Windows services such as COM+ and DCOM. You pretty much have to create an activeX COM server to take advantage of these great technologies. Another great use of ActiveX dlls is an ActiveX.exe server that can perform background processing in its own thread. I have created workbooks that were too slow. So I created an exe server to perfom lengthy "procedures" and then notify the host (Excel) when it was completed. You can actually gain similiar results using a second instance of Excel by way of callbacks instead of events. Here is an example posted here with a download available.
http://www.mrexcel.com/board2/viewtopic.php?t=224532&start=0&postdays=0&postorder=asc&highlight=instance

Basically, create another instance of Excel, let it do some "stuff" in its own thread, and then notify the calling instance when it has completed. You can also return arguments, objects, ect...

Dave
12-19-2006, 09:39 AM
Well thanks again Tom for your time, info and links. Great learning material. I missed your follow-up on the initial link and thanks for that reference. Hope you have a great holiday season! Dave

andrew93
12-26-2006, 11:24 PM
Thanks for the quick tutorial Tom! After much searching on the 'net I should have come here first. I'm yet to find a more concise and accurate description of how to create a (functioning) add-in.

Thanks
Andrew