我之前在Python工作过,有一个列表字典非常顺利(即一个键对应一个东西列表)。我正努力在vba中实现同样的目标。假设我在Excel工作表中有以下数据:
Flanged_connections 6
Flanged_connections 8
Flanged_connections 10
Instrument Pressure
Instrument Temperature
Instrument Bridle
Instrument Others
Piping 1
Piping 2
Piping 3
现在我想读取数据并将其存储在键所在的字典中 Flanged_connections
, Instrument
和 Piping
并且值是第二列中的对应值。我希望数据看起来像这样:
'key' 'values':
'Flanged_connections' '[6 8 10]'
'Instrument' '["Pressure" "Temperature" "Bridle" "Others"]'
'Piping' '[1 2 3]'
然后能够通过做到获得列表 dict.Item("Piping")
与列表 [1 2 3]
作为结果。所以我开始考虑做类似的事情:
For Each row In inputRange.Rows
If Not equipmentDictionary.Exists(row.Cells(equipmentCol).Text) Then
equipmentDictionary.Add row.Cells(equipmentCol).Text, <INSERT NEW LIST>
Else
equipmentDictionary.Add row.Cells(equipmentCol).Text, <ADD TO EXISTING LIST>
End If
Next
这似乎有点乏味。有更好的方法吗?我尝试在vba中搜索使用数组,它似乎与java,c ++和python有点不同,有stuft之类的 redim preserve
和喜欢。这是在vba中使用数组的唯一方法吗?
我的解决方案
基于@varocarbas的评论,我创建了一个集合字典。这是我理解正在发生的事情的最简单方法,尽管它可能不是最有效的。其他解决方案也可能有效(未经我测试)。这是我建议的解决方案,它提供了正确的输出:
'/--------------------------------------\'
'| Sets up the dictionary for equipment |'
'\--------------------------------------/'
inputRowMin = 1
inputRowMax = 173
inputColMin = 1
inputColMax = 2
equipmentCol = 1
dimensionCol = 2
Set equipmentDictionary = CreateObject("Scripting.Dictionary")
Set inputSheet = Application.Sheets(inputSheetName)
Set inputRange = Range(Cells(inputRowMin, inputColMin), Cells(inputRowMax, inputColMax))
Set equipmentCollection = New Collection
For i = 1 To inputRange.Height
thisEquipment = inputRange(i, equipmentCol).Text
nextEquipment = inputRange(i + 1, equipmentCol).Text
thisDimension = inputRange(i, dimensionCol).Text
'The Strings are equal - add thisEquipment to collection and continue
If (StrComp(thisEquipment, nextEquipment, vbTextCompare) = 0) Then
equipmentCollection.Add thisDimension
'The Strings are not equal - add thisEquipment to collection and the collection to the dictionary
Else
equipmentCollection.Add thisDimension
equipmentDictionary.Add thisEquipment, equipmentCollection
Set equipmentCollection = New Collection
End If
Next
'Check input
Dim tmpCollection As Collection
For Each key In equipmentDictionary.Keys
Debug.Print "--------------" & key & "---------------"
Set tmpCollection = equipmentDictionary.Item(key)
For i = 1 To tmpCollection.Count
Debug.Print tmpCollection.Item(i)
Next
Next
请注意,此解决方案假定所有设备都已排序!