[SOLVED] Very strange behaviour in collection of arrays (1 Viewer)

smbrr

Registered User
Joined
Jun 12, 2014
Messages
61
Hi there,

I'm in the need of some help. I open a text file and store it in a collection of array:

Code:
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    Line Input #filenumber, lineread
    file.Add Split("" & SEP & lineread, SEP) 'index starts at 1 for simplicity
Wend
Close #filenumber
Then I'm going to make a bunch of changes to this collection but it just... doesn't do it. The code works fine, but the arrays are literally unchanged:

Code:
For i = 1 To UBound(file(1))
    If file(1)(i) = "Sales Doc. Type" Then file(1)(i) = "Type"
    If file(1)(i) = "Name 1" Then file(1)(i) = "List Name"
    If file(1)(i) = "Main WorkCtr" Then file(1)(i) = "Mnwkctr"
Next i
I tried it in the immediate debug window, and sure enough I can execute all sort of code without error but none of the data inside my collection is changed.

Code:
      : Item 1(0) : "" : String
      : Item 1(1) : "Order" : String
      : Item 1(2) : "Sales Doc. Type" : String
      : Item 1(3) : "System Status" : String
      : Item 1(4) : "User Status" : String
      : Item 1(5) : "City" : String
      : Item 1(6) : "Name 1" : String
      : Item 1(7) : "Main WorkCtr" : String
      : Item 1(8) : "Country" : String
What the hell is this? :banghead:

Thank you
 

static

Registered User
Joined
Nov 2, 2015
Messages
823
A Collection's items are read only.
Copy the item, change it, delete the original, add the copy.
 

smbrr

Registered User
Joined
Jun 12, 2014
Messages
61
A Collection's items are read only.
Copy the item, change it, delete the original, add the copy.
But what I'm putting in the collection should only be references to arrays, the arrays themselves should be be fully accessible, shouldn't they?

Like, as a workaround, I'm making a collection of collection of object containing 1 single parameter "value", so I can do:
Code:
file(i)(c).value = "whatever"
The pointer to that object is read only, but I can access the object and modify it, why is that not happening for my Arrays and is there a way I can make it happen?

If not I suppose I'll keep my work-around but that creates a lot of unnecessary code and overhead and irks me.
 

static

Registered User
Joined
Nov 2, 2015
Messages
823
Normal values, including arrays are read only and not pointers. Objects are ... special.

You could try using a dictionary object instead. AFAIK they're updateable.


msdn.microsoft.com/en-us/library/x4k5wbx4(v=vs.84).aspx
 

rpeare

Registered User
Joined
Sep 15, 2016
Messages
18
If all you're interested in is changing the column headers can't you just artificially create the first row of the array then add all the subsequent records from your file skipping over the first line?
 

MarkK

Super Moderator
Joined
Mar 17, 2004
Messages
7,761
You can write to an array of arrays . . .
Code:
Dim var
    
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    If IsEmpty(var) Then
        ReDim var(0)
    Else
        ReDim Preserve var(UBound(var) + 1)
    End If
    Line Input #filenumber, lineread
    var(UBound(var)) = Split("" & SEP & lineread, SEP) 'index starts at 1 for simplicity
Wend
Close #filenumber
Or you can wrap your array in a class, so create a class module with code like...
Code:
Option Compare Database
Option Explicit

Public vLineArray as Variant
...and maybe you call it cYourClass.
Then add New instances of that class to the collection, and assign the array to the Public variable in the class....
Code:
Dim tmp As cYourClass
    
Open filepath For Input As #filenumber
While Not EOF(filenumber)
    Line Input #filenumber, lineread
    Set tmp = New cYourClass
    tmp.vLineArray = Split("" & SEP & lineread, SEP)
    File.Add tmp
Wend
 

smbrr

Registered User
Joined
Jun 12, 2014
Messages
61
If all you're interested in is changing the column headers can't you just artificially create the first row of the array then add all the subsequent records from your file skipping over the first line?
No, I make a lot of changes to the file itself, the headers are just the tip of the iceberg.

I suppose doing an array of array is a good idea, it's just that I use collections almost everytime I need to store stuff in a list, and everytime I tried to use arrays, I've hit roadblocks like dimensions that can't be redim'd, arrays can't be class objects and so on, so I rarely use them at all.

I might do that then.

Performance wise, if I'm just looking through them with an index and not a string key, are array better than collections? Because my big collection of collection of objects gets really slow to access when I start hitting indexes over 30 000.

Thank you for the explanation of arrays and pointers. At the very least, trying to change the value in there should throw an error like it does for simple data types like a string in a collection.
 

smbrr

Registered User
Joined
Jun 12, 2014
Messages
61
Performance wise, if I'm just looking through them with an index and not a string key, are array better than collections?
To answer my own question, yes it's much faster. The speed is a perfect constant whether I access item #3 or item #56103, whereas the collection really crawled to 10 times slower toward the end.
 

static

Registered User
Joined
Nov 2, 2015
Messages
823
I use collections almost everytime I need to store stuff in a list
hitting indexes over 30 000
The obvious question (since this is a forum about Access, which is designed to store and manipulate large sets of data) is why?
 

smbrr

Registered User
Joined
Jun 12, 2014
Messages
61
50k row isn't what I would call a large data set considering my databases have over 100 million rows.

As for Access being designed to store an manipulate data, for human-speed interaction, sure, not for program-speed interaction. Using tables and recordsets in VBA for every operation that needs a list of some kind would cause the code to run at a thousandth of the speed I'm used to (not to mention the pain of having to use Access objects). Much like my Excel VBA macros often read a table at the beginning and store it in collections of objects that are used for the whole macro instead of constantly reading/writing/rearranging ranges in the workbook.

It's often just text and we have like 2 GB of RAM to play with, so why bother?
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top