JonXL
Well-known Member
- Joined
- Feb 5, 2018
- Messages
- 513
- Office Version
- 365
- 2016
- Platform
- 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
I feel like this should be easily-achieved... so I must be missing something.
My procedure:
My class:
Any help is always appreciated.
Thank you!
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!