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
550 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 ...
Posted by Andrew Gould on 13 October 2014
Class Modules in VBA allow you to define your own classes of objects. This video explains why that's a useful and shows you how to go about creating a class. You'll learn about the two class events: initialise and terminate, you'll see how to define properties, including how to create read-only properties and set default values. You'll also find out how easy it is to create methods for your class and how to use your class in your regular code.
See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.
There are no files which go with this video.
There are no exercises for this video.
You can increase the size of your video to make it fill the screen like this:
Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown above.
When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.
To improve the quality of a video, first click on the Settings icon:
Make sure yoiu're playing your video so that the icons shown above appear, then click on this gear icon.
Choose to change the video quality:
Click as above to change your video quality.
The higher the number you choose, the better will be your video quality (but the slower the connection speed):
Don't choose the HD option shown unless your connection speed is fast enough to support it!
Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.
From: | duggie |
When: | 02 Nov 22 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
From: | Andrew G |
When: | 02 Nov 22 at 13:06 |
Hi duggie!
Me simply refers to the instance of the class. It's redundant in a class module - if you don't qualify the reference to the property or method you're calling, VBA will assume you're referring to the property or method defined in the class. So Title and Me.Title are the same.
If you create a method or property in a class module which has the same name as an existing VBA method or property you may find it useful to use the Me keyword to indicate when you're referring to the class module version of the method or property but it's still not required.
I hope it helps!
From: | warmday |
When: | 03 Nov 22 at 22:47 |
Andrew,
Can you please elaborate what you mean. It seems to contradict but obviously it's because I've misunderstood you.
You state:
"It's redundant in a class module"
then you go on to say:
"If you create a method or property in a class module which has the same name as an existing VBA method or property you may find it useful to use the Me keyword to indicate when you're referring to the class module version of the method or property"
Can you show sample code of whayt you mean?
Thanks
From: | Andrew G |
When: | 07 Nov 22 at 08:24 |
Hi duggie,
Yes, indeed, you can't have duplicate names for methods and properties in the same module. So, the existing method or property I mentioned must be outside the class in which you're writing Me.
I'm sorry that I don't have time to look at the code you posted at the moment. I'll try to look at this when I have time but it's unlikely to be soon, we're very busy at the moment!
From: | warmday |
When: | 06 Nov 22 at 12:46 |
Andrew,
Thanks for your reply. I think I've finally understood what you mean.
When you wrote: "If you create a method or property in a class module which has the same name as an existing VBA method or property ..... "
in particular: "which has the same name as an existing VBA method or property"
you are saying - as an existing VBA method or property THAT IS OUTSIDE OF THE CLASS MODULE?
Am I right?
Sorry to be a pain but further to all this, the following two methods appear to be quite interesting and I would be grateful if you could tell me the reason for the difference in the outcome.
Method1:
' Standard Module:
Option Explicit
Public Sub WrongMethod()
Dim OrigVals As Variant
OrigVals = Sheet1.Cells(1, 1).CurrentRegion.Value
Dim a As ClassWrongMethod
Set a = New ClassWrongMethod
With a
Set .ws = Sheet1
.DataArray = OrigVals
Call .CreateList
End With
End Sub
' ClassWrongMethod
Option Explicit
Private pDataArray As Variant
Private pws As Worksheet
Public Property Get DataArray() As Variant
DataArray = pDataArray
End Property
Public Property Let DataArray(ByVal DArray As Variant)
pDataArray = DArray
End Property
Public Property Get ws() As Worksheet
Set ws = pws
End Property
Public Property Set ws(ByVal w As Worksheet)
Set pws = w
End Property
Public Sub CreateList()
Dim DataArrayRows As Long
DataArrayRows = UBound(Me.DataArray, 1)
Dim Counter As Long
For Counter = 1 To DataArrayRows
If Len(Me.DataArray(Counter, 1)) < 5 Then
Do Until Len(Me.DataArray(Counter, 1)) = 5
Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)
Loop
End If
Next Counter
Me.ws.Cells(1, 3).Resize(DataArrayRows, 1).Value = Me.DataArray
End Sub
Method2:
' Standard Module:
Option Explicit
Public Sub CorrectMethod()
Dim a As ClassCorrectMethod
Set a = New ClassCorrectMethod
Set a.ws = Sheet1
Dim i As Long
For i = 1 To 5
a.DataArrayItem(i, 1) = Cells(i, 1)
Next i
Call a.CreateList
End Sub
' ClassCorrectMethod
Option Explicit
Private pDataArray As Variant
Private pws As Worksheet
Private Sub Class_Initialize()
ReDim pDataArray(1 To 5, 1 To 1) As Variant
End Sub
Public Property Get DataArray() As Variant
DataArray = pDataArray
End Property
Public Property Let DataArray(ByVal DArray As Variant)
pDataArray = DArray
End Property
Property Get DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant) As Variant
DataArrayItem = pDataArray(RowIndex, ColIndex)
End Property
Public Property Let DataArrayItem(ByVal RowIndex As Variant, ByVal ColIndex As Variant, MArray As Variant)
pDataArray(RowIndex, ColIndex) = MArray
End Property
Public Property Get ws() As Worksheet
Set ws = pws
End Property
Public Property Set ws(ByVal w As Worksheet)
Set pws = w
End Property
Public Sub CreateList()
Dim DataArrayRows As Long
DataArrayRows = UBound(Me.DataArray, 1)
Dim Counter As Long
For Counter = 1 To DataArrayRows
If Len(Me.DataArrayItem(Counter, 1)) < 5 Then
Do Until Len(Me.DataArrayItem(Counter, 1)) = 5
Me.DataArrayItem(Counter, 1) = "0" & Me.DataArrayItem(Counter, 1)
Loop
End If
Next Counter
Me.ws.Cells(1, 3).Resize(DataArrayRows, 1).Value = Me.DataArray
End Sub
The first method gets stuck in an infinite loop. This line calls the Get Property twice:
Me.DataArray(Counter, 1) = "0" & Me.DataArray(Counter, 1)
but in the correct method, this line first goes to the Get propery, then the Let property:
Me.DataArrayItem(Counter, 1) = "0" & Me.DataArrayItem(Counter, 1)
but I can't see why it should be the case (that the wrong method calls the Get Property twice and doesn't "hit" the Let Property at all).
From: | Andrew G |
When: | 05 Nov 22 at 08:04 |
Hi duggie,
So, if you create a method or property called Title in a class module you can write Title or Me.Title in the class module and it means the same. So Me is redundant.
If, for some reason, you decided to create a method or property called MsgBox in a class module you can write MsgBox or Me.MsgBox in the class module and it means the same. So Me is still redundant.
You may prefer to use Me to make your intention clear when you write your code.
I hope it helps!
From: | duggie |
When: | 31 Jan 20 at 13:25 |
Hi,
Can you please explain why Method 1 fails but Method 2 works?
Method 1:
'This is Class1
Option Explicit
Public MyArr As Variant
Private Sub Class_Initialize()
ReDim MyArr(1 To 5) As Variant
Dim i As Integer
For i = 1 To 5
MyArr(i) = 0
Next i
End Sub
'This is in a standard module
Option Explicit
Sub Start()
Dim abc As Class1
Set abc = New Class1
abc.MyArr(1) = 10
Debug.Print abc.MyArr(1)
End Sub
After this line has been run:
abc.MyArr(1) = 10
in the Immediate Window, I see:
abc.MyArr(1) = 0
I expect it to be 10.
However using tmp variable seems to do the trick.
Method 2:
Sub Start()
Dim tmp As Variant
Dim abc As Class1
Set abc = New Class1
tmp = abc.MyArr
tmp(1) = 10
abc.MyArr = tmp
Debug.Print abc.MyArr(1)
End Sub
Thanks
From: | Andrew G |
When: | 01 Feb 20 at 13:05 |
Hi duggie, I hope it's OK to answer this question with a link but there's a good explanation of what's going on here https://stackoverflow.com/questions/25328975/array-as-a-class-member
I hope that helps!
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.