Working with arrays

[ajetrumpet]

Array experts,

I have a few assumptions about arrays that I would like some comments on (or clarified) if anyone is interested. Here is what I think the facts are about arrays in general...

1) The only time an array has to be declared a variant is when it is going to hold different values that are of more than one data type.

2) They can hold any number of values that are of a single data type, as long as it is declared in the appropriate section of the procedure (e.g. - Dim MyArray(integer to integer) As String).

3) Array values can be used in functions.

4) The only way to capture values that are completely unrelated to each other, and throw them into an array, is to define them separately. As in the following example (if it is correct)...

Dim MyArray(1 to 3) As String, IntCounter As Integer

  MyArray(1) = Forms!Form!Control1
  MyArray(2) = Forms!Form!Control2
  MyArray(3) = Forms!Form!Control3

For intcounter = 1 to 3
  MsgBox "'The value of this Array Element is' & ' ' & MyArray(intcounter)"
Next

I would greatly appreciate any and all comments on this stuff, as it gets into one of the realms of Visual Basic that I am not too good at. I can throw values into a single type array from a recordset loop, and then reference them, but as far as dealing with the "variant" type nature, I am still unsure as to what I am doing.

I have a basic understanding of simplicity when it comes to arrays, but nothing that I would consider "advanced", which is what I am wanting to learn and understand. Thanks all!

 

[bakerld]

No answer but...

I don't have an answer for you, sorry. But I wanted to say I feel your pain with arrays. I've been programming Access for 3 years now and I still don't understand arrays and have never used one. I know they are powerful but I just can't figure them out.

 

[ajetrumpet]

Baker,

I understand the concept, and do use single type elements with my database files, but I'm kind of searching for information here. I'm hoping some of the site gurus will give me another boost like they have in the past! Hopefully this will serve as some valuable information for all of us.

 

[Rabbie]

As I see it the main advantage of arrays is that you can process the contents in a loop so you dont have masses of code to process a lot of values. You can just increase the index to your array by using the count value of a for statement.

Hope this helps.

 

[tehNellie]

[edit] Pending a re-write.

Stupid, stupid board timing out and losing all the stuff I wrote, does "Arrays are great" as a short and sweet version help?

I'll try and do it [yet] again, soon.

 

[tehNellie]

1) The only time an array has to be declared a variant is when it is going to hold different values that are of more than one data type.

No, some methods of creating an array require that you declare it as a variant.
To use the array() function requires a variant for example:

dim myarray as variant
myarray = array("rod","jane","freddie")

I'm not a huge fan of variants generally, they have their place for sure, but they can't half help complicate matters later on. Likewise I'm struggling to think of an example when using a variant array to store multiple data types isn't likely to cause as many headaches as it solves, especially if you use a dynamic array, but I'm open to persuasion on that point and it is certainly possible if you declare the array as a variant.

2) They can hold any number of values that are of a single data type, as long as it is declared in the appropriate section of the procedure (e.g. - Dim MyArray(integer to integer) As String).

It's not as rigid as that and you don't need to declare using myarray(integer to integer) unless you really want to. By default Arrays use base 0 which I've gotten used to, using "Option Base 1" also means your arrays will start at element 1, but I found it more confusing than helpful in the end and reverted back to base 0. It's also worth noting that some VBA created arrays such as using Filter() will create a 0 based array regardless of any Option Base statement.

You also don't need to specify your array dimensions when you declare it. If you don't know how many elements you need when you start you could, I suppose play safe and use:

Dim myarray(1000) as string

but the more practical alternative is to declare a Dynamic array and resize it as you need to using Redim [preserve].

Redim redimensions the array to the size that you specify:

Dim Myarray() as string

redim MyArray(5)

What if you've already got data in it that you want to keep? use a combination of redim preserve and Ubound (and possibly Lbound if you aren't using base 0 arrays):

dim myArray() as String
redim myArray(1)
myArray(0) = "Rod"
myArray(1) = "Jane"
'oops we need some more
redim preserve myarrary(ubound(myarray)+1)
Myarray(2) = "Freddie"

Ubound returns the upper index value of the array, Lbound does the opposite.

What you can't do with Redim is create an array as a string and then redim it as an integer.

3) Array values can be used in functions.

Absolutely, either single elements or the entire array can be passed to a Function.

4) The only way to capture values that are completely unrelated to each other, and throw them into an array, is to define them separately. As in the following example (if it is correct)...
Code:

Dim MyArray(1 to 3) As String, IntCounter As Integer

MyArray(1) = Forms!Form!Control1
MyArray(2) = Forms!Form!Control2
MyArray(3) = Forms!Form!Control3

For intcounter = 1 to 3
MsgBox "'The value of this Array Element is' & ' ' & MyArray(intcounter)"
Next

To be pedantic, your example is related, they're all controls on a form, you could do something like:

i = 0
For Each control In Me.Controls
 If TypeOf control Is TextBox Then
   myarray(i) = me.control.value
   i = i +1
 endif
next

or if you want to be really fancy use a multidimension array and capture where the data is coming from:

i = 0
For Each control In Me.Controls
 If TypeOf control Is TextBox Then
   myarray(i,0) = me.control.name
   myarray(i,1) = me.control.value
   i = i +1
 endif
next

To be honest, while I use arrays a lot, I rarely use a static array and have never needed a multidimension array to date, although the above example perhaps illustrates where you might need/use one.

They are worth spending a little time getting your head around, if you work with vba for any length of time you'll get to a point when you'll actively want, or at least, need to use them.

 

[CraigDolphin]

Like BakerId, I've hardly ever touched arrays so far in my VBA work. Just never found a good tutorial that explains the basics well enough for me to grasp it.

Thanks for a very educational and interesting thread. I'm learning a lot.

 

[ajetrumpet]

Just never found a good tutorial that explains the basics well enough for me to grasp it

I doubt you'll find a tutorial that does this Craig. Actually, I don't think I've ever read a tutorial. Talking face-to-face to someone for 30 minutes can substitute for hours of reading through tutorials, in my view anyway!!

I would much rather read information, just like Nellie has provided us.

 

[tehNellie]

If you want a practical example of using arrays, consider how you might go about consistently splitting down a column of names down into Firstname and Surname.

Points to consider:
You might have double-barrelled firstname like "Anne Marie"
You might have double-barrelled surnames
You might have triple barrelled surnames like "Van Der Saar" or any combination thereof. You also have to deal with name columns that could be Firstname, surname or vice versa.

You're welcome to have firstname/surname tables containing known name combinations or to prompt the user for the answer.

My solution was to populate an array using split() and then combine the various elements into likely combinations and perform a lookup to see if that combination had been encountered before and if there were no matches to prompt the user to give the correct combination all using one declared variable.
Using something along the lines of:

'split the name field using space as a separator, ignore hyphens for now, they simplify matters at this stage but should be removed later to ensure consistency across data sets.
myarray = split(rs![name]," ")

'see how many elements there are and act accordingly
if ubound(myarray) = 2 then
  'theres only a firstname and surname
  write the values to the relevant column"
elseif ubound(myarray) = 3 then
  Check myarray (0) & " " & Myarray(1) against firstnames
  Check myarray (1) & " " & myarray(2) against surnames
  Write the combination that matches to the table or prompt the user
elseif ubound(myarray) = 4 then
  keep going until you run out of combinations to check on.
end if

that's a very simplified example of how it works, and not especially well written, even for psuedo code but I hope it does at least give some indication of where an array can be worth it's weight in gold.

 

[CraigDolphin]

I can see how that approach could be exceedingly helpful...have done something similar when trying to exactly that situation (only also having to deal with suffixes (JR, SR, III etc) and middlenames/initals also). I gave up trying to do it in Access and used Excel and the convert-text-to-columns approach, along with numerous countif and if statements galore. There were thousands of names in two different spreadsheets to reconcile with each other, then populating a new db...and no formal convention was ever used when entering the names into the spreadsheet.

Ugly Ugly Ugly.

This is a much more powerful approach. Wish I'd seen this back then

 

[Banana]

Seeing that others already has explained arrays well, I'd like other to consider collections.

When I need to deal with a group of controls, recordsets or specific kind of objects, I would prefer to use collection instead of arrays. I personally think arrays makes more sense for holding a set of data, but not so much for objects.

To declare a new collection:

Dim colSomething As New Collection

The major advantage of collection over array is you do not need to worry about dimensions and re-dimensioning it. Just add it!

colSomething.Add Me.Control1

Any custom collections has inherent properties Count and Item and two methods, Add and Delete. One more property of collection is that you can use keys to quickly retrieve a specific control out of a collection; something you can't do with an array.

colSomething.Add Me.Control1, "1st"

Debug.Print colSomething("1st").Name

The problem with collection, however, is that all objects held in a collection are defined as variant- there's no guarantee that a collection of objects all represent tabledef without a querydef leaking into it. Some will circumvent this limitation by declaring a custom collection in a class module and using Property Get/Let to restrict what object may be added to a collection.

Furthermore, it's one-dimension, so a mutli-dimension array would be able to hold the pointer to an control, its value, and its old value and just about whatever you need to hold. Again, this theoretically can be circumvented by defining a custom collection with extension in a class module, though I would imagine it too impractical.

 

[CraigDolphin]

Interesting. I've always used queries or tables to populate recordsets.....does this mean that you can declare a new, blank recordset and then populate it using the add method in vba directly?

I'm not quite sure I follow the 'keys' terminology. Is that equivelent, say, to the tag property of a control, but for an item in a collection? Essentially it's attribute information (or metadata) about the item in the collection that can be used to refer to specific collection items in code without using their name? Are you limited to one key per item, or can you have a list of keys separated by commas?

 

[tehNellie]

I gave up trying to do it in Access and used Excel and the convert-text-to-columns approach, along with numerous countif and if statements galore.

If it's any consolation I wrote the first version in Excel because I couldn't figure out how to do it in Access at the time.

When I need to deal with a group of controls, recordsets or specific kind of objects, I would prefer to use collection instead of arrays. I personally think arrays makes more sense for holding a set of data, but not so much for objects.

Agree with you totally. I wouldn't normally attempt to use an Array to create something like:

myarray(0,0) = "Firstname"
myarray(0,1) = "Bill"
myarray(1,0) = "Surname"
myarray(1,1) = "Jones"

Which is starting to resemble an object class without the flexibility or control.

I tend to use Arrays as a means to take that initial data, turn it into manageable chunks, do someting to it and then pass it out to its destination whether that's a table, an object or whatever.

 

[Banana]

Interesting. I've always used queries or tables to populate recordsets.....does this mean that you can declare a new, blank recordset and then populate it using the add method in vba directly?

Well, I don't know about a "blank" recordset, but you can 'set' a recordset to tabledef or querydef object, where any edits will be reflected in underlying source or use CloneRecordset method to create a copy without affecting the source.

Besides, I don't see a "Add" property for recordset?

I'm not quite sure I follow the 'keys' terminology. Is that equivelent, say, to the tag property of a control, but for an item in a collection? Essentially it's attribute information (or metadata) about the item in the collection that can be used to refer to specific collection items in code without using their name? Are you limited to one key per item, or can you have a list of keys separated by commas?

No, more like an index.

Instead of asking a collection for a item named "ARidiculouslyExcessivelyVeryLongNameForADumbObject", you can just use key "This" to get it.

colSomething.Add "ARidiculouslyExcessivelyVeryLongNameForADumbObject", "This"

Debug.Print colSomething("This") 'Will return: ARidiculouslyExcessivelyVeryLongNameForADumbObject

You can only have one key per item, and it must be unique. (Actually, you could think of it in same terms as primary key in a table)

 

[supercool]

when i have a lot of un-sorted or unlinked data to put into a more organized order for delivery or presentation of information ( such as report, or create a new table with specific format)..
you have to put in more coding efforts to use array than use recordset, and of course, it would bring huge benefits to you, you can access any of its record instantly without using "move next" command, also you can get its side easily by unbound function, in addition, you could pass whole chunk of logically unrelated data to another function which basically could never be supported by recordset
however, array generally responses slightly slow compared to recordset


we have to bear in mind the cons and pros while designing.

 

[tehNellie]

to be honest, I don't really think that comparing Arrays to Recordsets is really comparing like for like.

All an arry really comprises is a variable containing one or more distinct elements. And where it really comes into it's own is using some of the inbuilt functions such as split() and Filter() to populate it.

Coding for Arrays isn't that difficult once you understand the concept and the controls available for it and they're certainly no more complicated than a recordset.

as for performance you might just as well compare a car against a submarine sure they both move and have an engine, but they aren't really designed to perform the same task.

 

[ajetrumpet]

they're certainly no more complicated than a recordset.

as for performance you might just as well compare a car against a submarine sure they both move and have an engine, but they aren't really designed to perform the same task.

To say, The LEAST!!

 

[CraigDolphin]

Thanks for the clarifications and corrections. Think I might bookmark this thread for future reference

 

[tehNellie]

I guess to take this further and to expand a bit more using the previous example:
To get the name information from the Database and to write the relevant information back to the table I used a recordset. I guess I could have read the whole lot into an array and fired INSERT statements at the database, but why do it that way? The recordset does exactly what I want and need it to.

Now I've got a window on my data with the recordset, I need to do something to it, in this case take a single column and turn it into two columns where there are multiple strings held in the first (still with me?). While I could do some jiggery pokery with instr(), Mid(), left() and so on, VBA comes with an inbuilt function that does exactly what I need it to which is break the [Name] string down into parts on a common separator: " " ie Split() which dumps everything into an Array that I can do stuff with much more easily than trying to manipulate the recordset column or pass values into individual variables, of which I reckon you'd need at least 7, but might only populate 2.

I can handle intials immediately, its a 4 element array and the second element is one character long? What are the chances it's going to be a name like "Jean C Van Damme"? At the very least I can prioritise how the lookup functions to reduce the amount of work taken to find a match. maybe I want to ignore initials, prefixes or suffixes altogether. Performance wise, it's all in memory at this point, it'll run as fast as your code and PC will let it.

You're working with what is supposed to be Firstname, Surname and you get Surname, Firstname? How hard do you reckon it is to transpose those array values?

Now that I've done all my messing around with the array what do I use to populate the table? why the very same recordset I used to get the original name information to begin with, it's just silly not to.

 

[Simon_MT]

This is classic PICK.

Search the web for:

Universe multi-value fields.

Here's an example:

http://search.cpan.org/~rubykat/Tie-FieldVals-0.40/lib/Tie/FieldVals/Row.pm

Universe is a brand of the PICK OS.

Simon