Accesing functions in dll created in VBA

[Tammy]

I've posted this one on the Excel programming group site but have had
no response so thought I'd try my luck here. I've created a dll from
within Excel which all works well. I now have to reference it in a
workbook but can't get it to work.

I've spent hours searching in groups and know that I can't use the
"new" keyword in my workbook. Following the instructions I've found
I've created a function in my dll which will create the new instance
of the class I want and then have code in the workbook calling that
function. The class is set to PublicNotCreatable.

In the dll the function in the standard module is as below:

Public Function GetProcedures () as cProcedures
Set GetProcedures = New cProcedures
End Function

In the workbook I have set a reference to the dll and have the
following code:
Dim cAlemy As AlemyExcelAddin.cProcedures
Set cAlemy = AlemyExcelAddin.GetProcedures()

The problem is that the workbook doesn't see the GetProcedures
function. All the procedures in the cProcedures class show up but
not the procedures in the standard module.

Any suggestions greatly appreciated as I have no idea what I'm doing
wrong and really don't want to have to recreate all the forms in VB6.

Thanks
Tammy

 

[Steve Jorgensen]

Did you word your quesiton correctly? Excel does not create DLLs.

 

[Tammy]

It doesn't normally but I've loaded the Office Developer edition which
gives the office products (Word, Excel etc) the capability of creating
dlls. Works well but has a limition in that it can't use the "New"
keyword to instantiate a class when accessing the dll from another
workbook. I've tried the suggested workarounds that I've found with
no success.

 

[Stephen Bullen]

Hi Tammy,

It doesn't normally but I've loaded the Office Developer edition which
gives the office products (Word, Excel etc) the capability of creating
dlls. Works well but has a limition in that it can't use the "New"
keyword to instantiate a class when accessing the dll from another
workbook. I've tried the suggested workarounds that I've found with
no success.

The Office Developer edition can not create standalone dlls in the way you want it to. To create dlls with creatable classes, you need to copy the
code to VB and create the dll there.

That said, the one thing you can do is package your app as a COM Addin for Excel and expose your class from the COM Addin. To do that:

1. Add an 'Addin Designer' class to the project and set it to target Excel.

2. In the Designer's code module, put the following code (watch out for word wrap):

'Expose this class to the COM Addin 'Object' property
Private Sub AddinInstance_OnConnection(ByVal Application As Object, ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As
Object, custom() As Variant)
AddInInst.Object = Me
End Sub

'Expose a Procedures property to return a new instance of the cProcedures class
Public Property Get GetProcedures() As cProcedures
Set GetProcedures = New cProcedures
End Property

3. Use it like:

Dim cAlemy As AlemyExcelAddin.cProcedures
Set cAlemy = Application.COMAddins("AlemyExcelAddin.DesignerClassName").Object.GetProcedures

But the best solution is really to move your code to VB6.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[Tammy]

Thanks Stephen, that's answered my question. I'll take your advice
and move the code into VB.

Thanks again,
Tammy