Class instead of array

[tiredofit]

On my worksheet, in column A, I have 5 rows of data and a heading in cell A1.

Say:

Name
a
a
b
c
d

This is my code without using classes:

Option Explicit

Sub NoClass()

Dim MyArray(1 To 6, 1 To 1) As Variant

Dim i As Integer

For i = 2 To 5

    If MyArray(i, 1) <> MyArray(i + 1, 1) Then ' Do something
    
Next i

End Sub

With a class, this is Class1:

Option Explicit

Private pName As String

Public Property Get Name() As Variant
    
    Name = pName
    
End Property

Public Property Let Name(ByVal N As Variant)
    
    pName = N
    
End Property

This is a standard module:

Sub WithClass()

Dim MyArray(1 To 6, 1 To 1) As Variant

Dim i As Integer

Dim MyClass As Class1

For i = 2 To 5
    
    Set MyClass = New Class1
    
    MyClass.Name = MyArray(i, 1)
    
    if MyClass.Name <> ??????
    
    If MyArray(i, 1) <> MyArray(i + 1, 1) Then
    
Next i

End Sub

What I can't work out is what should go in the ?????

Thanks

 

[Kyle123]

Your code doesn't make much sense to me as it doesn't actually work (or compile), but wouldn't you just want:

    MyClass.Name = MyArray(i, 1)
    
    if MyClass.Name <> MyArray(i + 1, 1) 
    
    If MyArray(i, 1) <> MyArray(i + 1, 1) Then

 

[Kyle123]

I think you're probably trying to write something like:

    Dim myArray(1 To 6) As Class1
    
    For i = LBound(myArray) To UBound(myArray)
        Set myArray(i) = New Class1
        myArray(i).Name = Sheets(1).Range("a1").Offset(i).Value2
        If i > 1 Then
            If myArray(i).Name <> myArray(i - 1).Name Then
                MsgBox "Different Value"
            End If
        End If
    Next i

 

[tiredofit]

Your code doesn't make much sense to me as it doesn't actually work (or compile), but wouldn't you just want:

    MyClass.Name = MyArray(i, 1)
    
    if MyClass.Name <> MyArray(i + 1, 1) 
    
    If MyArray(i, 1) <> MyArray(i + 1, 1) Then

Sorry, a mistake on my behalf.

Should have been:

Sub WithClass()

Dim MyArray(1 To 6, 1 To 1) As Variant

Dim i As Integer

Dim MyClass As Class1

For i = 2 To 5
    
    Set MyClass = New Class1
    
    MyClass.Name = MyArray(i, 1)
    
    If MyClass.Name <> ?????? Then
    
    
Next i

End Sub

I'm trying to replace this:

MyArray(i, 1) <> MyArray(i + 1, 1)

with something involving the name via the class, ie:

If MyClass.Name <> MyArray(i+1, 1) Then

I can replace the LHS but not the RHS.

This is taking info from here:

https://excelmacromastery.com/vba-class-modules/

Thanks

 

[Kyle123]

Doesn't my code do that?

 

[tiredofit]

Yes, thanks.

 

[Kyle123]

Great glad you got it sorted