Копировать массив vba excel

Example

You can copy a VBA array into an array of the same type using the = operator. The arrays must be of the same type otherwise the code will throw a «Can’t assign to array» compilation error.

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

The source array can be fixed or dynamic, but the destination array must be dynamic. Trying to copy to a fixed array will throw a «Can’t assign to array» compilation error. Any preexisting data in the receiving array is lost and its bounds and dimenions are changed to the same as the source array.

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)

Once the copy is made the two arrays are seperate in memory, i.e. the two variables are not references to same underlying data, so changes made to one array do not appear in the other.

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

Copying Arrays of Objects

With arrays of objects the references to those objects are copied, not the objects themselves. If a change is made to an object in one array it will also appear to be changed in the other array — they are both referencing the same object. However, setting an element to a different object in one array won’t set it to that object the other array.

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

Variants Containing an Array

You can also copy an array into and from a variant variable. When copying from a variant, it must contain an array of the same type as the receiving array otherwise it will throw a «Type mismatch» runtime error.

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

Копирование массивов

Вы можете скопировать массив VBA в массив того же типа, используя оператор = . Массивы должны быть одного типа, иначе код будет генерировать ошибку компиляции «Can not assign to array».

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

Исходный массив может быть фиксированным или динамическим, но целевой массив должен быть динамическим. Попытка скопировать в фиксированный массив вызовет ошибку компиляции «Can not assign to array». Любые существующие данные в принимающем массиве теряются, а его границы и размеры изменяются так же, как исходный массив.

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

Возвращаемые массивы из функций

Функция в нормальном модуле (но не модуле класса) может возвращать массив, помещая () после типа данных.

Function arrayOfPiDigits() As Long()
    Dim outputArray(0 To 2) As Long
    
    outputArray(0) = 3
    outputArray(1) = 1
    outputArray(2) = 4

    arrayOfPiDigits = outputArray
End Function

Результат функции можно затем поместить в динамический массив того же типа или варианта. К элементам также можно получить доступ, используя второй набор скобок, однако это вызовет функцию каждый раз, поэтому лучше сохранить результаты в новом массиве, если вы планируете использовать их более одного раза

Sub arrayExample()

    Dim destination() As Long
    Dim var As Variant
    
    destination = arrayOfPiDigits()
    var = arrayOfPiDigits
    
    Debug.Print destination(0)          ' outputs 3
    Debug.Print var(1)                  ' outputs 1
    Debug.Print arrayOfPiDigits()(2)    ' outputs 4
    
End Sub

Обратите внимание, что возвращаемое на самом деле является копией массива внутри функции, а не ссылкой. Поэтому, если функция возвращает содержимое массива Static, его данные не могут быть изменены процедурой вызова.

Вывод массива через выходной аргумент

Обычно это хорошая практика кодирования для аргументов процедуры как входных данных и для вывода через возвращаемое значение. Однако ограничения VBA иногда требуют, чтобы процедура выводила данные через аргумент ByRef .

Вывод в фиксированный массив

Sub threePiDigits(ByRef destination() As Long)
    destination(0) = 3
    destination(1) = 1
    destination(2) = 4
End Sub

Sub printPiDigits()
    Dim digits(0 To 2) As Long
    
    threePiDigits digits
    Debug.Print digits(0); digits(1); digits(2) ' outputs 3 1 4
End Sub

Вывод массива из метода класса

Выходной аргумент также может использоваться для вывода массива из метода / процедуры в модуле класса

' Class Module 'MathConstants'
Sub threePiDigits(ByRef destination() As Long)
    ReDim destination(0 To 2)
    
    destination(0) = 3
    destination(1) = 1
    destination(2) = 4
End Sub

' Standard Code Module
Sub printPiDigits()
    Dim digits() As Long
    Dim mathConsts As New MathConstants
    
    mathConsts.threePiDigits digits
    Debug.Print digits(0); digits(1); digits(2) ' outputs 3 1 4
End Sub

Передача массивов на прохождение

Массивы могут передаваться в процедуры, помещая () после имени переменной массива.

Function countElements(ByRef arr() As Double) As Long
    countElements = UBound(arr) - LBound(arr) + 1
End Function

Массивы должны передаваться по ссылке. Если не указан какой-либо передающий механизм, например myFunction(arr()) , то VBA будет считать ByRef по умолчанию, однако хорошая практика кодирования делает его явным. Попытка передать массив по значению, например myFunction(ByVal arr()) приведет к ошибке компиляции «Array argument должно быть ByRef» (или ошибке компиляции «Синтаксическая ошибка», если Auto Syntax Check не проверена в параметрах VBE) ,

Передача по ссылке означает, что любые изменения в массиве будут сохранены в процессе вызова.

Sub testArrayPassing()
    Dim source(0 To 1) As Long
    source(0) = 3
    source(1) = 1
    
    Debug.Print doubleAndSum(source)  ' outputs 8
    Debug.Print source(0); source(1)  ' outputs 6 2
End Sub

Function doubleAndSum(ByRef arr() As Long)
    arr(0) = arr(0) * 2
    arr(1) = arr(1) * 2
    doubleAndSum = arr(0) + arr(1)
End Function

Если вы хотите избежать изменения исходного массива, будьте осторожны, чтобы написать функцию, чтобы она не меняла никаких элементов.

Function doubleAndSum(ByRef arr() As Long)
    doubleAndSum = arr(0) * 2 + arr(1) * 2
End Function

Альтернативно создайте рабочую копию массива и работайте с копией.

Function doubleAndSum(ByRef arr() As Long)
    Dim copyOfArr() As Long
    copyOfArr = arr
    
    copyOfArr(0) = copyOfArr(0) * 2
    copyOfArr(1) = copyOfArr(1) * 2
    
    doubleAndSum = copyOfArr(0) + copyOfArr(1)
End Function

# Copying Arrays

You can copy a VBA array into an array of the same type using the = operator. The arrays must be of the same type otherwise the code will throw a «Can’t assign to array» compilation error.

The source array can be fixed or dynamic, but the destination array must be dynamic. Trying to copy to a fixed array will throw a «Can’t assign to array» compilation error. Any preexisting data in the receiving array is lost and its bounds and dimenions are changed to the same as the source array.

Once the copy is made the two arrays are seperate in memory, i.e. the two variables are not references to same underlying data, so changes made to one array do not appear in the other.

# Copying Arrays of Objects

With arrays of objects the references to those objects are copied, not the objects themselves. If a change is made to an object in one array it will also appear to be changed in the other array — they are both referencing the same object. However, setting an element to a different object in one array won’t set it to that object the other array.

# Variants Containing an Array

You can also copy an array into and from a variant variable. When copying from a variant, it must contain an array of the same type as the receiving array otherwise it will throw a «Type mismatch» runtime error.

# Returning Arrays from Functions

A function in a normal module (but not a Class module) can return an array by putting () after the data type.

The result of the function can then be put into a dynamic array of the same type or a variant. The elements can also be accessed directly by using a second set of brackets, however this will call the function each time, so its best to store the results in a new array if you plan to use them more than once

Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can’t be changed by the calling procedure.

# Outputting an Array via an output argument

It is normally good coding practice for a procedure’s arguments to be inputs and to output via the return value. However, the limitations of VBA sometimes make it necessary for a procedure to output data via a ByRef argument.

# Outputting to a fixed array

# Outputting an Array from a Class method

An output argument can also be used to output an array from a method/proceedure in a Class module

# Passing Arrays to Proceedures

Arrays can be passed to proceedures by putting () after the name of the array variable.

Arrays must be passed by reference. If no passing mechanism is specified, e.g. myFunction(arr()), then VBA will assume ByRef by default, however it is good coding practice to make it explicit. Trying to pass an array by value, e.g. myFunction(ByVal arr()) will result in an «Array argument must be ByRef» compilation error (or a «Syntax error» compilation error if Auto Syntax Check is not checked in the VBE options).

Passing by reference means that any changes to the array will be preserved in the calling proceedure.

If you want to avoid changing the original array then be careful to write the function so that it doesn’t change any elements.

Alternatively create a working copy of the array and work with the copy.

Although you can use CopyMemory and FillMemory, I’d strongly advise that you never keep these references around for too long. As an example I made stdRefArray class based on this exact principle, DO NOT USE THIS CODE! Read on to find out why…:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "stdRefArray"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'I STRONGLY RECOMMEND AGAINST USING THIS CLASS. SEE WHY HERE:
'https://stackoverflow.com/a/63838676/6302131

'Status WIP
'High level wrapper around 2d array.

#Const DEBUG_PERF = False

'Variables for pData
Private Declare PtrSafe Sub FillMemory Lib "kernel32" Alias "RtlFillMemory" (Destination As Any, ByVal Length As Long, ByVal Fill As Byte)
Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpDest As Any, lpSource As Any, ByVal cbCopy As Long)


Public Data As Variant

Private Const VARIANT_SIZE As Long = 16

Public Function Create(ByRef Data As Variant) As stdRefArray
    Set Create = New stdRefArray
    Call Create.Init(Data)
End Function
Public Sub Init(ByRef DataIn As Variant)
    'Create direct reference to array:
    CopyMemory Data, DataIn, VARIANT_SIZE
End Sub

Private Sub Class_Terminate()
   'Clean up array reference
   FillMemory Data, VARIANT_SIZE, 0
End Sub

Public Function GetData(ByVal iRow as long, ByVal iCol as long) as Variant
  Attribute GetData.VB_UserMemID=0
  GetData = GetData(iRow,iCol)
End Function

My initial idea of using this class was to do something like the following:

Cars.FindCar(...).GetDoor(1).Color = Rgb(255,0,0)

where the Car class has a reference to the Cars array, and similarly with the Door class stores a reference to the Cars array, allowing for «instant» setters straight to the source of the initial data.

This works fine! But…

I came across massive issues while debugging. If you’re in debug mode, in the Door class, in the color setter, if you make a change to the structure which will need recompilation I.E. Change the name of a dimed variable, change the name of a method/property, or changed their types, Excel will instantly crash. A similar thing will occur when you click the VBA stop (square) button. Not only this, but it is extremely nasty to debug these instant crashes from Excel…

This makes the above code ensure the rest of your code base is also difficult to maintain. It will increase time to make fixes, cause a lot of frustration and make. The time saved in runtime doesn’t justify the time it’ll take to fix issues around it.

If you do ever make these array references ensure you keep their lives incredibly short, and adequately comment in between regarding debugging issues.

Note: If anyone can find a work around this crash issue (i.e. properly clean up the stack prior to VBA crash, I’d be very interested!)

Instead I highly suggest you use a simple class like this:

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "stdRefArray"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
'Status WIP
'High level wrapper around arrays
Public Event Changed(ByVal iRow As Long, ByVal iCol As Long, ByVal Value As Variant)
Public vData As Variant

Public Function Create(ByRef Data As Variant) As stdRefArray
    Set Create = New stdRefArray
    Call Create.Init(Data)
End Function
Public Sub Init(ByRef Data As Variant)
    'Slow, but a stable reference
    vData = Data
End Sub



Public Property Get Data(Optional ByVal iRow As Long = -1, Optional ByVal iCol As Long = -1) As Variant
Attribute Data.VB_UserMemId = 0
    If iRow = -1 And iCol = -1 Then
        CopyVariant Data, vData
    ElseIf iRow <> -1 And iCol <> -1 Then
        CopyVariant Data, vData(iRow, iCol)
    Else
        stdError.Raise "stdRefArray::Data() - Invalid use of Data", vbCritical
    End If
End Property
Public Property Let Data(ByVal iRow As Long, ByVal iCol As Long, Value As Variant)
    vData(iRow, iCol) = Value
    RaiseEvent Changed(iRow, iCol, Value)
End Property
Public Property Set Data(ByVal iRow As Long, ByVal iCol As Long, Value As Object)
    Set vData(iRow, iCol) = Value
    RaiseEvent Changed(iRow, iCol, Value)
End Property
Public Property Get BoundLower(ByVal iDimension As Long) As Long
    BoundLower = LBound(vData, iDimension)
End Property
Public Property Get BoundUpper(ByVal iDimension As Long) As Long
    BoundUpper = UBound(vData, iDimension)
End Property


Private Function CopyVariant(ByRef dest As Variant, ByVal src As Variant)
    If IsObject(src) Then
        Set dest = src
    Else
        dest = src
    End If
End Function

I’ve added a few extra steps which will help with bindings. You do still very much lose a lot of native behaviour, however this is the safest bet which is also the easiest to maintain. It will also be the fastest way to get collection-like functionality without using a collection.

Usage, Car.cls:

Private WithEvents pInventory as stdRefArray
Public Function Create(ByRef arrInventory as variant)
   Set Create = new Car
   Set Create.pInventory = stdRefArray.Create(arrInventory)
End Function
Public Function GetDoor(ByVal iRow as long) as Door
   Set GetDoor = new Door
   GetDoor.init(pInventory,iRow)
End Function

Door.cls

Private pArray as stdRefArray
Private pRow as long
Private Const iColorColumn = 10
Sub Init(ByVal array as stdRefArray, ByVal iRow as long)
    set pArray = array
    pRow = iRow
End Sub
Public Property Get Color() as long
    Color = pArray(pRow,iColorColumn)
End Property
Public Property Let Color(ByVal iNewColor as long)
    pArray(pRow,iColorColumn) = iNewColor
End Property

The example probably isn’t too great lol, but hopefully you get the idea.