Application event

[tiredofit]

Can someone please explain the difference between these two ideas:

Idea 1:

This is in ThisWorkbook:

Option Explicit

    Private abc As Class1

Private Sub Workbook_Open()

    Set abc = New Class1

End Sub

This is in Class1:

Option Explicit

    Private WithEvents xlApp As Application

Private Sub Class_Initialize()

    Set xlApp = Application

End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
  
    Wb.Worksheets("Sheet1").Range("A1").Value = "Created on " & Date

End Sub

Idea 2:

This is in ThisWorkbook:

Option Explicit

    Private abc As New Class1

Private Sub Workbook_Open()

    Set abc.xlApp = Application

End Sub

This is in Class1:

Option Explicit

    Public WithEvents xlApp As Application

Private Sub Class_Initialize()

    Set xlApp = Application

End Sub

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)

    Wb.Worksheets("Sheet1").Range("A1").Value = "Created on " & Date

End Sub

The differences being between Sub Workbook_Open(), where one is:

Set abc = New Class1

and the other:

Set abc.xlApp = Application

Also in Class1, WithEvents is declared as Private in the First but Public in the second.

Both First and Second work, so what impact do the differences have?

Thanks

 

[Jaafar Tribak]

In the second example, you are hooking the application events twice unnecessarly. You can just get rid of :

Private Sub Class_Initialize()

    Set xlApp = Application

End Sub

BTW, the first method is what you should use... Have a read at this What's the difference between Dim As New vs Dim / Set

 

[tiredofit]

In the second example, you are hooking the application events twice unnecessarly. You can just get rid of :

Private Sub Class_Initialize()

    Set xlApp = Application

End Sub

BTW, the first method is what you should use... Have a read at this What's the difference between Dim As New vs Dim / Set

thanks

re delcaring public v private, how comes it doesn't seem to make a difference in these two examples?

 

[Jaafar Tribak]

re delcaring public v private, how comes it doesn't seem to make a difference in these two examples?

Do you mean ;

Public/Private WithEvents xlApp As Application

If you declare withevents Private then it won't compile at :

  Set abc.xlApp = Application

Bexause the xlApp class Method won't be visible to the caller code

 

[tiredofit]

Do you mean ;

Public/Private WithEvents xlApp As Application

If you declare withevents Private then it won't compile at :

  Set abc.xlApp = Application

Bexause the xlApp class Method won't be visible to the caller code

Yes I did.

Thanks again for the explanation.

 

[Jaafar Tribak]

Yes I did.

Thanks again for the explanation.

You are welcome.

I strongly recommend that read the page in the link I posted above if you haven't read it yet.

 

[tiredofit]

You are welcome.

I strongly recommend that read the page in the link I posted above if you haven't read it yet.

Yes I have read it and am aware of the benefits of instantiating using two lines instead of a single line.