灌装三维阵列中VBA二维数组 [英] Filling a 3D array with 2D arrays in VBA

问题描述

我有一组数据，其中每个项具有对应于它的信息的2D阵列。我想创建一个三维阵列，其中所述第一尺寸为项目名称和在第二和第三尺寸由对应于该项目的信息处理。

I have a set of data where each item has a 2D array of information corresponding to it. I'd like to create a 3D array where the first dimension is the item name and the second and third dimensions are taken up by the info corresponding to the item.

我可以阅读每个项目的数据转换成二维数组，但我无法弄清楚如何获得二维数组读​​入3D阵列。

I can read the data for each item into a 2D array, but I can't figure out how to get the 2D array read into the 3D array.

我知道所有的尺寸的大小，所以我开始阅读和写作过程中我才可以创建固定大小的数组。

I know the sizes of all the dimensions so I can create an array of fixed size before I begin the reading and writing process.

我想通过仅在项目名称循环，并通过每一个二维数组的每一个细胞都不能循环做到这一点。

I'd like to do this by looping only through the names of the items and not looping through every cell of every 2D array.

这是很容易获得的二维数组到一个ArrayList阅读，但我希望能够命名的项目，并能在阅读这些回脱颖而出，似乎很难与一个ArrayList的事情。

It is easy to get the 2D arrays read in to an ArrayList but I want to be able to name the items and be able to read these back in to excel and it seems difficult to do with an ArrayList.

现在的问题是：如何做，我读了Excel中选择2D到3D的VBA固定大小的数组

The question is: how do I read a 2D selection from excel into a 3D fixed sized array in VBA?

推荐答案

下面是每一种方法的一个例子：数组或词典数组的数组。在词典的方法是相当容易比数组的数组，如果你想要的是价值观的键控查找。有可能是值得到在其他情况下阵列的阵列

Here is an example of each approach: array of arrays or Dictionary of arrays. The Dictionary approach is considerably easier than the array of arrays if what you want is keyed lookup of values. There might be merits to the array of arrays in other cases.

这是假人code，没有真正的目的，而是要说明几件事情：抓住一个值和值的数组。我被抓住一些范围数据这是很容易和2D构建值的二维数组。从那里我建立这些值的数组，然后把它们放到相关的数据结构。然后，我在数据结构捅得到一些价值出来。

This is dummy code with no real purpose but to show a couple things: grabbing a single value and an array of values. I am building a 2D array of values by grabbing some Range data which is easy and 2D. From there I build up the arrays of these values and then put them into the relevant data structure. Then I poke at the data structure to get some values out of it.

阵列方式的阵列首先显示（和图片的左侧输出）。

Array of Arrays approach is shown first (and outputs on the left of the picture).

Sub ArraysOfArrays()

    Dim arrA() As Variant
    Dim arrB() As Variant

    'wire up a 2-D array
    arrA = Range("B2:D4").Value
    arrB = Range("F2:H4").Value

    Dim arrCombo() As Variant
    ReDim arrCombo(2, 1) As Variant

    'name and give data
    arrCombo(0, 0) = "arrA"
    arrCombo(1, 0) = arrA

    'add more elements
    ReDim Preserve arrCombo(2, 2)

    arrCombo(0, 1) = "arrB"
    arrCombo(1, 1) = arrB

    'output a single result
    'cell(2,2) of arrA
    Range("B6") = arrCombo(1, 0)(2, 2)

    Dim str_search As String
    str_search = "arrB"

    'iterate through and output arrB to cells
    Dim i As Integer
    For i = LBound(arrCombo, 1) To UBound(arrCombo, 1)
        If arrCombo(0, i) = str_search Then
            Range("B8").Resize(3, 3).Value = arrCombo(1, i)
        End If
    Next i
End Sub

下面夫妇关键点：

• 您只能使用扩展使用ReDim 的数组。 使用ReDim 是非常特别的，你只能改变数组的最后一维与 preserve 使用时。因为我需要他们中的一个跟踪条目的数量，我这样做，在这...不自然的第二个索引。如果你事先知道的大小，这痛苦的一步被跳过。


• 我的最后一个阵列是一个2×N个数组，其中2包含名称和数据的YXZ数组。


• 在为了寻找在混合给定的数组，你必须通过他们都进行迭代。

• You can only expand the array using ReDim. ReDim is very particular that you only change the last dimension of the array when used with Preserve. Since I need one of them to track the number of entries, I do that in the second index which is... unnatural. If you know the size in advance, this painful step is skipped.
• My final array is a 2xN array where the 2 contains a name and a YxZ array of data.
• In order to find a given array in the mix, you have to iterate through them all.

阵列的字典远小于code和更优雅。一定要添加引用工具方式＆gt;参考在VBA编辑器Microsoft脚本运行时

Dictionary of Arrays is far less code and more elegant. Be sure to add the reference Tools->References in the VBA editor to Microsoft Scripting Runtime.

Sub DictionaryOfArrays()

    Dim dict As New Scripting.Dictionary

    'wire up a 2-D array
    arrA = Range("B2:D4").Value
    arrB = Range("F2:H4").Value

    dict.Add "arrA", arrA
    dict.Add "arrB", arrB

    'get a single value
    Range("F6") = dict("arrB")(2, 2)

    'get a array of values
    Range("F8").Resize(3, 3) = dict("arrA")

End Sub

输入数据和结果的图片

数据，如果你想将它复制（粘贴在 B1 ）

Data to copy if you want it (paste in B1)

a               b       
1   2   3       10  11  12
4   5   6       13  14  15
7   8   9       16  17  18

这篇关于灌装三维阵列中VBA二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！