Excel VBA中如何填充与来自多值练成范围多维（三维）阵列？ [英] Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?

问题描述

我想填充从存储在Excel工作表数据的三维阵列。我希望我只是不知道语法，但它可能无法以批赋值为3D阵列像您可以用二维数组。如...

 暗淡2DArray（）为Variant
2DArray =范围（A1：C10）
 

这是如此干净，高效的我所期待的东西等同，而不是使用嵌套的3对，下一个循环，一次填充数据的一个值。

有关背景这是查找/内插的气体的COM pressibility因子Z，这是依赖于比重，pressure和温度。我有比重的6个不同的值6表（实际上只是命名范围）。每个表都有pressure和温度交叉列表以Z因子为数据点。这里是code我有（不工作）：

 子Array3DTest（）
    昏暗的ZTables（）为Variant
    使用ReDim ZTables（1至6个，1至15，1至9）
    6比重表，'15 pressure指数，9温度指数    这里是我的软弱试图猜测语法...
    'ZTables =（tblZFactorSG1，tblZFactorSG2，tblZFactorSG3，
    'tblZFactorSG4，tblZFactorSG5，tblZFactorSG6）结束小组
 

要澄清，tblZFactorSG1 ... ... SG2 SG3 ...等都是6命名范围的交叉标签ž因素。所有15个行（pressure）由9列（温度）。最后一行被注释掉，因为VBA不喜欢的语法，但也许你可以看到什么，我想在这里做。我想在指定的15x9大块数据到3Darray。 tblZFactorSG1包含应在ZTables（1，1至15，1至9）去的值。 tblZFactorSG2包含的值应在ZTables（2，1至15，1至9）去

感谢您事先的任何帮助。

解决方案

 子ArrayOfArrays（）    昏暗的ZTables（1至6）的变体，X作为长，范围    范围=阵列（tblZFactorSG1，tblZFactorSG2，tblZFactorSG3，_
                 tblZFactorSG4，tblZFactorSG5，tblZFactorSG6）    对于x = 1至6个
        ZTables（X）= ActiveSheet.Range（范围（X-1））。值
    下一个x结束小组
 

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)

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.

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

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

Thanks in advance for any help.

解决方案

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中如何填充与来自多值练成范围多维（三维）阵列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！