vba - 字典不显示特定键(数值)的项目

Question

这是一篇很长的文章，但请留在我身边...

我有一个字典，它将“PO”保存为Key，将“SO”保存为项目(在某些情况下，某个“PO”可能有多个“SO”) .

工作表中的我的 Excel 数据，字典在其中获取的值如下所示:

用于填充字典(工作)的代码如下所示:

Option Explicit

Const OrdersDBShtName               As String = "Orders_DB"
Public OrdersDBSht                  As Worksheet
Public LastSORow                    As Long
Public PODict                       As Object ' Public Dictionay for PO#, and keep SO per PO# (unique ID)

'======================================================================
Sub InitDict()

Dim AdminSht                        As Worksheet
Dim i As Long, j As Long

' set the sheet object where the "Orders_DB" data lies
On Error Resume Next
Set OrdersDBSht = ThisWorkbook.Worksheets(OrdersDBShtName)
On Error GoTo 0
If OrdersDBSht Is Nothing Then ' in case someone renamed the "Admin" Sheet
    MsgBox Chr(34) & OrdersDBShtName & Chr(34) & " Sheet has been renamed, please modify it", vbCritical
    End
End If

With OrdersDBSht
    LastSORow = .Cells(.Rows.Count, "B").End(xlUp).Row ' get last row with data in column "B" ("SO#")

    ' get all SO numbers in Dictionary (SO# is unique ID)
    Set SODict = CreateObject("Scripting.Dictionary")

    ' get all PO's in Dictionary (PO# is unique, but there can be several SO# per PO#)
    Set PODict = CreateObject("Scripting.Dictionary")
    Dim ID As Variant, Names As String

    ' add unique Category values to Dictionary object , and save Item Names in Names
    For i = 2 To LastSORow
        If Not PODict.Exists(.Range("A" & i).Value) Then
            ID = .Range("A" & i).Value

            For j = 2 To LastSORow
                If .Range("A" & j).Value = ID Then
                    If Names = "" Then
                        Names = .Range("B" & j).Value  ' get the SO#
                    Else
                        Names = Names & "," & .Range("B" & j).Value  ' get the SO#
                    End If
                End If
            Next j
            PODict.Add ID, Names
        End If
        ID = Empty
        Names = Empty
    Next i

    ' section below for DEBUG Only (works)
    Dim Key As Variant
    For Each Key In PODict.keys
       Debug.Print Key & " | " & PODict(Key)
    Next Key
End With

End Sub

问题:我有一个带有 2 个 ListBox 的 User_Form。

1. ExistingPO_LB - “PO”的 ListBox，读取 Dictionary 中的所有唯一 Key对象。
2. ExistingSO_LB - “SO#”的 ListBox，应仅显示在中选择的 Key 的 Items 现有PO_LB。

在某些情况下(例如下面的屏幕截图)它可以工作:

在某些情况下(例如下面的屏幕截图)，它不会(即使项目已正确保存在PODict 字典对象):

User_Form代码

Private Sub EditSO_Btn_Click()

With Me.ExistingSO_LB
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            EditSONumer = .List(i)
            Exit For
        End If
    Next i
End With

If EditSONumer = 0 Then
    MsgBox "No SO was selected from the list", vbInformation
    Exit Sub
End If

Unload Me
AddEdit_Orders_Form.Show  ' call sub Edit Order (load Add Order with the SO# data requested)

End Sub

'=========================================================
Private Sub ExistingPO_LB_Click()
' ****** This is the Sub I guess I'm missing something ******

Dim i As Long
Dim POSelected  As Variant
Dim SOArr       As Variant

With Me.ExistingPO_LB
    For i = 0 To .ListCount - 1
        If .Selected(i) Then
            POSelected = .List(i)
            Exit For
        End If
    Next i
End With

' update the SO listbox with only relevant SO (from the selected PO)
SOArr = Split(PODict(POSelected), ",") '<=== PODict(POSelected) return empty ???
With Me.ExistingSO_LB
    .Clear ' clear the previous items        
    For i = LBound(SOArr) To UBound(SOArr)
        .AddItem SOArr(i)
    Next i
End With

End Sub

'=========================================================
Private Sub UserForm_Initialize()
' load all existing PO's from "Orders DB" sheet

Dim Key As Variant

' populate listbox with PO's
With Me.ExistingPO_LB
    For Each Key In PODict.keys
        .AddItem Key
    Next Key
End With

End Sub

Answer 1

数字键以数字形式输入，您将它们作为字符串获取。我建议您坚持一种字典约定。

Sub TestDict()
  Dim dict As New Dictionary
  dict.Add 1, "one"
  dict.Add "2", "two"

  Debug.Print dict("1")     ' Nothing
  Debug.Print dict(1)       ' one

  Debug.Print dict("2")    ' two
  Debug.Print dict(2)      ' Nothing
End Sub

解决方案

为你的字典选择一个惯例并坚持下去。在此应用程序中，我将采用始终将键转换为字符串的约定，在插入和获取时。对代码进行一些更改就可以实现它:

If Not PODict.Exists(CStr(Range("A" & i).Value) Then ' could use .Text also

PODict.Add CStr(ID), Names


SOArr = Split(PODict(CStr(POSelected)), ",") ' maybe not needed here, but to illustrate