On 12 Nov, 09:19, JAC <johnaco...@gmail.com> wrote:
> On 12 Nov, 00:56, "Tim Williams" <timjwilliams at gmail dot com>
> wrote:
>
>
>
> > > 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" <johnaco...@gmail.com> wrote in message
>
> >news:bbf3007f-dd2d-4341-96e4-(E-Mail Removed)....
>
> > >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,
>
> 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
Similar Threads
