问题描述
我无法找到如何在VBA词典中存储和访问数组的方法.
I cannot find out how to store and access an array in a VBA dictionary.
我有一个包含多个项目的密钥,比如说苹果",其值为"3",数量为"5".
I have a key with multiple items, say "Apple" with value "3" and quantity "5".
单个项目不会有问题,即:dict("Apples")= 3
A single item would not be a problem, ie:dict("Apples") = 3
但是如何存储和检索多个值?假设我要添加"Apples,3,5",然后移至单元格C4并自动将Apple粘贴到此处,C5中为3,C6中为5.
But how do I store and retrieve multiple values?Lets say I want to add "Apples, 3, 5" and then move to cell C4 and automatically paste Apples there, 3 in C5 and 5 in C6.
到目前为止,我已经尝试过:
So far I've tried:
Dim last_row As Long
last_row = Cells(Rows.Count, 1).End(xlUp).Row
Dim last_col As Long
last_col = ActiveSheet.UsedRange.Columns.Count
Dim strVal As String
Dim Item(0 To 99) As String
Dim header As Range
Dim rng As Range
For Each rngCell In Range(Cells(1 + i, 1), Cells(last_row, 1))
i = i + 1
strVal = rngCell.Text
If Not dict.Exists(strVal) Then
n = 0
For Each headerCell In Range(Cells(i, 2), Cells(i, last_col))
n = n + 1
Item(n) = headerCell.Text
'MsgBox headerCell.Text
Next headerCell
dict(strVal) = Item
Else
MsgBox "already exists"
End If
Next rngCell
Dim Items(0 To 99) As String
sFruit = InputBox("Check value of key")
Items = dict(sFruit)
MsgBox "The value of " & sFruit & " is " & Items(2)
这是不起作用的最后一部分.我是否将Items声明为Variant或String或Object,还是将Item = Items(2)放在消息框上方并进行引用.我无法从字典中检索到的数组中提取任何类型的信息.
It's the last part that isn't working. Whether I declare Items as Variant or String or Object or even if I put Item = Items(2) above the message box and refer to that. I can't get extract any kind of information out of an array retrieved from the dictionary.
推荐答案
这里是一个如何将数组写入字典以及以后如何访问它们的示例.
Here is an example of how to write arrays into dictionaries and how to access them later.
Option Explicit
Public Sub DictArrayExample()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
'create an array
Dim Arr() As Variant
Arr = Array("Item1", "Item2", "Item3") 'Array Index 0 to 2
'write the array into dictionary
dict.Add "apples", Arr
'or directly into dictionary without variable
dict.Add "bananas", Array("banana1", "banana2")
'directly access array inside dictionary
Debug.Print dict("apples")(1) 'index 1 = Item2
'get the array from the dictionary into another variable
Dim RetrieveArr() As Variant
RetrieveArr = dict("apples")
'access array in that variable
Debug.Print Join(RetrieveArr, ", ")
Debug.Print RetrieveArr(0) 'index 0 = Item 1
End Sub
这篇关于在VBA字典中引用数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!