字典不显示某些键的项目（数值） [英] Dictionary doesn't display Items for certain Key (Numeric value)

问题描述

这是一个长的，但留在我 ...

我有一个字典将PO保存为键和SO作为项目（可能有一些PO具有多个SO的情况。 p>



我的Excel数据在工作表中，其中Dictionary获取的值如下所示：

用于填充字典（工作）的代码如下所示： p>

  Option Explicit 
 
 Const OrdersDBShtName As String =Orders_DB
 Public OrdersDBSht As Worksheet 
公共LastSORow长期
公共PODict作为对象的PO＃的公共字典，并保持SO每PO＃（唯一ID）
 
'======== =========================================== ================= 
 Sub InitDict（）
 
 Dim AdminSht As Worksheet 
 Dim i As Long，j As Long 
 
'设置Orders_DB数据所在的工作表对象
错误恢复下一步
设置OrdersDBSht = ThisWorkbook.Worksheets（OrdersDBShtName）
错误GoTo 0 
如果OrdersDBSht不是，那么'以防万一有人重命名管理员表
 MsgBox Chr（34）& OrdersDBShtName& Chr（34）& sheet已重命名，请修改它，vbCritical 
 End 
 End If 
 
 With OrdersDBSht 
 LastSORow = .Cells（.Rows.Count，B ）.End（xlUp）.Row'得到列B（SO＃）中的数据的最后一行
 
'获取字典中所有SO数字（SO＃是唯一ID）
 Set SODict = CreateObject（Scripting.Dictionary）
 
'获取字典中的所有PO（PO＃是唯一的，但每个PO＃可以有多个SO＃）
设置PODict = CreateObject（Scripting.Dictionary）
 Dim ID As Variant，As As String 
 
'将唯一的类别值添加到Dictionary对象，并将名称保存在名称
对于i = 2 To LastSorow 
如果不是PODict.Exists（.Range（A& i）.Value）然后
 ID = .Range（A& i）.Value 
 
对于j = 2到LastSORow 
如果.Range（A& j）.Value = ID然后
如果Names =然后
 Names = .Range（B & j）.Value'得到SO＃
 Else 
 Names = Names& ，& .Range（B& j）.Value'get the SO＃
 End If 
 End If 
 Next j 
 PODict.Add ID，Names 
 End如果
 ID =空
名称=空
下一个i 
 
'部分下面的DEBUG只（作品）
 Dim Key As Variant 
 PODict.keys中的每个密钥
 Debug.Print Key& |& PODict（Key）
下一个键
结束
 
 End Sub 
  

问题：我有一个 User_Form 与2 ListBox es。

1. 现有的PO_LB - 一个 ListBox 为PO，读取字典对象中的所有唯一键。


2. ExistingSO_LB - SO＃的 ListBox ，应仅显示 ExistingPO_LB 中选择的键项目。

在某些情况下（如下面的屏幕截图），它有效：

在某些情况下（如下面的屏幕截图），不（即使项目已正确保存在 PODict 字典 object）：


User_Form 代码

  Private Sub EditSO_Btn_Click（）
 
 With Me.ExistingSO_LB 
对于i = 0 To .ListCount  -  1 
如果.Selected（i）然后
 EditSONumer = .List（i）
退出
结束如果
下一个我
结束
 
如果是EditSONum er = 0然后
 MsgBox从列表中没有选择SO，vbInformation 
退出Sub 
如果
 
卸载我
 AddEdit_Orders_Form.Show'调用子编辑订单（加载添加订单与SO＃数据请求）
 
结束Sub 
 
'============== 
私有子现有PO_LB_Click（） 
'******这是Sub我想我错过了一些东西****** 
 
 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）
退出
结束如果
下一个i 
结束
 
'只更新SO列表框，只有相关的SO（从所选的PO） 
 SOArr = Split（PODict（POSelected），，）'< === PODict（POSelected）返回空？ 
与Me.ExistingSO_LB 
 .Clear'清除以前的项目
对于i = LBound（SOArr）到UBound（SOArr）
 .AddItem SOArr（i）
下一步i 
结束
 
 End Sub 
 
'====================== 
 Private Sub UserForm_Initialize（）
'加载所有现有的PO从订单DB表
 
 Dim Key As Variant 
 
'填充列表框与PO的
与Me.ExistingPO_LB 
对于PODict中的每个键。键
 .AddItem键
下一键
结束
 
结束子
  

解决方案

数字键以数字输入，您将其作为字符串读取。我建议你为你的字典坚持一个惯例

  Sub TestDict（）
 Dim dict as New Dictionary 
 dict.Add 1，one
 dict.Add2，two
 
 Debug.Print dict（1）'没有
 Debug.Print dict（1）'one 
 
 Debug.Print dict（2）'two 
 Debug.Print dict（2）'Nothing 
 End Sub 
  

解决方案

选择你的字典的惯例并坚持下去。在这个应用程序中，我将遵循在插入和抓取时始终将我的键转换为字符串的惯例。您的代码中的一些更改可以实现：

 如果不是PODict.Exists（CStr（Range（A& i ）.Value）然后'可以使用.Text还
 
 PODict.Add CStr（ID），名称
 
 
 SOArr =拆分（PODict（CStr（POSelected） ），）可能不需要这里，但是要说明
  

This is a Long one, but stay with me...

I have a Dictionary that saves "PO" as Key and "SO" as Items (there can be cases that a certain "PO" has multiple "SO".

My Excel data in a worksheet, where the Dictionary get's his values looks like this:

The code for populating the Dictionary (working) looks like this:

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

The Problem: I have a User_Form with 2 ListBoxes.

1. ExistingPO_LB - a ListBox for "PO"s, reads all the Unique Keys in the Dictionary object.
2. ExistingSO_LB - a ListBox for "SO#", should show only Items for the Key selected in ExistingPO_LB.

In some cases (like the screen-shot below) it works:

In some cases (like the screen-shot below) it doesn't (even though the Items have been saved correctly in PODict Dictionary object):

User_Form Code

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

解决方案

The numeric keys were entered as numbers and you are fetching them as strings. I suggest that you stick to one convention for your dictionary.

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

Solution

Chose a convention for your dictionary and stick to it. In this application I would take the convention of always converting my keys to strings, both when inserting and when fetching. A few changes in your code can achieve it:

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

这篇关于字典不显示某些键的项目（数值）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！