複製陣列

你可以使用 = 運算子將 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