arrays - Excel VBA 将范围值复制到数组，

Question

我有以下代码摘录，我试图将一系列值复制到声明的数组中，但它一直给我“无法分配给数组”错误，

Dim permittedCurve(0 To 7000) As Variant

permittedCurve = activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value

我也试过这个，但是给了我同样的错误..

Dim permittedCurve(7000) As Variant

permittedCurve = application.transpose(activeWorkbook.Worksheets("Origin").Range("AB6:AB7006").value)

有人可以帮忙吗？我真的没有看到这两种方法有什么问题。 :-(

=============================

已更新

我试过以下，

Dim permittedCurve(4) As Variant
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve

这仍然会产生相同的“无法分配给数组”错误...为什么？

Answer 1

当您从工作表的单元格中批量加载时，您总是得到一个二维数组。第一等级可以被认为是“行”，第二等级可以被认为是“列”。

dim permittedCurve As Variant
'the following is the same as redim permittedCurve(1 to 3, 1 to 6)
permittedCurve = Range("A1:F3").Value2
debug.print lbound(permittedCurve, 1) & ":" & ubound(permittedCurve, 1)
debug.print lbound(permittedCurve, 2) & ":" & ubound(permittedCurve, 2)
'results from the Immediate window:
1:3
1:6

鉴于使用 native 的问题(和开销)TRANSPOSE function ，如果您打算整体从工作表中来回铲取值，请坚持使用二维数组。

更新后的问题可以通过更改变量声明来解决。

Dim permittedCurve As Variant  '<~~ just a variant, not specifically a variant array with 5 elements
Dim indicationCurve(4) As Variant

indicationCurve(0) = 1
indicationCurve(1) = 10
indicationCurve(2) = 100
indicationCurve(3) = 1000
indicationCurve(4) = 10000

'Copying the curves
permittedCurve = indicationCurve
'now it's a variant array with 5 elements