How do *you* use arrays?

[Fred Boer]

Hello! I am teaching an introductory course in programming in VBA. I am
using DAO and Access 2000. I am not a programmer, and while (I think! <g>) I
understand how to create an array, and can teach them *how* to do it, I
don't have enough practical background to teach them how arrays are
typically used in applications. So... all you programmers out there:

1. Do you frequently use arrays in your applications? Sometimes? Rarely?
2. If you use them, can you give me some concrete examples of what you use
them for?
3. What kinds of data do you typically store in arrays?

Many thanks!
Fred Boer

P.S. You'd be quite justified in asking why I am teaching a programming
course if I'm not an experienced programmer... and I'd *love* to explain the
circumstances and discuss it with you at length, but I really expect that
nobody has that kind of time!

 

[Guest]

One thing an array is good for is in conjunction with
the "split" function to break apart a string on your
delimiter--e.g., if you want to break apart the range
StartNo-EndNo, the split function will create an array.

 

[Ken Snell]

Hello Fred...you keep expanding your capabilities! < g >
comments inline.


--
Ken Snell
<MS ACCESS MVP>

Hello! I am teaching an introductory course in programming in VBA. I am
using DAO and Access 2000. I am not a programmer, and while (I think! <g>) I
understand how to create an array, and can teach them *how* to do it, I
don't have enough practical background to teach them how arrays are
typically used in applications. So... all you programmers out there:

1. Do you frequently use arrays in your applications? Sometimes? Rarely?

I don't use arrays very often, but when I do I typically use them to read in
a lot of information that I then will "loop" through (such as reading in the
filenames via a loop with the Dir function -- I do this when I know that I
need to use the Dir function again while still using a filename...Dir cannot
be called for new folder without losing the old info, so by putting the info
into an array, I can store the info and not worry about it). I'd say that I
use arrays in 10 - 15 % of my code modules.

Note that all the use of Forms, Controls, Worksheets, etc. objects in VBA
code is the use of arrays! So you and your students will be using arrays
without necessarily thinking about it. For example:

For intLoop = 0 To Me.Controls.Count - 1
Debug.Print Me.Controls(intLoop).Name
Next intLoop


I find that I use arrays more often in EXCEL VBA programming than I do in
ACCESS VBA because in EXCEL I usually read in a lot of data for later
manipulation; in ACCESS, those data are already stored in tables and can be
easily accessed via queries and recordsets.

2. If you use them, can you give me some concrete examples of what you use
them for?
3. What kinds of data do you typically store in arrays?

All kinds...numbers, strings, etc.

 

[Fred Boer]

Thanks! I've never worked with the "split" function, but I will look it up!

Fred

 

[david epsom dot com dot au]

This is an Access development group. Access is a database
product (sorry!). Access has a 'persistent array structure
called a 'table' and an 'indexed' array structure called a
'collection'.

Almost all of my work with arrays is actually work with tables.
In 10 years of Access programming I have never had to use an
array, and the only arrays or user defined collections in our
products are there for historical reasons.

Arrays are used for high-speed small-memory access to data
when the original data comes from disk, tape, or the network.
With a database application, the speed and size of the
application is determined by the data and the database engine,
and there is almost never a need to cache sets of data outside
the database cache.

Furthermore (!) an Array is a concept inherited from Mathematics
by way of Basic and Fortran. If you aren't doing mathematics,
or an old fashioned Basic/Fortran programmer, it is not likely
that you would even think of using an array instead of a
collection, dictionary, table, stack, heap, or object.

And in VB6, the concept of the array was preserved by the 'control
array'. There is no similar concept in Access, because we have
'control collections' instead.

So: it's an obsolete feature, drop it from the course and
teach 'collections' instead. If you are forced by your syllabus
to teach 'arrays', show them rst.GetRows, tell them that an
array is just an old way of holding a recordset, and move on.

(david)

 

[Fred Boer]

Dear Ken:

Hello Fred...you keep expanding your capabilities! < g >

<Chuckle> Well, something has to keep my waistline company! <g>

Thanks for your comments. I was particularly intrigued by your description
of how looping through a control collection is actually using an array. I
never thought of it that way!

Thanks a lot!

Fred

 

[Fred Boer]

Dear David:

Comments inline...

This is an Access development group. Access is a database
product (sorry!). Access has a 'persistent array structure
called a 'table' and an 'indexed' array structure called a
'collection'.

Yes, I know this is an Access development group, and I'm not at all sorry
that Access is a database product; if it were a spreadsheet I'd find it
horribly boring! <g>
Thanks to you and Ken, I am now beginning to understand the way tables and
collections can be considered as arrays...

Almost all of my work with arrays is actually work with tables.
In 10 years of Access programming I have never had to use an
array, and the only arrays or user defined collections in our
products are there for historical reasons.

A useful perspective, thanks!

So: it's an obsolete feature, drop it from the course and
teach 'collections' instead. If you are forced by your syllabus
to teach 'arrays', show them rst.GetRows, tell them that an
array is just an old way of holding a recordset, and move on.

Perhaps it might help clarify things if I mention that the curriculum I am
presenting isn't intended to be "database programming", but about general
programming concepts. Is it possible that arrays are more significant when
doing other types of development? Perhaps that is why it figures prominently
in the curriculum. On a personal note, I have for some time used "Beginning
Access VBA" by Sussman, et. al. as a learning tool, and have come to this
book's section on arrays, which has also led me to ask about their
usefulness and application

Thanks for taking the time to respond!

Fred

 

[Marshall Barton]

The challenges never stop, do they Fred? ;-)

Hello! I am teaching an introductory course in programming in VBA. I am
using DAO and Access 2000. I am not a programmer, and while (I think! <g>) I
understand how to create an array, and can teach them *how* to do it, I
don't have enough practical background to teach them how arrays are
typically used in applications. So... all you programmers out there:

1. Do you frequently use arrays in your applications? Sometimes? Rarely?

A Database System has almost no need for arrays. There are
only the very rare (and advanced) situation where an array
is more appropriate than a recordset or a collection. One
place where an array is appropriate (probably too advanced
for a beginner class) is when managing a combo or list box
RowSource with a callback function. As a matter of fact, I
can't think of an area of VBA where it would not be a
disservice to teach Access/VBA beginners about arrays, they
might get the idea that they should use arrays instead using
a more appropriate approach. Spend your limited lecture
time and their even more limited attention span on more
useful data structures or programming techniques.

2. If you use them, can you give me some concrete examples of what you use
them for?

Converting a number to text:
Public Function Digit2Text(k As Integer) As String
If k >= 0 And k < 10 Then
Digit2Text = Array("Zero","One","Two","Three", . . .)(k)
Else
Digit2Text = "Too Big"
End If
End Sub

3. What kinds of data do you typically store in arrays?

Strings, Integers, etc. However, with a variant array, just
about anything, but I don't think you want to go there yet.

Good luck in your new career ;-))

 

[Allen Browne]

Hi Fred

As the others have said, I use arrays in Access far less often than I
expected.

Mostly it is repetitive stuff inside loops, where the array is the simplest
way to handle the names. For example, quick'n'dirty code that loops through
all controls on a form, or all items in the AllForms collection, to select
the items you need to work further with.

One feature I do find useful is the ParamArray, so a function can accept an
unknown number of arguments and work with them as an array. This example
returns the largest value passed in:
Function Largest(ParamArray varValues()) As Variant

Dim i As Integer 'Loop controller.

Dim varMax As Variant 'Largest value found so far.



varMax = Null 'Initialize to null



For i = LBound(varValues) To UBound(varValues)

If IsNumeric(varValues(i)) Then

If varMax >= varValues(i) Then

'do nothing

Else

varMax = varValues(i)

End If

End If

Next



Largest = varMax

End Function


Example usage:
? Largest(1, 5, 17, 0, 12, -98, 4.5, 3)

 

[Paul Overway]

Fred:

Within Access, I've seldom used arrays, except in some of the cases
indicated by other posts. In VB, I've used arrays for things like storing a
virtual deck of cards, encryption/decryption routines, etc. VB also allows
control arrays....which allows you to create new controls during runtime.
Arrays DO have some utility, but not so much in a database application.

 

[david epsom dot com dot au]

oops.... when I said (sorry) I meant that as an apology for
restating the obvious! Not as a satirical aside :~(

programming concepts. Is it possible that arrays are more significant
doing other types of development? Perhaps that is why it figures
in the curriculum. On a personal note, I have for some time used

Well, I had a friend at university who was irritated to find the lecture
notes still said '1956' in the bottom right corner.... I think that is why
the 'array' figures in the curriculum!

OK, that was a little unfair. An array is a simple basic way of looking at
a data set, and even if it is now of limited use to a developer, it is still
helpful in describing structures. For example, how would you describe the
routing table in a router? It is typically a statically allocated memory
area with a two dimensional numeric index accessed through a hash table,
even if it wasn't originally declared as an 'array'. Or how would you
describe the data on a graphics card? Typically each point is processed as a
matrix of local points, and array syntax is the same as matrix syntax.

However, a major reason for using an array is the lack of some other
structure, so developers use arrays a lot less than they used to, and
database developers have even fewer places to use an array than any other
kind of developer.

Mathematicians and Engineers had been dealing with tables of data before
Excel and Access had been dreamt of, and the table structure got the name
'array' when FORTRAN was developed. It was a statically sized structure and
FORTRAN did memory allocation for data at compile time.

For most development now you would expect to see dynamically sized storage
elements used more than statically sized storage elements, and even when you
have a statically sized storage element called an 'array', that key word
typically is not used. VB is unusual both in using that keyword and in
having a dynamically sizable array structure.

Access was unusual in having a persistent/streamable array structure (the
table). Other modern languages do have persistent/streamable dynamic data
structures now, but they are often called neither 'array' nor 'table'.

In the absence of a persistent/streamable dynamic data structure,
programmers used to have to use what they had. They built static data
structures for fast access, and wrote their own streaming code to get
to/from disk. Access no longer has it's own programming language (Access
Basic). Instead, the tables and recordsets are part of the ADO/DAO objects.
The table structures that VBA/VB/VBS natively support are only Array and
Collection. In C++, most of the dynamic data structures are technically part
of the library, rather than native parts of the
language specification. Again, an Array is a native part of the C++ base
language. This doesn't make an array an import structure for a developer,
but it does mean that an array is an important structure for a language
student!

Situations where someone might use a simple array: If you LACK other
language structures, or if you need low-overhead, low-memory, and
fast-access, or if your data is natively indexed numerically, is static and
multi-dimensioned, or if your data is so semantically simple that you can't
be bothered with another structure, or if your language maps another data
structure onto a simple array (for example, it is common to map strings as
an array of characters, although that is not true in VB).


And finally, what you asked for in the first place, places where I have used
arrays:
Analysis of electrical signals where the Math library used arrays, hash
tables before the PC got so fast that hashing became irrelevant, string
manipulation in C and Pascal, control arrays in VB, hardware IO for embedded
systems (including hardware access on the PC before the PC was virtualised),
caching data for speed and stability in Access before collections and
disconnected recordsets became available, streaming of persistent data in
Pascal and BASIC, passing data between processes (VB to C++), MAX,AVERAGE
and other statistical functions in FORTRAN (a language that did not support
SQL), and embedded strings in a language that supported neither streaming
nor resource files. That's all I can think of, there is more, but I think
that's representative of my career :~)

(david)

 

[david epsom dot com dot au]

filenames via a loop with the Dir function -- I do this when I know

:~) As it happens, for that we use string concatenation instead of
an Array! I didn't write that bit of code for our application, and
I won't try to guess what it would have looked like if I had, but
for the person who did it, dynamically resizing a string came more
naturally than dynamically resizing an array :~)

(david)

 

[Fred Boer]

Dear Marsh:

The challenges never stop, do they Fred? ;-)

Thankfully, no!

A Database System has almost no need for arrays.

Perhaps that was why I was having trouble imagining how they would commonly
be used...

place where an array is appropriate (probably too advanced
for a beginner class) is when managing a combo or list box
RowSource with a callback function.

Callback functions? I think I'll leave that challenge alone for now, (he

time and their even more limited attention span

You know my students?

Good luck in your new career ;-))

Just a new facet in the shining diamond that is my working life!

Cheers!
Fred

 

[Fred Boer]

Thanks Allen! I'll probably use your code as a nice example...

Fred

 

[Fred Boer]

Thanks, Paul! A virtual deck of cards, eh? Was that a *paying* job? <g>

Cheers!
Fred

 

[Fred Boer]

Dear David:

oops.... when I said (sorry) I meant that as an apology for
restating the obvious! Not as a satirical aside :~(

And *I'm* sorry to have misinterpreted you! <embarrassed smile>

Thanks for your detailed response! I've found all of the responses to my
question *very* helpful, and I'm sure my students will benefit as well...
(whether they like it or not! <g>)

All the best!
Fred

 

[onedaywhen]

Yes, I know this is an Access development group, and I'm not at all sorry
that Access is a database product; if it were a spreadsheet I'd find it
horribly boring! <g>

Here's a quote from Chip Pearson, Excel MVP (hope you don't find it
too horribly boring):

"VB/VBA are not quite the same as arrays in C/C++. Even though they
may look and feel quite the same to the programmer, they are
different. In VB/VBA you are actually using something called a
SAFEARRAY. A SAFEARRAY is actually a header structure... This
structure (or Type in VB-speak) defines the array and contains
pointers (and pointers to pointers) to the actual data that we in VB
see as actual data."

So even a basic VBA array is more of a complex object than it may
appear.

--

 

[Albert D. Kallal]

Here is some code from ms-access that I used for analyzing and playing black
jack:

Type OneHand
FirstCard As Integer
SecondCard As Integer
AceCount As Integer
BlackJack As Integer
bet As Integer
HandTotal As Integer
TotalBet As Single
End Type

Public Players(7) As OneHand ' the 7 players
Public Dealer As OneHand ' the dealer
Dim SplitsHands(14) As OneHand
Dim SplitPtr As Integer
Dim BlankHand As OneHand ' used to "zero" a hand

Dim Deck(208) As Integer ' our 4 decks

You can see how usefull The above arrays are.

With the above, the code to deal out cards is:

'Give One card to the 7 spots, and setup the player bet

For Spotptr = 1 To 7
Players(Spotptr) = BlankHand 'zero his info & set bet
Players(Spotptr).FirstCard = NextCard(Players(Spotptr))
Players(Spotptr).bet = BlankHand.bet
AmountBet = AmountBet + BlankHand.bet ' running total of amounts bet
Next Spotptr

 

[Tim Ferguson]

An array is a simple basic way of looking at
a data set, and even if it is now of limited use to a developer,

Oh come on: that is not sensible. It's like saying the wheel has had
it's day because now we have table lamps!

Situations where someone might use a simple array: If you LACK other
language structures, or if you need low-overhead, low-memory, and
fast-access, or if your data is natively indexed numerically, is
static and multi-dimensioned, or if your data is so semantically
simple that you can't be bothered with another structure, or if your
language maps another data structure onto a simple array

.... or stacks or queues or dequeues, or heaps or you want to sort stuff or
index stuff or it's irregularly shaped (we don't have too many triangular
tables...).

Then there is stuff that VBA won't let you do any other way. The Forms
collection, for example, won't let you open the same form twice, so the
only practical way to do that is to have an array of Form objects. And so
on.

B Wishes


Tim F

 

[david epsom dot com dot au]

"VB/VBA are not quite the same as arrays in C/C++. Even

It follows from the dynamically resizable arrays, which
follow from the definition of BASIC as an interpreted
language. BASIC could not have statically allocated
memory.

Since the array is not statically allocated, it is similar
to using Heap Allocation in C - and even closer to using
a fully managed memory class in C++.

(david)

PS:

BASIC was not defined as an interpreted language just
because the developers wanted JAVA but were to primitive
to achieve that. The idea of an interpreted language
derived partly from ideas about what 'robots' and
'computers' should do.

Beginner's All-purpose Symbolic Instruction Code was
conceived as a way of 'talking' to a computer. The
computer responded as you 'talked' by doing syntax
checking, and you could either batch instructions
and 'RUN' (similar to 'GO' in T-SQL), or run interactively
from the command line. These ideas were at the cutting
edge of computer (hardware and software) development
at the time.

The computer world has taken a bit of a self-indulgent
diversion through C in the last 10 years, but it has
gradually edged back towards sensible ideas, and despite
all the insults C# includes many of the important ideas
from Pascal, Modula, and BASIC, including, from BASIC,
the inclusion of Memory Management and a fully managed
String Class.