Class instead of array

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,924
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
On my worksheet, in column A, I have 5 rows of data and a heading in cell A1.

Say:

Code:
Name
a
a
b
c
d

This is my code without using classes:

Code:
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:

Code:
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:

Code:
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
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Your code doesn't make much sense to me as it doesn't actually work (or compile), but wouldn't you just want:
Rich (BB code):
    MyClass.Name = MyArray(i, 1)
    
    if MyClass.Name <> MyArray(i + 1, 1) 
    
    If MyArray(i, 1) <> MyArray(i + 1, 1) Then
 
Upvote 0
I think you're probably trying to write something like:
Rich (BB code):
    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
 
Last edited:
Upvote 0
Your code doesn't make much sense to me as it doesn't actually work (or compile), but wouldn't you just want:
Rich (BB code):
    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:

Rich (BB code):
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:

Rich (BB code):
MyArray(i, 1) <> MyArray(i + 1, 1)

with something involving the name via the class, ie:

Rich (BB code):
If MyClass.Name <> MyArray(i+1, 1) Then

I can replace the LHS but not the RHS.

This is taking info from here:


Thanks
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,950
Messages
6,175,582
Members
452,653
Latest member
craigje92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top