Details for warmday

With regard to my previous post, I've found the solution on this website:

https://answers.microsoft.com/en-us/msoffice/forum/all/excel-vba-sending-email-with-outlook-2010-specify/4ae28948-0b01-4eb7-8f5f-10fbfa8f4df9

Dim objOutlook As Object

Dim objMail As Object

Dim objOlAccount As Object ' NEW LINE

Set objOutlook = CreateObject("Outlook.Application")

Set objMail = objOutlook.CreateItem(0)

Set objOlAccount = objOutlook.Session.accounts.Item(2) ' NEW LINE

With objMail

Set .SendUsingAccount = objOlAccount ' NEW LINE

.BodyFormat = 2

.To = "primaryaccount@hotmail.co.uk"

.Subject = "Some subject"

.HTMLBody = "Hi"

.Send

End With

Set objOutlook = Nothing

Set objMail = Nothing

Your video uses early binding.

Do you know if it will also work for late binding?

I tried this:

Dim objOutlook As Object

Dim objMail As Object

Set objOutlook = CreateObject("Outlook.Application")

Set objMail = objOutlook.CreateItem(0)

With objMail

.SendUsingAccount = objOutlook.session.accounts("secondaryaccount@hotmail.co.uk")

.BodyFormat = 2

.To = "primaryaccount@hotmail.co.uk"

.Subject = "Some subject"

.HTMLBody = "Hi"

.Send

End With

Set objOutlook = Nothing

Set objMail = Nothing

but it failed on this line:

.SendUsingAccount = objOutlook.session.accounts("secondaryaccount@hotmail.co.uk")

with a

Run time error '450' Wrong number of arguments or invalid property assignment

message.

Is this an Excel quirk?

I have these two properties:

    Public Property Let Relaxing (ByVal R As Boolean)
        pRelaxing = R
    End Property

    Public Property Let Running(ByVal R As Boolean)
        pRunning = R
    End Property

    If I changed the capital R to a lower case r in the Relaxing Property, ie (ByVal r As Boolean), the R in the Running property also changes to a lower case r.

These two properties are not related to each other, so I didn't expect this behaviour.

In this video, you stated it's better to write:

Title = "Not Set"

rather than:

pTitle = "Not Set"

because the former will access the Let Property.

Can you tell me if the following will give the same result (actually I've checked it and it does):

Me.Title = "Not Set"

But I'm not entirely clear on the keyword "Me" in the contect of a Class Module.

Thanks

On the poiint regarding adding ByRef / ByVal when passing arguments, I have noticed the VB editor automatically adds ByVal when it is the default, eg when adding a Property Get / Let.

In the Worksheet_BeforeDoubleClick event, the VB editor adds BaVal to Target but omits for Cancel, like so:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Is Cancel ByVal or ByRef?

Thanks

I'm a little confused with regard to opening / closing of userforms and would welcome your thoughts.

Whenever I use userforms, I write MyUserform.Show to display it (which automatically loads it) and when I've finished, I write Unload (MyUserform), which will also hide it.

But this article (if I understand it correctly) says hide it, DON'T UNLOAD IT!

https://rubberduckvba.wordpress.com/2017/10/25/userform1-show/

Can you please explain.

I am trying to learn the mertis of creating your own events.

Can you please explain the use of the following code?

This is in Sheet1:

Option Explicit

    Dim WithEvents SomeClass As Class1
   
Private Sub SomeClass_ShowMessage(Message As String)
   
    MsgBox Message
   
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

    Set SomeClass = New Class1
   
    Call SomeClass.SomeMethod

End Sub

This is in Class1:

Option Explicit

    Public Event ShowMessage(Message As String)

Sub SomeMethod()

    RaiseEvent ShowMessage("Hi")

End Sub

My (limited!) understanding of this code is that when something changes on Sheet1, a messagebox displays the message "Hi".

But why go to all that length using a class module, as well as Raise Event, Event and WithEvents when you could simply write this in Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

   MsgBox "Hi!"

End Sub

Thanks

The following is code form your video:

' ThisWorkbook

Option Explicit
   
    Private xlApp As EventApp
   
Private Sub Workbook_Open()

    Set xlApp = New EventApp
   
End Sub

' Class Module called EventApp

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
    Wb.Worksheets("Sheet1").Range("A2").Value = "Created by " & Environ("UserName")
   
End Sub

I have seen this from a book:

' Class Module called EventApp

    Public WithEvents xlApp As Application

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)

    Wb.Worksheets("Sheet1").Range("A1").Value = "Created on " & Date
    Wb.Worksheets("Sheet1").Range("A2").Value = "Created by " & Environ("UserName")
   
End Sub

' Standard Module

    Public myAppEvent As New EventApp

Sub TrapAppEvent()

    Set myAppEvent.xlApp = Application

End Sub

Both versions work but there are some differences.

1. Your variables are declared Private whereas the other version is declared Public, eg Private xlApp As EventApp / Private WithEvents xlApp As Application

    The other version declares: Public WithEvents xlApp As Application / Public myAppEvent As New EventApp

2. The other version contains this line:

    Set myAppEvent.xlApp = Application

but yours doesn't.

Can you please explain the significance of these two points.

Thanks

Andrew,

We know "usually" the default for passing arguments is by reference, so if the keyword ByRef / ByVal is omitted, the argument will be passed by reference.

In a standard module, this can (and has been) easily proved.

However, I have yet to see an example to prove the case fro a Get / Let / Set Property.

Do these properties also behave in the same way, ie the default is by reference? The reason for my query is if you create a Let Property using Insert -> Procedure -> Property, the keyworb, ByVal, is automatically added.

Does that mean for a Let Property (and I assume also for a Set Property), the default, perhaps the ONLY way to pass arguments is by value?

Is it possible to prove this by way of an example?

Also what about Get Properties? Do they pass by reference by default or by value?

Thanks