在vba中创建列表字典 [英] Create dictionary of lists in vba

问题描述

我早些时候在Python中工作，其中有一个列表字典真的很顺利（即一个对应于一个列表的东西）。我正在努力在vba中实现同样的目的。说我在excel表中有以下数据：

  Flanged_connections 6 
 Flanged_connections 8 
 Flanged_connections 10 
仪器压力
仪器温度
仪器Bridle 
仪器其他
管道1 
管道2 
管道3 
  

现在我想读取数据并存储在一个字典中，键是 Flanged_connections ，乐器和管道，值是第二列中的值。我想要的数据如下所示：

 'key''values'：
 
'Flanged_connections '''
'仪器''[压力温度Bridle其他]'
'管道''[1 2 3]'
  

然后可以通过执行 dict.Item（Piping）与列表 [1 2 3] 作为结果。所以我开始考虑做一些事情：

 对于每一行在inputRange.Rows 
 
如果没有equipmentDictionary .Exists（row.Cells（equipmentCol）.Text）Then 
 equipmentDictionary.Add row.Cells（equipmentCol）.Text，& INSERT NEW LIST> 
 Else 
 equipmentDictionary.Add row.Cells（equipmentCol）.Text，<添加到现有列表> 
结束如果
 
下一个
  

这似乎有点乏味做。有更好的方法吗？我试图在vba中搜索使用数组，它似乎与java，c ++和python有点不同，像stuft像 redim preserve 等等。这是在vba中使用数组的唯一方法吗？

我的解决方案：

基于@varocarbas的评论我已经创建了一个集合的字典。这是我最理解的方式来理解发生了什么，尽管它可能不是最有效率的。其他解决方案也可能会工作（未经我测试）。这是我建议的解决方案，它提供了正确的输出：

 '/ ------------ -------------------------- \'
'|设置设备的字典|'
'\ ----------------------------------- --- /'
 
 inputRowMin = 1 
 inputRowMax = 173 
 inputColMin = 1 
 inputColMax = 2 
 equipmentCol = 1 
 dimensionCol = 2 
 
设置deviceDictionary = CreateObject（Scripting.Dictionary）
设置inputSheet = Application.Sheets（inputSheetName）
设置inputRange = Range（Cells（inputRowMin，inputColMin），Cells （inputRowMax，inputColMax））
设置equipmentCollection =新集合
 
对于i = 1要输入Range.Height 
 thisEquipment = inputRange（i，equipmentCol）.Text 
 nextEquipment = inputRange（i + 1，equipmentCol）.Text 
 thisDimension = inputRange（i，dimensionCol）.Text 
 
'字符串相等 - 将此设备添加到集合并继续
如果（StrComp（thisEquipment，nextEquipment，vbTextCompare）= 0）然后
 equipmentCollection.Add thisDimension 
'字符串不相等 - 将thisEquipment添加到收集和收藏到字典
 Else 
 equipmentCollection.Add thisDimension 
 equipmentDictionary.Add thisEquipment，equipmentCollection 
设置equipmentCollection =新集合
结束如果
 
下一个
 
'检查输入
 Dim tmpCollection作为集合
每个键在equipmentDictionary.Keys 
 
 Debug.Print---- ----------&关键& ---------------
设置tmpCollection = equipmentDictionary.Item（key）
对于i = 1 To tmpCollection.Count 
 Debug.Print tmpCollection 。项目（i）
下一个
 
下一个
  

请注意这个解决方案假设所有的设备都被排序！

解决方案

VBA中的数组或多或少与其他各种各样的特性： / p>

• 重新加载数组是可能的（尽管不是必需的）。


• 大多数数组属性（例如， Sheets 数组）是基于1的。虽然，由@TimWilliams正确指出，用户定义的数组实际上是基于0的。下面的数组定义了一个长度为11的字符串数组（10表示上位）。

除此之外，还有关于符号，你不应该找到任何问题来处理VBA数组。

  Dim stringArray（10）As String 
 stringArray（1）=first val
 stringArray（2）=second val
'等
  

关于您要求的内容，您可以在VBA中创建一个字典，并在其上列出一个列表（或VBA等价物： Collection ），这里有一个示例代码：

  Set dict = CreateObject（Scripting.Dictionary）
设置coll =新集合
 coll.Add（coll1）
 coll.Add（coll2）
 coll .Add（coll3）
如果不是dict.Exists（dict1）然后
 dict.Adddict1，coll 
如果
 
 Dim curVal As String：curVal = dict（dict1）（3）' - > coll3
 
设置dict = Nothing 
  

I have worked in Python earlier where it is really smooth to have a dictionary of lists (i.e. one key corresponds to a list of stuff). I am struggling to achieve the same in vba. Say I have the following data in an excel sheet:

Flanged_connections 6
Flanged_connections 8
Flanged_connections 10
Instrument  Pressure
Instrument  Temperature
Instrument  Bridle
Instrument  Others
Piping  1
Piping  2
Piping  3

Now I want to read the data and store it in a dictionary where the keys are Flanged_connections, Instrument and Piping and the values are the corresponding ones in the second column. I want the data to look like this:

'key' 'values':

'Flanged_connections' '[6 8 10]'
'Instrument' '["Pressure" "Temperature" "Bridle" "Others"]'
'Piping' '[1 2 3]'

and then being able to get the list by doing dict.Item("Piping") with the list [1 2 3] as the result. So I started thinking doing something like:

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

This seems a bit tedious to do. Is there a better approach to this? I tried searching for using arrays in vba and it seems a bit different than java, c++ and python, with stuft like redim preserve and the likes. Is this the only way to work with arrays in vba?

My solution:

Based on @varocarbas' comment I have created a dictionary of collections. This is the easiest way for my mind to comprehend what's going on, though it might not be the most efficient. The other solutions would probably work as well (not tested by me). This is my suggested solution and it provides the correct output:

'/--------------------------------------\'
'| 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

Note that this solution assumes that all the equipment are sorted!

解决方案

Arrays in VBA are more or less like everywhere else with various peculiarities:

• Redimensioning an array is possible (although not required).
• Most of the array properties (e.g., Sheets array in a Workbook) are 1-based. Although, as rightly pointed out by @TimWilliams, the user-defined arrays are actually 0-based. The array below defines a string array with a length of 11 (10 indicates the upper position).

Other than that and the peculiarities regarding notations, you shouldn't find any problem to deal with VBA arrays.

Dim stringArray(10) As String
stringArray(1) = "first val"
stringArray(2) = "second val"
'etc.

Regarding what you are requesting, you can create a dictionary in VBA and include a list on it (or the VBA equivalent: Collection), here you have a sample code:

Set dict = CreateObject("Scripting.Dictionary")
Set coll = New Collection
coll.Add ("coll1")
coll.Add ("coll2")
coll.Add ("coll3")
If Not dict.Exists("dict1") Then
    dict.Add "dict1", coll
End If

Dim curVal As String: curVal = dict("dict1")(3) '-> "coll3"

Set dict = Nothing 

这篇关于在vba中创建列表字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！