# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  copy 1 array to another array

## matelot

Is there a function in VBA that will allow me to copy from 1 array to
another. I know that I can loop throught each element and assign 1 array to
the other. But I want to know if there's a faster way such array1 = array2.
Assume they both have the same size.

Thanks
M

----------


## Toppers

Hi,

This works if it helps (you cannot assign to an array):

Sub a()

Dim array1(10, 10), array2() As Variant

For i = 1 To 10
For j = 1 To 10
array1(i, j) = i ^ j
Next j
Next i

array2 = array1

For i = 1 To 10
For j = 1 To 10
Debug.Print array2(i, j)
Next j
Next i
End Sub

"matelot" wrote:

> Is there a function in VBA that will allow me to copy from 1 array to
> another. I know that I can loop throught each element and assign 1 array to
> the other. But I want to know if there's a faster way such array1 = array2.
> Assume they both have the same size.
>
> Thanks
> M

----------


## Tushar Mehta

With VB6 (XL2000 or later), use array1=array2, though the target array
cannot be dimensioned.

Option Explicit
Option Base 0

Sub testArrays()
Dim Arr1(2), Arr2()
Arr1(0) = 1: Arr1(1) = 2: Arr1(2) = 3
Arr2 = Arr1
MsgBox Arr2(2)
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <C02B73D2-CD00-4E4B-B906-6D1D5F5BC2ED@microsoft.com>,
matelot@discussions.microsoft.com says...
> Is there a function in VBA that will allow me to copy from 1 array to
> another. I know that I can loop throught each element and assign 1 array to
> the other. But I want to know if there's a faster way such array1 = array2.
> Assume they both have the same size.
>
> Thanks
> M
>

----------


## Peter T

Just to add, for all versions incl. XL97 declare the target variable as a
non-array variant. In Tushar's demo change -

>     Dim Arr1(2), Arr2()
to
Dim Arr1(2), Arr2

Regards,
Peter T


"Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1e25a154fafa4aa898b2b7@msnews.microsoft.com...
> With VB6 (XL2000 or later), use array1=array2, though the target array
> cannot be dimensioned.
>
> Option Explicit
> Option Base 0
>
> Sub testArrays()
>     Dim Arr1(2), Arr2()
>     Arr1(0) = 1: Arr1(1) = 2: Arr1(2) = 3
>     Arr2 = Arr1
>     MsgBox Arr2(2)
>     End Sub
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <C02B73D2-CD00-4E4B-B906-6D1D5F5BC2ED@microsoft.com>,
> matelot@discussions.microsoft.com says...
> > Is there a function in VBA that will allow me to copy from 1 array to
> > another. I know that I can loop throught each element and assign 1 array
to
> > the other. But I want to know if there's a faster way such array1 =
array2.
> > Assume they both have the same size.
> >
> > Thanks
> > M
> >

----------


## matelot

Thanks for the response. That's what I need it.
BTW, what is the i^j? Is it supposed to be i power to j?

"Peter T" wrote:

> Just to add, for all versions incl. XL97 declare the target variable as a
> non-array variant. In Tushar's demo change -
>
> >     Dim Arr1(2), Arr2()
> to
>     Dim Arr1(2), Arr2
>
> Regards,
> Peter T
>
>
> "Tushar Mehta" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> news:MPG.1e25a154fafa4aa898b2b7@msnews.microsoft.com...
> > With VB6 (XL2000 or later), use array1=array2, though the target array
> > cannot be dimensioned.
> >
> > Option Explicit
> > Option Base 0
> >
> > Sub testArrays()
> >     Dim Arr1(2), Arr2()
> >     Arr1(0) = 1: Arr1(1) = 2: Arr1(2) = 3
> >     Arr2 = Arr1
> >     MsgBox Arr2(2)
> >     End Sub
> >
> > --
> > Regards,
> >
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >
> > In article <C02B73D2-CD00-4E4B-B906-6D1D5F5BC2ED@microsoft.com>,
> > matelot@discussions.microsoft.com says...
> > > Is there a function in VBA that will allow me to copy from 1 array to
> > > another. I know that I can loop throught each element and assign 1 array
> to
> > > the other. But I want to know if there's a faster way such array1 =
> array2.
> > > Assume they both have the same size.
> > >
> > > Thanks
> > > M
> > >
>
>
>

----------

