本文介绍了在VBA字典中引用数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我无法找到如何在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字典中引用数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 00:01
查看更多