Devolving code to a DLL

[JAC]

I have been trying to devolve most of my VBA code from a spreadsheet
to a DLL, and have had some success.

However, I am currently stumped. I am trying without success to put my
code for Application events into the DLL, but I can't get the events
to fire up as I expected. Can anyone suggest what I might be doing
wrong?

The following simple code fragment has been put into an ActiveX DLL
created in VB6. I have named the project EventLib. I have added the
Excel 11.0 and Office 12.0 object libraries to my project references.
Currently, the project has one class module, which I have named
clsXLEvents, following Chip Pearson's advice.
Option Explicit

Private WithEvents Appl As Application

Private Sub Class_Initialize()
MsgBox "Creating Instance", vbOKOnly, "clsXLEvents"

Set Appl = Application
End Sub

Private Sub Appl_NewWorkbook(ByVal Wb As Excel.Workbook)
MsgBox "NewWorkbook: " & Wb.Name, vbOKOnly, "DLL clsXLEvents"
End Sub

Private Sub Appl_WorkbookBeforeClose(ByVal Wb As Excel.Workbook,
Cancel As Boolean)
MsgBox "WorkbookBeforeClose: " & Wb.Name, vbOKOnly, "DLL
clsXLEvents"
End Sub

Private Sub Appl_WorkbookBeforeSave(ByVal Wb As Excel.Workbook, ByVal
SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "WorkbookBeforeSave: " & Wb.Name, vbOKOnly, "DLL
clsXLEvents"
End Sub

Private Sub Appl_WorkbookOpen(ByVal Wb As Excel.Workbook)
MsgBox "WorkbookOpen: " & Wb.Name, vbOKOnly, "DLL clsXLEvents"
End Sub

<<

I have created a simple Excel workbook, called Events.xls, and added
the EventLib.dll to my VBA references in the Tools menu.

I have placed the following code in ThisWorkbook
Option Explicit

Private XLAppl As EventLib.clsXLEvents

Private Sub Workbook_Open()
Stop
Set XLAppl = New EventLib.clsXLEvents
End Sub

<<

I was hoping to see output from the MsgBox'es when I opened an new
workbook, saved a workbook, etc.

However, no such events were trapped.

Clearly, I am missing something, but I don't know what!

Has anyone tried anything similar to this, and had any success?

Thank you in anticipation.

 

[Tim Williams]

Private WithEvents Appl As Application
try
Private WithEvents Appl As Excel.Application

Remove this line from Class_Initialize
Set Appl = Application
Your dll code isn't running in Excel, so it doesn't know about "Application"
Instead, add a property to your dll class so you can set a reference to
Excel from within VBA

Property Set ExcelApp(oXLApp as Excel.Application)
Set Appl = oXLApp
End sub

In VBA:
Private Sub Workbook_Open()
Set XLAppl = New EventLib.clsXLEvents
Set XLAppl.ExcelApp = Application
End Sub




Untested, but I think that's about it.
Tim

 

[JAC]

try
   Private WithEvents Appl As Excel.Application

Remove this line from Class_Initialize
    Set Appl = Application
Your dll code isn't running in Excel, so it doesn't know about "Application"
Instead, add a property to your dll class so you can set a reference to
Excel from within VBA

Property Set ExcelApp(oXLApp as Excel.Application)
    Set Appl = oXLApp
End sub

In VBA:
Private Sub Workbook_Open()
    Set XLAppl = New EventLib.clsXLEvents
    Set XLAppl.ExcelApp = Application
End Sub

Untested, but I think that's about it.
Tim

Tim,

Thanks for that. I'll give it a try.

 

[JAC]

Tim,

Thanks for that. I'll give it a try.

Tim,

I have implemented your suggestion and it works as you expected.

I had thought of using "Excel.Application" instead of "Application"
earlier, but had omitted one of the crucial steps.

Thank you for helping me solving a problem that had been vexing me for
some time. I had tried numerous solutions, all of them incorporating
bits of the actual solution, but regrettably each one was incomplete
in itself.

Once again, it pays to consider the detail and take an overview of
what one is trying to achieve, before getting bogged down in fruitless
code revisions.

I am obliged. I hope others find this useful.

JAC

 

[Tim Williams]

Glad to hear it worked out.

Tim

Tim,

Thanks for that. I'll give it a try.

Tim,

I have implemented your suggestion and it works as you expected.

I had thought of using "Excel.Application" instead of "Application"
earlier, but had omitted one of the crucial steps.

Thank you for helping me solving a problem that had been vexing me for
some time. I had tried numerous solutions, all of them incorporating
bits of the actual solution, but regrettably each one was incomplete
in itself.

Once again, it pays to consider the detail and take an overview of
what one is trying to achieve, before getting bogged down in fruitless
code revisions.

I am obliged. I hope others find this useful.

JAC