Excel VBA 如何使用来自多个 excel 范围的值填充多维 (3d) 数组？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23880740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA How to populate a multi-dimensional (3d) array with values from multiple excel ranges?
提问by Chris
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 数组。我希望我只是不知道语法，但可能无法像使用 2D 数组那样“批量”为 3D 数组赋值。如...
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...etc 是具有交叉表 Z 因子的 6 个命名范围。都是 15 行（压力）乘 9 列（温度）。最后一行被注释掉了，因为 VBA 不喜欢这种语法，但也许你可以在这里看到我想要做什么。我正在尝试将数据以 15x9 的块分配给 3Darray。tblZFactorSG1 包含应该在 ZTables(1, 1 to 15, 1 to 9) 中的值。tblZFactorSG2 包含应该在 ZTables(2, 1 to 15, 1 to 9) 中的值。
Thanks in advance for any help.
在此先感谢您的帮助。
回答by Tim Williams
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
