New Classes Created in a Loop All Have Propery Set to Last Instance

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi!

I'm running into an issue with placing new instances of class objects into a dictionary where setting one of the class's properties in the loop somehow updates all instances of the class in the dictionary... 😕

My code loops through the sheets in a workbook and puts data about those sheets into a dictionary. The different data pieces are stored in a class created just for that purpose.

The problem I'm having is that the .DataArray() property of my classes always equals the value set in the last run through the loop - for all the objects in the dictionary! So, for example, if there are two worksheets and the array in the first one is {1, 2, 3} and the array in the second one is {4, 5, 6}, after the code runs, the .DataArray() value for dctSheetData("Sheet1").DataArray() is {4, 5, 6} like the value for dctSheetData("Sheet2").DataArray() is {4, 5, 6} - every dctSheetData(key).DataArray() has been assigned the value assigned to the last object in the loop.

I feel like this should be easily-achieved... so I must be missing something.

My procedure:

VBA Code:
' declarations here 

For Each ws In wb.Worksheets

    Dim objSheetData As New clsSheetData
    
    objSheetData.DataArray = ws.UsedRange
    objSheetData.SetDictionary (ws)
    
    dctSheetData.Add ws.Name, objSheetData

Next ws

My class:

VBA Code:
Private arrData As Variant
Private dctDictionary As New Scripting.Dictionary

Private Sub Class_Initialize()
    dctDictionary.RemoveAll
End Sub

Property Get DataArray()
    DataArray = arrData
End Property

Property Let DataArray(rng As Excel.Range)
    arrData = rng
End Property

Public Sub SetDictionary(ws As Excel.Worksheet)
    Dim dctTemp As New Scripting.Dictionary
    
    'add items to dctTemp
    
    Set dctDictionary = dctTemp
    
End Sub

Any help is always appreciated.

Thank you!
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
Should you not be creating a new instance of the class for each iteration of the loop?
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Should you not be creating a new instance of the class for each iteration of the loop?
Oh boy! This is my second attempt working with classes...

I though that's what I was doing with Dim objSheetData As New clsSheetData.

What should I be doing instead?

The dictionary I set up is different for each one (I have a separate Get property that returns that from the class). But maybe that's just a coincidence from how I have that working?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,274
Office Version
  1. 365
Platform
  1. Windows
You should declare objSheetData at the top of the code, without the New keyword.

Within the loop you should create a new instance of the class on each iteration like this.
VBA Code:
Set = New clsSheetData

I think that should work, but I wasn't able to test because I was getting errors with the class code you posted.
 
Solution

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,123
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

At its simplest, I think you're trying to achieve this:

VBA Code:
Dim dctDictionary As Scripting.Dictionary
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook   'adjust as required
Set dctDictionary = New Scripting.Dictionary
    
For Each ws In wb.Worksheets
    dctDictionary.Add ws.Name, ws.UsedRange.Value
Next ws

Your dictionary use at the moment is a bit muddled. If you want to code using classes, your options include:

- A code module dictionary containing CLASSA objects (arrays), or
- CLASSB (a dictionary) containing CLASSA objects (arrays), or
- CLASSC (a dictionary containing arrays)

More code will be required if you want to build a dictionary class - to be self-contained, the class module will need routines to add, remove and return particular items, return the count etc.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
You should declare objSheetData at the top of the code, without the New keyword.

Within the loop you should create a new instance of the class on each iteration like this.
VBA Code:
Set = New clsSheetData

Yes!! That did it!

So now I need to learn about the difference in these two methods and when to use which. Funny thing, after I saw your post I went researching about the Dim x As New y versus Dim x As y : Set x = New y and one of the first things I found mentioned this very problem...


I think that should work, but I wasn't able to test because I was getting errors with the class code you posted.

My bad... in editing out the irrelevant pieces of code I must have goofed something else up. Possibly with getting rid of the .Value at the end of arrData = rng.

Anyway, got it all working now. So thank you so much!
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
455
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
At its simplest, I think you're trying to achieve this:

VBA Code:
Dim dctDictionary As Scripting.Dictionary
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook   'adjust as required
Set dctDictionary = New Scripting.Dictionary
    
For Each ws In wb.Worksheets
    dctDictionary.Add ws.Name, ws.UsedRange.Value
Next ws

Unfortunately that won't quite work for my purposes. I stripped quite a bit of stuff from my code when I posted it but there is more stored in my class than just the value in the UsedRange.

Thank you for offering the suggestion, though; I genuinely do appreciate you taking the time to look at my problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,130
Messages
5,628,871
Members
416,347
Latest member
AT2021

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
Top