Our training courses

Other training resources

Our training venues

Why we are different

Details for warmday

warmday has participated in the following threads:

Added by warmday on 22 Jan 2023 at 18:31

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.

Added by duggie on 02 Nov 2022 at 12:43

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

 

 

Added by warmday on 18 Oct 2021 at 15:06

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

 

Added by warmday on 10 May 2021 at 15:05

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.

 

Added by warmday on 22 Apr 2020 at 11:15

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

 

 

Added by warmday on 08 Feb 2020 at 21:16

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

 

Added by duggie on 11 Jun 2018 at 17:37

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

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.

End of small page here
Please be aware that our website uses cookies!
I'm OK with this Tell me more ...