This page has 2 threads | Add post

Finding our videos useful? Please consider helping us to share more!

VBA - classes and structures videos | Excel VBA Part 35 - Class Modules

Posted by Andrew Gould on 13 October 2014

Excel VBA Part 35 - Class Modules

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.

Making a video bigger

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.

Improving the quality of a video

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.

This page has 2 threads

Add a new post

From:	duggie
When:	02 Nov 22 at 12:43

Reply to duggie

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

Reply to Andrew G

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

Reply to warmday

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

Reply to Andrew G

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

Reply to warmday

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

Reply to Andrew G

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

Reply to duggie

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

Reply to Andrew G

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!

From:	duggie
When:	04 Feb 20 at 17:29

Reply to duggie

Thanks for the link.

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BY CATEGORY

VBA CATEGORIES