Excel VBA 如何使用来自多个 excel 范围的值填充多维 (3d) 数组? [英] Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?

问题描述

我正在尝试从 Excel 工作表中存储的数据填充 3d 数组.我希望我只是不知道语法，但可能无法像使用 2D 数组那样批量"为 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...

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

这是非常干净和高效的，我希望有等价的东西，而不是使用 3 个嵌套的 for-next 循环一次填充一个值.

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.

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

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):

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

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

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

在此先感谢您的帮助.

推荐答案

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

这篇关于Excel VBA 如何使用来自多个 excel 范围的值填充多维 (3d) 数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！