字典不显示某些键的项目(数值) [英] Dictionary doesn't display Items for certain Key (Numeric value)
问题描述
这是一个长的,但留在我 ...
我有一个 我的Excel数据在工作表中,其中Dictionary获取的值如下所示: 用于填充 问题:我有一个 在某些情况下(如下面的屏幕截图),它有效: 在某些情况下(如下面的屏幕截图),不(即使 User_Form 代码 数字键以数字输入,您将其作为字符串读取。我建议你为你的字典坚持一个惯例 解决方案 选择你的字典的惯例并坚持下去。在这个应用程序中,我将遵循在插入和抓取时始终将我的键转换为字符串的惯例。您的代码中的一些更改可以实现: This is a Long one, but stay with me... I have a My Excel data in a worksheet, where the Dictionary get's his values looks like this: The code for populating the The Problem: I have a In some cases (like the screen-shot below) it works:
In some cases (like the screen-shot below) it doesn't (even though the User_Form Code
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. 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:
这篇关于字典不显示某些键的项目(数值)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!字典
将PO保存为
。 键
和SO作为项目(可能有一些PO具有多个SO的情况。 p>
字典
(工作)的代码如下所示: 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。
现有的PO_LB
- 一个 ListBox
为PO,读取字典
对象中的所有唯一键
。
ExistingSO_LB
- SO#的 ListBox
,应仅显示 ExistingPO_LB
中选择的键
项目
项目
已正确保存在 PODict
字典
object):
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) ),)可能不需要这里,但是要说明
Dictionary
that saves "PO" as Key
and "SO" as Items (there can be cases that a certain "PO" has multiple "SO".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
User_Form
with 2 ListBox
es.
ExistingPO_LB
- a ListBox
for "PO"s, reads all the Unique Key
s in the Dictionary
object.ExistingSO_LB
- a ListBox
for "SO#", should show only Items
for the Key
selected in ExistingPO_LB
. Items
have been saved correctly in PODict
Dictionary
object):
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
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