VBA: Dictionary - Can only retrieve the last entry

Question

This is probably a newbie mistake where I'm not aware of some setting that I haven't changed. Anyways, I'm trying use Dictionary to store a instances of class I've created.

Class cls_Connote is just a container of details.

Public connoteNumber As String
Public despatchDate As Date
Public carrier As String
Public service As String
Public items As Integer
Public weight As Integer
Public cost As Single
Public surchargeType As String

Here is how I'm storing the details into the class then into the dictionary.

Function getSurcharge_tag(givenTag As String, givenCol As String, ByRef dicStore As Dictionary, ByRef counter As Integer)`

Dim tagLen As Integer
Dim conNum, conTag As String

Dim clsSurchargeDetails As New cls_Connote
Dim despatchDate, carrier As String
Dim items, weight As Integer
Dim cost As Single


Range(givenCol).Select

tagLen = Len(givenTag)

Do While (ActiveCell.Value <> "")
    conNum = Mid(ActiveCell.Value, 1, Len(ActiveCell.Value) - 1)
    conTag = Mid(ActiveCell.Value, Len(ActiveCell.Value) - tagLen + 1, Len(ActiveCell.Value))

    If (conTag = givenTag) Then 'Remove: both the Original and Adjusted connote lines

        despatchDate = ActiveCell.Offset(0, -2).Value
        items = ActiveCell.Offset(0, 10).Value
        weight = ActiveCell.Offset(0, 11).Value
        cost = ActiveCell.Offset(0, 12).Value

        clsSurchargeDetails.connoteNumber = conNum
        clsSurchargeDetails.despatchDate = despatchDate
        clsSurchargeDetails.carrier = carrier
        clsSurchargeDetails.items = items
        clsSurchargeDetails.weight = weight
        clsSurchargeDetails.cost = cost
        clsSurchargeDetails.surchargeType = givenTag

        dicStore.Add conNum, clsSurchargeDetails
        givenCtr = givenCtr + 1

        ActiveCell.EntireRow.Delete
    Else
        ActiveCell.Offset(1, 0).Select
    End If
Loop
End Function

This is how I'm trying to get the connotes out of the Dictionary.

Function displaySurcharges(wrkShtName As String, ByRef dicList As Dictionary)

'Remove the existing worksheet
Dim wrkSht As Worksheet
On Error Resume Next
    Set wrkSht = Sheets(wrkShtName)
On Error GoTo 0
If Not wrkSht Is Nothing Then
    Worksheets(wrkShtName).Delete
End If

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = wrkShtName

populateColumnHeaders

Range("A2").Select

Dim getCon As cls_Connote
Set getCon = New cls_Connote
Dim vPtr As Variant
Dim ptrDic As Integer

For Each vPtr In dicList.Keys

    Set getCon = dicList.Item(vPtr)

    ActiveCell.Value = getCon.connoteNumber
    ActiveCell.Offset(0, 1).Value = getCon.despatchDate
    ActiveCell.Offset(0, 2).Value = getCon.carrier
    ActiveCell.Offset(0, 12).Value = getCon.items
    ActiveCell.Offset(0, 13).Value = getCon.weight
    ActiveCell.Offset(0, 15).Value = getCon.cost
    ActiveCell.Offset(0, 16).Value = getCon.surchargeType

    Set getCon = Nothing
    ActiveCell.Offset(1, 0).Select
Next vPtr
End Function

I can see dicList does contain different details, getCon only gets the last entry in the Dictionary.

Any help would be fantastic !

score 0 · Accepted Answer · answered May 08 '13 at 09:43

0

To avoid reusing and adding the same reference within the loop, when you need a new instance (after If (conTag = givenTag)) just ask for one:

Set clsSurchargeDetails = New cls_Connote

answered May 08 '13 at 09:43

Alex K.

171,639
30
264
288

VBA: Dictionary - Can only retrieve the last entry

1 Answers1