Why is Excel VBA overwriting these object values

Question

I am trying to create a list of objects in VBA but it seems like new objects are not being created and values are being updated to a single instance of a class.

This is the class

' ---------------------------------------------------------------
'
' Class to represent Program Increment
'
' ---------------------------------------------------------------

Public name As String

Public sprints As New Collection

This is the calling code:

' get the unique pi values
Dim piList As New Collection
For r = firstRow To lastRow
    currentVal = Cells(r, 2)
    On Error Resume Next
        Dim currentPi As New ProgramIncrement
        currentPi.name = currentVal
        piList.Add currentPi, currentVal
    On Error GoTo 0
Next

This is the output for the first pi

And this is the output for the second pi

I'm not seeing what I'm doing wrong base upon online documents such as this. https://analystcave.com/vba-vba-class-tutorial/

Mathieu Guindon · Accepted Answer · 2019-06-07 17:39:33Z

7

As New creates an auto-instantiated object. Dim statements aren't executable, so there's only one object indeed.

Remove As New and use Set ... = New statements to create new objects.

Dim currentPi As ProgramIncrement
Set currentPi = New ProgramIncrement

Dim being inside the loop makes no difference - on one hand it makes it easy to later refactor and extract the loop body into its own procedure scope; on the other hand it can be read as though a new variable is created at every iteration, but that's not how scopes work in VBA: the smallest scope is procedure scope - blocks (e.g. loop bodies) don't scope anything.

answered Jun 7, 2019 at 17:39

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John · Accepted Answer · 2019-06-08 15:55:23Z

-1

This worked per Mathieu Guindon's answer.

Dim piList As New Collection
Dim currentPi As ProgramIncrement
For r = firstRow To lastRow
    currentVal = Cells(r, 2)
    Set currentPi = New ProgramIncrement
    currentPi.name = currentVal
    On Error Resume Next
        piList.Add currentPi, currentVal
    On Error GoTo 0
Next

edited Jun 8, 2019 at 15:55

answered Jun 7, 2019 at 17:58

John

4,0545 gold badges41 silver badges74 bronze badges

1 Comment

John Over a year ago

Edited to reflect this.

Collectives™ on Stack Overflow

Why is Excel VBA overwriting these object values

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related