在VBA中用2D阵列填充3D阵列 [英] Filling a 3D array with 2D arrays in VBA

问题描述

我有一组数据，其中每个项目都有一个对应于它的二维数组的信息。我想创建一个3D数组，其中第一个维度是项目名称，第二个和第三个维度由对应于该项目的信息占据。

I可以将每个项目的数据读入2D数组，但是我无法弄清楚如何将2D数组读入3D数组。

我知道大小在我开始阅读和写作之前，我可以创建一个固定大小的数组。

我想通过循环只通过名称的项目，而不是遍历每个2D数组的每个单元格。

很容易将2D数组读入ArrayList，但我想要命名这些项目并能够将它们读入excel，并且似乎难以与ArrayList进行对比。

问题是：如何从excel中读取2D选择在VBA中成为3D固定大小的数组？

解决方案

下面是每个方法的一个例子：array的数组或数组的字典。如果你想要的是键值查找值，那么 Dictionary方法比数组的数组要容易得多。在其他情况下，数组的数组可能是有优点的。

这是没有真正目的的虚拟代码，但是要显示一些事情：抓取单个值和数组的价值观。通过抓取一些简单和2D的 Range 数据，我正在构建一个2D数组。从那里我建立这些值的数组，然后将它们放入相关的数据结构中。然后我捅数据结构以获取一些值。

数组方法首先显示（并在左侧输出）的图片）

  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）值
 
 Dim arrCombo（）As Variant 
 ReDim arrCombo（2，1）As Variant 
 
'name and give data 
 arrCombo （0，0）=arrA
 arrCombo（1，0）= arrA 
 
'添加更多元素
 ReDim保存arrCombo（2，2）
 
 arrCombo（0，1）=arrB
 arrCombo（1，1）= arrB 
 
'输出单个结果
'单元格（2,2） arrA 
范围（B6）= arrCombo（1，0）（2，2）
 
 Dim str_search As String 
 str_search =arrB
 
'遍历并将arrB输出到单元格
 Dim i As In teger 
对于i = LBound（arrCombo，1）到UBound（arrCombo，1）
如果arrCombo（0，i）= str_search然后
范围（B8）。调整大小（3， 3）.Value = arrCombo（1，i）
 End If 
 Next i 
 End Sub 
  

这里的关键点：

• 您只能使用 ReDim 。 ReDim 非常特别，您只能更改数组的最后一个维度与 Preserve 一起使用时。由于我需要其中一个跟踪条目的数量，所以我在第二个索引中这样做是不自然的。如果你提前知道这个大小，那么这个痛苦的步骤将被跳过。


• 我的最后一个数组是一个2xN数组，其中2包含一个名字和一个YxZ数组。


• 为了在混合中找到一个给定的数组，你必须遍历它们。

数组字典远远不如代码和更优雅。确保在VBA编辑器中将引用 Tools->参考文献添加到Microsoft Scripting Runtime。

  Sub DictionaryOfArrays（）
 
 Dim dict As New Scripting.Dictionary 
 
'连线2-D数组
 arrA =范围（ B2：D4）值
 arrB =范围（F2：H4）值
 
 dict.AddarrA，arrA 
 dict.AddarrB ，arrB 
 
'获取单个值
范围（F6）= dict（arrB）（2，2）
 
'值
 Range（F8）。调整大小（3，3）= dict（arrA）
 
 End Sub 
  

输入数据和结果的图片

要复制的数据（粘贴在 B1 ）

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

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.

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.

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.

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.

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

Couple key points here:

• 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.

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

Picture of input data and results

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中用2D阵列填充3D阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！