VBA将3维阵列粘贴成片 [英] VBA pasting 3 dimensional array into sheet

问题描述

我有一个3维数组（5 x 5 x 3），我需要将Sheet1（5 x 5 x 2）贴到Sheet2（5 x 5 x 3）到Sheet3（5 x 5 x 1） 。因为我正在3个嵌套for循环中构建这个3维数组，所以我不能使用for循环来访问循环的（5 x 5）部分。有没有任何标识符可以通过使用（1：end，1：end，1）来指示excel来索引数组的所有元素，例如MatLab中的所有元素？基本代码如下：

  Sub practice_2（）
 
 Dim arr（1 To 5，1 To To 5，1到3）
 Dim a As Integer 
 Dim x As Integer 
 Dim y As Integer 
 
 For a = 1 To 3 
 For x = 1到5 
对于y = 1到5 
 arr（x，y，a）= x * y 
下一个
下一个
 
表格a）。选择
'以下行是我要访问的（5 x 5 x 1）数组
范围（单元格（1，1），单元格（5，5））= arr 
下一个
 End Sub 
  

解决方案

你可以直接在Excel中使用3-D数组。但是，VBA变体非常灵活。您可以通过使用包含2-D数组而不是3-D数组的1-D数组来获得所需内容：

  Dim arr（1到3）
 
 Dim a As Integer 
 Dim x As Integer 
 Dim y As Integer 
 
 For a = 1 To 3 
 ReDim内部（1到5，1到5）
 
'不用担心...复制
 arr（a）= inner 
 
对于x = 1到5 
对于y = 1到5 
 arr（a）（x，y）= a * x * y 
下一个
下一个
 
表（a）。选择
 
范围（单元格（1，1），单元格（5，5））= arr（a）
下一个
  

（在回答您关于数组语法的具体问题时，答案是否。）

I have a 3 dimensional array (5 x 5 x 3) and I need to post (5 x 5 x 1) to Sheet1, (5 x 5 x 2) to Sheet2, (5 x 5 x 3) to Sheet3. Because I am building this 3 dimensional array inside 3 nested for loops, I cannot use a for loop to access the (5 x 5) part of the loop. Is there any identifier that tells excel to index all elements of an array, such as in MatLab by using (1:end, 1:end, 1)? Basic code is below:

Sub practice_2()

Dim arr(1 To 5, 1 To 5, 1 To 3)
Dim a As Integer
Dim x As Integer
Dim y As Integer

    For a = 1 To 3
        For x = 1 To 5
            For y = 1 To 5
                arr(x, y, a) = x * y
            Next
        Next

        Sheets(a).Select
        'Following line is where I want to access the (5 x 5 x 1) array
        Range(Cells(1, 1), Cells(5, 5)) = arr
    Next
End Sub 

解决方案

There's not much you can do directly with a 3-D array in Excel. However, VBA Variants are pretty flexible. You could get what you want by using a 1-D array that contains 2-D arrays instead of a 3-D array:

Dim arr(1 To 3)

Dim a As Integer
Dim x As Integer
Dim y As Integer

For a = 1 To 3
    ReDim inner(1 To 5, 1 To 5)

    'don't worry...makes a copy
    arr(a) = inner

    For x = 1 To 5
        For y = 1 To 5
            arr(a)(x, y) = a * x * y
        Next
    Next

    Sheets(a).Select

    Range(Cells(1, 1), Cells(5, 5)) = arr(a)
Next

(In answer to your specific question about array syntax, the answer is "no".)

这篇关于VBA将3维阵列粘贴成片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！