Hi all,

To begin with: I have searched the forums and google for an hour or so to find a good tutorial about creating a Visual Basic 6 DLL, which can be used by Excel VBA..

Here's the problem:

I have created a very simple VB 6 DLL, which contains this code

Public Sub create(vrble)
MsgBox "message generated from VB6 DLL"
MsgBox vrble

Workbooks(vrble).ActiveSheet.Range("A1").Value = "test dll"
End Sub

I did the Project>References>Microsoft Excel 10.0 Library, and also the reference to my DLL in Excel VBA, in where a module contains the following code:

Sub linkdll()
Dim link As clsLinkDLL
Set link = New clsLinkDLL
link.create (ThisWorkbook.Name)
Set link = Nothing
End Sub

''''''''''''''''''''''''''
What I am trying to do, is to run a huge amount of code in an VB6 DLL, but it needs to be linked to my active workbook..
So, for instance, if I have a workbook "Test.xls", and I call a subroutine in the VB6 DLL, it needs to know which workbook is used. So I tried to pass along the name of the workbook, which works initially. The messagebox gives me the name of the workbook in the DLL, but the code after that crashes..

In simple, does anyone know how pass on a workbook, and make sure the DLL can work with that workbook (i.e. put information in cells etc.)

Help would be much appreciated (I know this should be an easy answer, but somehow I couldnt work it out)

try passing in the Worksheet object itself e.g.

and then in your VBA:

Hi gcianfanelli,

Thanks for your fast reply..

I copied your code into my subroutines, but a same kind of error occurs. I would really like to be able to pass along the workbookname, so i have absolute freedom in what I do...

Any other ideas are much appreciated, and if you know any links to an Excel VBA/VB6 DLL tutorial please let me know

Thanks
Max

The problem you're having is because your DLL doesn't know about the instance of the Excel application you want to use.

I think you have 2 options:

1. Create a COM Addin for Excel
This will give you direct access to the current instance of the Excel application. You can then correctly identify the Workbook you need.

2. Pass in the Excel application as a parameter in your object's method
This will work just as well as the above, although it will mean you have to explicitly initialise your class or method with the current instance of the Excel application

There are many examples on the web for creating a COM addin, however the 2nd method is probably the best option for you judging by the stage you seem to be at.

Consider modifying your code like so:

hi gcianfanelli,

I tried your code, but the statement
link.create (application, ThisWorkbook.Name) gives a syntax error in VBA.

Secondly, allow me to give a little bit more information of my whereabouts..

I already made an addin file (.xla), which is automatically loaded by specific files i created (.xls files). I want to convert this addin into VB6 DLL's (or perhaps, COM Add-In).
I guess it would be best to create a COM addin, as I read above. Would this be of the same security level as of an VB6 DLL? (I guess this will be the same)

However, can this addin contain multiple modules and also (user)forms?

Thanks for the help so far..
Max