Excel vba - 访问范围的元素
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9403178/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel vba - access element of a range
提问by bsr
new to vba, and wonder how to address elements within a range. I add range of cells (rows) indexed with an id to a dictionary
vba 新手，想知道如何处理范围内的元素。我将用 id 索引的单元格（行）范围添加到字典中
Set spRange = import.Range("A2:" & spRange.End(xlDown).Address)
For Each cell In spRange
dict.Add cell.Offset(0, 2).Text, cell.Row
Next cell
later on, I retrieve the row, and need to access value of first element.
稍后，我检索该行，并需要访问第一个元素的值。
For Each x In dict
Set spRange = dict.Item(x)
'how to get value of first element of spRange
Next
It could be very easy for u, but i am not familiar with api :-)
对你来说可能很容易，但我不熟悉 api :-)
thanks
谢谢
回答by Siddharth Rout
bsreekanth
斯里坎特
The Dictionary object, if I am not wrong first came out way back in 1996 as part of VB Script 2 and was later added to the VB Scripting run-time library (scrrun.dll). To work with the Dictionary object, you have to add a reference to Microsoft Scripting Runtime.
如果我没记错的话，Dictionary 对象最早是在 1996 年作为 VB Script 2 的一部分出现的，后来被添加到 VB Scripting 运行时库 (scrrun.dll) 中。要使用 Dictionary 对象，您必须添加对 Microsoft Scripting Runtime 的引用。
The syntax of adding item to dictionary is
将项目添加到字典的语法是
DictObject.Add **<Unique key>**,Value
The key has to be unique else you will get an error. Let's cover different scenarios to understand how it works.
密钥必须是唯一的，否则您将收到错误消息。让我们涵盖不同的场景以了解它是如何工作的。
Let's take an example
让我们举个例子
We store the row (and not the cell text) and then retrieve the row number
我们存储行（而不是单元格文本），然后检索行号
Sub Sample()
Dim spRange As Range
Dim Dict As Dictionary
Dim j as long
Set spRange = Range("A1:A10")
Set Dict = New Dictionary
j = 1
For Each cell In spRange
Dict.Add j, cell.Row
j = j + 1
Next cell
Dim x As Variant
For Each x In Dict
Debug.Print Dict(x)
Next
End Sub
If you noticed that I am using the variable "j" to create unique keys and then storing the row values.
如果您注意到我使用变量“j”来创建唯一键，然后存储行值。
To retrieve the stored row values, I am then looping through the Dictionary Objects.
为了检索存储的行值，我将遍历字典对象。
Now back to your question.
现在回到你的问题。
later on, I retrieve the row, and need to access value of first element.
稍后，我检索该行，并需要访问第一个元素的值。
This is the part where I am kind of confused. Reason being, if you wanted just to get the value of a particular row in range spRangethen why are you using a dictionary object?
这是我有点困惑的部分。原因是，如果您只想获取范围spRange 中特定行的值，那么为什么要使用字典对象？
You can directly get the value using this code
您可以使用此代码直接获取值
Debug.print spRange.Cells(1, 1).Value
If you still would like to use a dictionary object then you can use the below code
如果你仍然想使用字典对象，那么你可以使用下面的代码
Sub Sample()
Dim spRange As Range
Dim Dict As Dictionary
Set spRange = Range("A1:A3")
Set Dict = New Dictionary
j = 1
For Each cell In spRange
Dict.Add j, cell.Row
j = j + 1
Next cell
Dim x As Variant
For Each x In Dict
Debug.Print spRange.Cells(Dict(x), 1).Value
Next
End Sub
And If your intention is to store the range values in the dictionary and then retrieve the value based on a particular key (Row Number)then you can use this code
如果您打算将范围值存储在字典中，然后根据特定键（行号）检索值，那么您可以使用此代码
Sub Sample()
Dim spRange As Range
Dim Dict As Dictionary
Set spRange = Range("A1:A3")
Set Dict = New Dictionary
For Each cell In spRange
'~~> See how I reversed it?
Dict.Add cell.Row, cell.Text
Next cell
Dim x As Variant
For Each x In Dict
Debug.Print Dict(x)
Next
'OR
'Debug.Print Dict(2)
End Sub
Now one last point. If you are not going to loop through the Dictionary object to retrieve the values but are planning to use something like "Debug.Print Dict(2)" then I would suggest using an extra piece of code which first checks if the element is present or not and then shows it. For example
现在最后一点。如果您不打算遍历 Dictionary 对象来检索值，而是打算使用“Debug.Print Dict(2)”之类的东西，那么我建议使用额外的一段代码来首先检查该元素是否存在或不，然后显示它。例如
If Dict.Exists(2) Then Debug.Print Dict(2)
HTH
HTH
Let me know if you have any questions.
如果您有任何问题，请告诉我。
Sid
锡德
