复制数组

你可以使用 = 运算符将 VBA 数组复制到相同类型的数组中。数组必须是相同的类型,否则代码将抛出无法分配给数组编译错误。

Dim source(0 to 2) As Long
Dim destinationLong() As Long
Dim destinationDouble() As Double

destinationLong = source     ' copies contents of source into destinationLong
destinationDouble = source   ' does not compile

源阵列可以是固定的或动态的,但目标阵列必须是动态的。尝试复制到固定数组将抛出无法分配给数组编译错误。接收数组中的任何预先存在的数据都将丢失,其边界和维数将更改为与源数组相同。

Dim source() As Long
ReDim source(0 To 2)

Dim fixed(0 To 2) As Long
Dim dynamic() As Long

fixed = source   ' does not compile
dynamic = source ' does compile

Dim dynamic2() As Long
ReDim dynamic2(0 to 6, 3 to 99) 

dynamic2 = source ' dynamic2 now has dimension (0 to 2)

复制完成后,两个数组在内存中是分开的,即两个变量不是对相同底层数据的引用,因此对一个数组所做的更改不会出现在另一个数组中。

Dim source(0 To 2) As Long
Dim destination() As Long

source(0) = 3
source(1) = 1
source(2) = 4

destination = source
destination(0) = 2

Debug.Print source(0); source(1); source(2)                ' outputs: 3 1 4
Debug.Print destination(0); destination(1); destination(2) ' outputs: 2 1 4

复制对象数组

对于对象数组,将复制对这些对象的引用,而不是对象本身。如果对一个数组中的对象进行了更改,则它也会在另一个数组中更改 - 它们都引用同一个对象。但是,将元素设置为一个数组中的另一个对象不会将该对象设置为另一个数组。

Dim source(0 To 2) As Range
Dim destination() As Range

Set source(0) = Range("A1"): source(0).Value = 3
Set source(1) = Range("A2"): source(1).Value = 1
Set source(2) = Range("A3"): source(2).Value = 4

destination = source

Set destination(0) = Range("A4")   'reference changed in destination but not source

destination(0).Value = 2           'affects an object only in destination
destination(1).Value = 5           'affects an object in both source and destination

Debug.Print source(0); source(1); source(2)                  ' outputs 3 5 4
Debug.Print destination(0); destination(1); destination(2)   ' outputs 2 5 4

包含数组的变体

你还可以在变量变量中复制数组。从变量复制时,它必须包含与接收数组相同类型的数组,否则将引发类型不匹配运行时错误。

Dim var As Variant
Dim source(0 To 2) As Range
Dim destination() As Range

var = source
destination = var

var = 5
destination = var  ' throws runtime error