VBA class instances

Question

I'm having an issue in VBA where every item in the array is being replaced every time i add something to that array.

I am attempting to go through the rows in a given range and cast every row of that into a custom class (named 'CustomRow' in below example). there is also a manager class (called 'CustomRow_Manager' below) which contains an array of rows and has a function to add new rows.

When the first row is added it works fine: https://drive.google.com/file/d/0B6b_N7sDgjmvTmx4NDN3cmtYeGs/view?usp=sharing

however when it loops around to the second row it replaces the contents of the first row as well as add a second entry: https://drive.google.com/file/d/0B6b_N7sDgjmvNXNLM3FCNUR0VHc/view?usp=sharing

Any ideas on how this can be solved?

I've created a bit of code which shows the issue, watch the 'rowArray' variable in the 'CustomRow_Manager' class

Macro file https://drive.google.com/file/d/0B6b_N7sDgjmvUXYwNG5YdkoySHc/view?usp=sharing

otherwise code is below:

Data

    A   B   C
1   X1  X2  X3
2   xx11    xx12    xx13
3   xx21    xx22    xx23
4   xx31    xx32    xx33

Module "Module1"

Public Sub Start()
Dim cusRng As Range, row As Range
Set cusRng = Range("A1:C4")
Dim manager As New CustomRow_Manager
Dim index As Integer
index = 0
For Each row In cusRng.Rows
    Dim cusR As New CustomRow
    Call cusR.SetData(row, index)
    Call manager.AddRow(cusR)
    index = index + 1
Next row
End Sub

Class module "CustomRow"

Dim iOne As String
Dim itwo As String
Dim ithree As String
Dim irowNum As Integer


Public Property Get One() As String
    One = iOne
End Property
Public Property Let One(Value As String)
    iOne = Value
End Property

Public Property Get Two() As String
    Two = itwo
End Property
Public Property Let Two(Value As String)
    itwo = Value
End Property

Public Property Get Three() As String
    Three = ithree
End Property
Public Property Let Three(Value As String)
    ithree = Value
End Property

Public Property Get RowNum() As Integer
    RowNum = irowNum
End Property
Public Property Let RowNum(Value As Integer)
    irowNum = Value
End Property

Public Function SetData(row As Range, i As Integer)
    One = row.Cells(1, 1).Text
    Two = row.Cells(1, 2).Text
    Three = row.Cells(1, 3).Text
    RowNum = i
End Function

Class module "CustomRow_Manager"

    Dim rowArray(4) As New CustomRow
    Dim totalRow As Integer

    Public Function AddRow(r As CustomRow)
        Set rowArray(totalRow) = r

        If totalRow > 1 Then
            MsgBox rowArray(totalRow).One & rowArray(totalRow - 1).One
        End If
        totalRow = totalRow + 1
    End Function

Read e.g. this article about auto-instancing variable. In general, you should avoid auto-instancing variables. HTH — Daniel Dušek

chris neilsen chris neilsen · Accepted Answer · 2015-12-11T06:31:36

Your issue is using

Dim cusR As New CustomRow

inside the For loop. This line is actually only executed once (note that when you single F8 step through the code it does not stop on that line)

Each itteration of the For loop uses the same instance of cusR. Therefore all instances of manager added to the class point to the same cusR

Replace this

For Each row In cusRng.Rows
    Dim cusR As New CustomRow

with this

Dim cusR As CustomRow
For Each row In cusRng.Rows
    Set cusR = New CustomRow

This explicitly instantiates a new instance of the class

VBA class instances

1 Answers