Create DLL From Excel Module

[J-Man]

I am extremely new with VB 6.0, so please bare with me. I have created an Excel project that I would like to create DLLs out of the modules...then access these DLLs within the Excel project. My understanding is that I need to compile these DLLs from within VB. Can someone give me the steps that I need to follow in order to do this? Thanks in advance!

[Edneeis]

I am afraid that this is not an easy task. You'll need to convert your modules from Excel into class modules of VB code and compile that as a dll. Then you'll need to have Excel reference the dll and create objects from it in order to use it.

Why do you want to move these modules to a dll?

[J-Man]

I want to make it so that I can make updates to the dll, send it out to people, and have them replace their existing dll without having to redo all of their worksheets in the Excel project. Do you have a better idea on how to handle this?

[Edneeis]

I think there is a way to use modules from other Excel Spreadsheets, if so you could have a single spreadsheet with all the module stuff and just redistribute that. Then any other Excel spreadsheets would just refer to it. Kind of how things like Adobe Acrobat incorporate themselves. It's somewhat like how you can reach the default templates modules from any excel spreadsheet. I don't really remember how to do it though, but it should give you another avenue to research.

[J-Man]

That sounds like a good avenue to pursue! However, I think that I am also going to look into the DLL thing, too, just to see how that would be done. Do you have any other insight into the DLL way? Thanks for your help!

[Edneeis]

Sure you if you want to convert it to a VB dll there is no problem in that, but you'll most likely have to convert your code to some degree. What kind of things are in the Modules? Just Functions/Subs? Does it handle Excel specific objects (ie Worksheets, Workbooks...)? Have you ever used a class module in VB before? A dll in VB exposes class modules not standard modules so you want be able to cut and paste. Also VB does not have the intrinsic Worksheet and Workbook objects along with others that are specific to Excel's VBA (but you can get to them by referencing the Excel Object in VB).

[J-Man]

I have done the following, and I am getting a "Can't find DLL entry point..." error.
I simply started an ActiveX project in VB6, and put the following function in the Class module:

Public Function Hello() As String
Hello = "GoodBye"
End Function

Then, I've declared this Function inside of Excel and tried to use the Hello function in an Excel module. What exactly am I doing wrong? Thanks.

[Edneeis]

The method of declaring the functions is for standard dlls (usually made with c/c++ and if you know c/c++ then this is the best route for making the dll) whereas VB makes ActiveX dlls which use COM to expose the entire class not just functions. So you don't do any of that declare stuff you make a reference in your Excel Project to the DLL (references in the project properties area) then you make instances of your class object kind of like this:

'if the class was called cMyClass
'after making a reference
dim Obj as New cMyClass
 
Obj.FunctionNameHere
 
'then you clean up when you are done
'to avoid memory leaks
set Obj=nothing

Or if you are just making a list of functions then change the Instancing property of the class in the dll to MultiUse. That will make it so you don't have to declare an object, but you will still make a reference and then just use the functions as if they were intrinsic to Excel.

[J-Man]

I understand all of your response except for the last paragraph. What do you mean by "Or if you are just making a list of functions..."? Maybe you could provide an example, like you did for the first part.

[Edneeis]

Well there really is no code associated with the list. What basically happens when you make the class MultiUse is that it makes all the methods of that class public to the entire project and eliminates the need for declaring an object. It'd be like having With cMyClass at the start of a project and End With at the end (but you don't need the leading .). SO lets say you have the following class named cMyClass and it has functions named MyFunc1 and MyFunc2 then I set the instancing of the class to MultiUse and compile the dll. Then in the Excel project I make a reference to the dll and use the functions as normal:

'inside the excel module or project
dim x as long
x=MyFunc1
 
'or
MyFunc2 Param1

[Edneeis]

Opps I just realized that I've been saying to use MultiUse but what I meant was GlobalMultiUse.

[Edneeis]

Here is an example which includes an Excel workbook, a dll, and the code to the dll. You'll have to register the dll or recompile it before using the Excel workbook.

[J-Man]

By recompiling the dll, does that register the dll?

[J-Man]

One more thing...is it possible to call functions in one dll from another dll?

[J-Man]

Along with my last question, can you call a function in one Class in a dll from another function in another Class in the same dll?

Thanks, and sorry for so many questions :-)

[Edneeis]

Ok first yes compiling the dll will register it, also there are rules to whether or not any applications already compiled and/or referencing the dll will continue to work without a recompile or rereference. You can set the compile options to use 'Binary compatibility' then as long as you don't change the existing interface everything will be fine. Which basically boils down to keeping everything backward compatible. For instance you can't change the arguments of an existing function but you can add new functions. Anyway, back to your questions: As for calling functions from other classes and dlls, sure you can. This creates dependancies in your dll but if its needed then its needed. ALso in regards to classes calling other classes, keep in mind that one class can't see the data from another because all that is relative to the instance that you are calling from it. Like if you have ClassA and ClassB and ClassA has a Message property and ClassB as a Show Method. Well if you tried to have ClassA 's Message Show up in ClassB's Method (internally) then that wouldn't work, because the it doesn't know the data of that particular instance of the ClassA object. You could however pass in a reference to the ClassA instance and that would solve the problem. Well I hope that clears things up although some how I think it may make things worse.

[J-Man]

Ok, I almost got it. Two more questions and that may clear it all up.

First, regarding the registering of the dll. If I move my project to another computer...one where I didn't compile the dll...I would need to register the dll on that computer, correct? Do I need some sort of software to do this, or is it a manual effort?

Second, Could you give me an example of what you mean by "You could however pass in a reference to the ClassA instance and that would solve the problem."?

Thanks for all your help. Like I said earlier, I am fairly new to the VB world.

[Edneeis]

You can register any control or dlls or what not by excuting one line in the Run dialog of windows, here is an example:
regsvr32 "C:\WINNT\system32\comdlg32.ocx"

It's just regsvr32 then the path to the dll in quotes and you'll get a message that it was successful. Also to run a VB dll you'll need the VB Runtimes and any files needed for any of the dependencies of the dll (i.e. any other components that you add). If you want/need to you can make a simple setup package using VB's Package and Development wizard.

As for passing info into the dll, VB by default passes arguments in by reference so you would just make whatever it is you want passed in a parameter of the method. Example:

'lets say that ClassB is a GlobalMultiUse instance dll
'which means we don't need to make an object to use it
'and we want to pass the Message property of ClassA
'which we did make an object of
 
'assuming the sub is named Show (although this name may conflict other object if it were in an actual GlobalMultiUse dll)
 
dim Obj as new ClassA
Obj.Message="Hey there, pass me over"
 
Show Obj.Message
'to pass in just the string message like shown here the function in ClassB for Show would look like this:
Public Sub Show(ByVal Msg as string)
 
'but if we may want other than 1 parameter of the object we can pass the whole thing byref
 
Show Obj
'to which the sub header would be
Public Sub Show(UseObj as ClassA)
 
'don't forget to clean up any objects we made
Set Obj=Nothing

[J-Man]

Cool! That makes sense to me. Thanks for all your help!!!!

[Edneeis]

No problem and welcome to the forums!

[J-Man]

One more thing before we close this thread...is it possible to "un-register" the dll once I've compiled it? I want to unregister my test dll's that I've compiled and since deleted. Thanks!

[Edneeis]

Yes but you'll need to unregister the dlls BEFORE you delete them because it needs the actual file otherwise you'll have to run RegClean (do a search for this on the Microsoft website) or just go through the registry and delete the entries that you don't need anymore. To unregister a dll you use the same method as registering it only with a /u switch added, like this:

regsvr32 "C:\Program Files\Microsoft Visual Studio\VB98\Temp\dllExample\dllExample.dll" /u <-just add the /u to the end to unregister.