Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
548 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
With regard to my previous post, I've found the solution on this website:
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
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.