Copy Array

[GUIDO22]

Is it possible to copy a complete array to a new array variable declaration ie.

TheNewArray() = TheOldArray()

Or does the new array - need to be Redim'd and each element copied over individually?

Thanks

 

[Dennisk]

try it and see. As that is what many people would do.

 

[GUIDO22]

I HAVE ! ..........and it doesnt seem to work!

Hence, my question to see syntactically if I was missing something..!

 

[KeithG]

YOu could always loop through the array assinging each value to your new array.

 

[gemma-the-husky]

i dont know if you can redefine or overlay definitions in vba

so that instead of an array of 1000 longs, you one array of 4000 chars

but i suspect you cant


and you cant use pointers either.

-----------------
you can assign every member of the array very quickly though anyway

 

[Dennisk]

If your array is embedded in a variant then you can assign it to another array also embedded in a variant. This is how you return an array from a function.

 

[gemma-the-husky]

dennis, can you expand on that a bit please - example maybe

 

[chergh]

What I think he means is this:

dim x as variant
dim y as variant

x = array(1, 2, 3)

y = x

 

[Dennisk]

thats basically it. but a variant is a 14 byte pointer to an object on the heap so it only works efficiently for arrays with a small number of elements.

here is an example of using a variant to return an array from a function

Dim StaffList As Variant
StaffList = ListOfStaffBooked(rstSource![DiaryID], intStartSlot)


'----------------------------------------------------------------------
Public Function ListOfStaffBooked(DiaryID As Long, StartSlot As Integer) As Variant
' Returns a list of StaffIDs in a Array embedded in a Variant
' Access 97 cannot return arrays from functions directly
' Used to determine double/overlapping bookings
'
Dim StaffList() As Long, i As Integer
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Err_Handler
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT StaffID FROM tblStaffBooked WHERE [DiaryID] = " & DiaryID & "AND [StartSlot] = " & StartSlot)

Do While Not rst.EOF
ReDim Preserve StaffList(i)
StaffList(i) = rst!StaffId
i = i + 1
rst.MoveNext
Loop
If i > 0 Then
ListOfStaffBooked = StaffList
End If

ERR_Exit:
db.Close
Set rst = Nothing
Exit Function

Err_Handler:
MsgBox Err.Description
Resume ERR_Exit
End Function

 

[MarkK]

You can also return a 2D variant array from a recordset using the GetRows() method of the Recordset object.

 

[RoyVidar]

Back to the initial question. You are not missing anything, you can't assign an array to an array in the 97 version. That is allowed in later versions, though (where you can also return arrays from functions).

You can assign variant arrays, as demonstrated by chergh

 

[The_Doc_Man]

Let's get down to the real nitty-gritty. Access of ANY version isn't best used for arrays. VBA can do them, yes, and they have their place, yes. But arrays are not really part of the Access paradigm. They're sort of sandwiched in because they are part of the VB paradigm.

I'm not going to insult you by saying you have no valid use for an array, but from a programming standpoint, the Access environment isn't aways like a free-standing VB environment. There are differences.

Let's get back to basics. Why are you using an array in the first place? Of what value is it to your process? Even if we cannot find you an exact match to your needs, maybe we can come close.