Array question changing column 1

[HebieBug]

Is it possible to change an array.
Insteed of setting an array so that it has a set ammount of rows thus VB being greedy in memory. Is is possible to run the array through a loop and incrementing the rows by one as is needed.
ANyone have some code for this

 

[RickSpr]

Have you looked at the ReDim statement? Provided you Dim your array with no bounds (e.g. "Dim avarArray()&quot, you can use ReDim to change the number of elements later. You can even enlarge the array after you've put data in it.

Rick Sprague

 

[LegoPiece]

Visual Basic supports dynamic arrays. This means that you don't have to define the number of elements upon declaring it.

E.g.

Dim MyArray() As Integer

Whenever you wish to resize to the array, you use the ReDim statement and specify the new number of elements you want it to have.

By using the ReDim Preserve statement, you re-size the array to the specified length and also ensure that whatever data you have in the array is untouched (preserved).

Here is an example of how you can do this:

Dim MyArray() As Integer

Private Sub FillArray()
Dim i as Integer

For i = 1 to 10
Redim Preserve MyArray(i)
MyArray(i) = i + 1
next i
End Sub

...Hope this helps!
Lego

 

[HebieBug]

All worked fine and placed it into my code...
Only thing is that there is something to add now the next one is a two dimentional array..
Being a smart little cookie I changed the code to addapt it to two dimentions
Private Sub Command1_Click()
Dim i As Integer
Dim MyArray() As Integer
For i = 1 To 10
ReDim Preserve MyArray(i, 6)
MyArray(i, 6) = i + 1
Next i
End Sub

The dreded responce from VB will not let me do it
Any ideas on it

 

[rvBasic]

The Preserve keyword allows you to change the size of the Last dimension of an array.

Obviously, by changing the value of i the way you do in the For ... Next loop, you change the size of the first dimension.

A solution to your problem would be to add columns to your array (change size of last dimension) and when done Transpose (i.e. interchange rows and columns) the array. _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[MichaelRed]

I usually define a UDT for the multidimensional thiggggggyyyyysssssssss Then declare that as an unbound array. Then the redim preserve doesn't care. It's workin on the single oblect. You DO need to declare the UDT in the General section.

General:

Private MyUdtType
MyInt as Integer
MyStr as String
MyLong as Long
End Type
Dim MyUdt() as MyUdtType


Private Function FillArray()

Dim Idx as Integer
ReDim MyUdt(0)

For Idx = 1 to 10

MyUdt(Idx).MyInt = Idx
MyUdt(Idx).MyStr = Trim(Str(Idx))
MyUdt(Idx).MyLong = Clng(Idx)
MyUdt(Idx) = Idx + 1

Redim Preserve MyUdt(Idx)
Next Idx

Redim Preserve MyUdt(UBound(MyUdt) - 1)


End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[jmarler]

HebieBug,
Although everyone here is correct with their statements, MichaelRed has the best solution. Visual Basic will let you redim arrays with preserve AS LONG AS THE ARRAY IS ONE DIMENSIONAL! Otherwise it will fail. If you want to store multiple elements in each "row" of your resizable array, then a UDT is going to be your best bet.
But, if a UDT will not work for what you need to do, then you could try an array of arrays (scary, but it does work). Perhaps an even better option (building on the array of arrays idea) would be a collection of classes or a collection of collections with data.
Why a collection? Remember that when you redim an array with the PRESERVE keyword, it needs to copy all of the data in memory to the new structure. Not very efficient (but at times, it is the best option). Collections, on the other hand, simply add a new pointer to you data without having to recopy all of your data everytime you need to resize. Its just a thought, but you may want to look into it.



- Jeff Marler

http://www.JeffMarler.com

(please note, that the page is under construction)

 

[RickSpr]

Jeff,

Hold on there! ReDim Preserve doesn't necessarily have to "copy all of the data in memory to the new structure". Windows memory blocks can be resizable; the resizing may occur by relocating a block above it. Windows does this very efficiently. I don't know that VBA uses such blocks for unbound arrays--but do you know for a fact that it doesn't?

Besides, you'd only have to moves the array elements if they were required to be contiguous. But if they're contiguous, you can move them all with a single machine instruction. Although it's an "interruptible" instruction, and typically takes longer than most machine code instructions, it's the same way strings are moved all the time, and being low level machine code, it's pretty darned efficient.

Compare that with the collection technique. The collection has an array of pointers, so you're still enlarging an array when you add something to a collection. Moreover, you're also adding all the overhead of an object interface. The tradeoff (between moving array contents versus moving an array of pointers, and between inline code versus an object method call) probably depends on the application details, but my hunch is that it breaks in favor of arrays more often than not.

You can look at it this way, too: If Collections were always more efficient than unbound arrays, why wouldn't Microsoft just implement unbound arrays as specialized Collections? Or maybe they do, in which case there's no disadvantage to using an unbound array, right? Rick Sprague

 

[rvBasic]

...Visual Basic will let you redim arrays with preserve AS LONG AS THE ARRAY IS ONE DIMENSIONAL! Otherwise it will fail ...

I'm baffled by this comment, because I do it all the time (I have to admit that I haven't done anything above the third dimension). Here's what MicroSoft says: (VB6 Language Reference p869)
... If your array has two or more dimensions, you can change the size of only the last dimension and still preserve the contents of the array ...

I think several things have become mixed up. Properly speaking all individual elements of an Array are of the same Data Type. In that case, there is no difficulty in changing the size of the last dimension.

However, if we are mixing data types, then we can no longer properly speak of an array. As a consequence Redim is not an applicable statement and the use of a Collection (or a Dictionary Object) may be more appropriate.

I have no definite proof that Redim does or doesn't copy the entire array into a new one, but there are some strong indications that it does:
...this call [ReDim] gets more and more painful from a performance standpoint as the system grows. ... Even if you ReDim Preserve in chunks instead of one element at a time, you'l find that the ReDim call is the slowest part of the system ... (Advanced VB6, Matthew Curland, p184). Which is in agreement with my experience. _________________________________
In theory, there is no difference between theory and practice. In practice, there is. [attributed to Yogi Berra]

 

[HebieBug]

Found out the answer and solved the problem
The following worked form me
Dim x As Integer
Dim i As Integer
On Error GoTo ending
Dataenviroment.Connection1.Open
Dim arecord As New ADODB.Recordset
arecord.Open "Select * from User_Details2 WHERE LastName LIKE '" & Me.TxtLastL & "%'", DEAccessAdmin.CTS, adOpenKeyset, adLockReadOnly
x = arecord.RecordCount
Dim MyArray() As Variant
ReDim Preserve MyArray(6, x)
i = 1
Me.ListBox1.ColumnCount = 6
While Not arecord.EOF
If Not IsNull(arecord("Feild1&quot) Then
MyArray(0, i) = arecord(Field1)
End If
MyArray(1, i) = arecord("Field2&quot
MyArray(2, i) = arecord("Field3&quot
MyArray(3, i) = arecord("Field4&quot
MyArray(4, i) = arecord("Field5&quot
MyArray(5, i) = arecord("Field6&quot
arecord.MoveNext
i = i + 1
Wend
ListBox1.Column() = MyArray
arecord.Close
Set arecord = Nothing

Thanx to everyone for there help..
Nice to find a site where when it hits the fan you can turn to for assistance