Excel VBA如何使用多个excel范围中的值填充多维（3d）数组？

I'm trying to populate a 3d array from data stored in an Excel worksheet. I'm hoping I just don't know the syntax, but it may not be possible to 'batch' assign values to 3D arrays like you can with 2D arrays. Such as...

我正在尝试从存储在Excel工作表中的数据填充3d数组。我希望我只是不知道语法,但可能无法“批量”将值分配给3D数组,就像使用2D数组一样。如...

Dim 2DArray() As Variant
2DArray = Range(A1:C10)

This is so clean and efficient I was hoping for something equivalent, as opposed to using 3 nested for-next loops to populate the data one value at a time.

这是如此干净和高效我希望有一些等价的东西,而不是使用3个嵌套的for-next循环来一次填充数据一个值。

For background this is to lookup/interpolate a gas compressibility factor Z, which is dependent on specific gravity, pressure and temperature. I have 6 tables (really just named ranges) for 6 different values of specific gravity. Each table has pressure and temperature cross-tabulated with Z factor as the datapoint. Here is the code I have (which doesn't work):

对于背景,这是查找/插入气体压缩系数Z,其取决于比重,压力和温度。我有6个不同的比重值的6个表(实际上只是命名范围)。每个表的压力和温度交叉列表,Z因子作为数据点。这是我的代码(不起作用):

Sub Array3DTest()
    Dim ZTables() As Variant
    ReDim ZTables(1 to 6, 1 to 15, 1 to 9) 
    '6 specific gravity tables, '15 pressure indices, 9 temp indices

    'Here is my feeble attempt to guess the syntax...
    'ZTables = ("tblZFactorSG1", "tblZFactorSG2", "tblZFactorSG3",
    '              "tblZFactorSG4", "tblZFactorSG5", "tblZFactorSG6")

End Sub

To clarify, tblZFactorSG1...SG2...SG3...etc are the 6 named ranges with the cross-tabbed Z factors. All are 15 rows (pressure) by 9 columns (temperature). The last line is commented out because VBA doesn't like the syntax, but maybe you can see what I'm trying to do here. I'm trying to assign the data to the 3Darray in chunks of 15x9. tblZFactorSG1 contains the values that should go in ZTables(1, 1 to 15, 1 to 9). tblZFactorSG2 contains the values that should go in ZTables(2, 1 to 15, 1 to 9).

为了澄清,tblZFactorSG1 ... SG2 ... SG3 ...等是具有交叉选项Z因子的6个命名范围。全部是15行(压力)乘9列(温度)。最后一行被注释掉了,因为VBA不喜欢语法,但也许你可以看到我在这里要做的事情。我正在尝试将数据分配给15x9的块中的3Darray。 tblZFactorSG1包含应该在ZTables中的值(1,1到15,1到9)。 tblZFactorSG2包含应该在ZTables中的值(2,1到15,1到9)。

Thanks in advance for any help.

在此先感谢您的帮助。

1 个解决方案

#1

Sub ArrayOfArrays()

    Dim ZTables(1 to 6) As Variant, x as Long, ranges

    ranges = Array("tblZFactorSG1", "tblZFactorSG2", "tblZFactorSG3", _
                 "tblZFactorSG4", "tblZFactorSG5", "tblZFactorSG6")

    For x=1 to 6
        ZTables(x) = ActiveSheet.Range(ranges(x-1)).Value  
    Next x

End Sub