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

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!

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).

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!