Copy one array to an other

[chuckchuckit]

Can I copy all of the elements from one array to the other easily without looping through each element row and column? I would then be deleting all elements from one of the arrays.

Dim MyArray(200, 30)
Dim MyArray_BKUP(200, 30)

I was trying various forms of "With" type coding, using Resize etc that works with "Cells" and "Ranges", but can't seem to get anything similar to work with arrays.

Thanks.
Chuck

 

[Weaver]

You could maybe write the array to a set of cells

    Set rng = Range("A1", Cells(201, 31))
    rng = myArray
    myArray_bkup = rng

 

[chuckchuckit]

Weaver, That looks interesting. I was actually hoping to stay away from using cells with that much data to help with the speedup of not going to the cells. And I would really need 2 separate sets of data as one would be changing much of the time. And deleting source might delete both.

Here is something I found on the internet concerning not being able to copy arrays directly:

"Unfortunately, VBA does not allow you to copy one array to another with a simple assignment statement. You must copy the array element by element."

Link:
http://www.cpearson.com/excel/vbaarrays.htm

So looks like I'll have to loop. ...seems there should be a way since it should be some sort of memory address transfer which should be easy without a loop...but then again if memory for the array allocation is not contiguous then perhaps that would not work so well. ...thoughts from former 'C' programming days many moons (decades) ago.

Thanks.

Chuck

 

[GTO]

Greetings,

Given your example declarations, it appears that you are transposing. How are the values loaded in the first array?

 

[snb_]

What's the problem ?

MyArray_BkUP=Myarray

 

[chuckchuckit]

Both arrays are variants. Each element is loaded one at a time with various values. Integers, longs, doubles and no way to know for sure which value in each element.

At certain intervals I use the data in most elements, then need to save all of those elements in one of the arrays to the other as a "backup copy". Erasing the first array and start reloading the elements again.

The "backup copying" would take the longest to move every element one at a time. At certain times I will need to also move whole rows at a time. I was hoping to not have to loop the columns for each row then either.

Hope that pertains to what you were asking.

Chuck

 

[chuckchuckit]

snb, following code comes back with "Can't assign to array"

MyArray_BkUP=Myarray

Does it work for you?

 

[snb_]

Yes if you do not dimension them

for example:

Sub snb()
  sq=sheets(1).cells(1,1).resize(300,30)
  sn=sq
  sheets(2).cells(1,1).resize(ubound(sn),ubound(sn,2))=sn
End sub

 

[chuckchuckit]

GTO, An example loading would be:

MyArray(3 ,21) = 55

'or if using variables

Dim varROW, varCOL
    varROW = 3
    varCOL = 21
MyArray(varROW, varCOL) = 55

Not sure I understand the transposing?

 

[chuckchuckit]

snb, the "cells" reference would be referring to the spreadsheet cells "instead of" array elements. Wouldn't it?