Some Background.  It used to be the case that arrays would be a major topic in any introductory computer programming/development course.  Considering the applications development object oriented approach taken in the two Visual Basic courses at Quinnipiac when I teach them, this is not so appropriate.  We were able to develop all sorts programs for working with databases in CS 110 without presenting arrays.  This was mostly due to the fact that Visual Basic contains at least a couple different objects that have more substantive functionality than arrays while still being easy to use.

In this course we could probably never discuss arrays and hardly have any negative impact on your education.  But on the other hand, since arrays are such a common structure in such a wide variety of languages and such a common ground for all computer information systems people it is important to present them to at least some extent.  They also do have their self-standing applicability even when working with databases or spreadsheets in Visual Basic.

Understanding Arrays.  When you format a cell to contain something like a number with a certain number of decimal places, a percentage, currency, date, text or something else you are essentially declaring that cell to be of a certain data type.  The data type of a cell determines the set of operations, storage and potential values that can be used.  

For the present, forget you are working in spreadsheets.  An array is a data structure that stores multiple values of the same data type that can be accessed at some location based on some numbering system.  Everything in the array must have the same data type.

The elements in an array are accessed by some sort of numbering scheme.  For example you could have an array of dates called DentistVisitDates that contains the dates of the last 10 times you visited your dentist.  It might be the case that

Notice several things about the list.  

• The index begins at 0 and ends with 9.  Array indices might begin with 1 and end with 10.  When working in C++ they start at 0 by default.  When working with databases in VisualBasic they also begin at 0.  When working with arrays in spreadsheets and VisualBasic the arrays usually start at 1.

• The most recent dates have the higher indices, a totally unnecessary relationship.  

• We have used a short date format, mm/dd/yy to represent the dates.  

• There is a single index.

As another example you might have an array of the names of the 9 planets in our solar system.

Assuming I remember the names and positions of the planets I have listed them in order of their distance from the sun.

Arrays can have more than one index to denote a location in the array.  The number of independent indices used are considered to give the array a particular number of dimensions.  You can have one-dimensional, two-dimensional or as many dimensional arrays as you want.  It is always good programming/development practice to try to have some sort of appropriate relationship between the indices and the data you are trying to put in the array.

Now consider augmenting the array of the Planets to contain the names of each planet's moons.  Since it is my understanding that Jupiter has the most moons and there are currently considered to be 16 of them we need to be able to associate at least 16 additional names with each element in our array.  We will now define a new array called PlanetMoon with 9 rows and 17 columns to make sure we can contain the name of the planets and all their satellites.  Regardless of the fact that this is not a particularly effective use of storage because of all the blank entries we will now fill in some of the elements.

. . .

But now what if we want to include all of the smaller satellites, either artificial or natural, that each of these planets/moons have?  We could realistically add another dimension to our array to list each of those that meet certain specified criteria.  This would just require some more bookkeeping and organization.

Spreadsheets?  Arrays?  Ultimately a spreadsheet is in many respects structurally nothing but a two dimensional array.  But instead of using purely numbers to determine a location or value in the array a letter and a number are used.  But, on the other hand, the elements in a spreadsheet are not restricted to be of the same type as they are in an array.  So you can see that there is some commonality between the constructs.  But remember this is an entire course in spreadsheets with never enough time to do everything that should be done.  To try and focus such a course on arrays would be ludicrous.  Still arrays have their place!

We will not present arrays in their most general context and will now return our focus to how arrays can be used to enhance spreadsheet processing.  Thus our final statement in this page is to redefine an array within the context of spreadsheets.  In a spreadsheet, an array is a contiguous collection of cells or values of the same data type that is operated on as a group.  So we could have an array of the mortgage payments.  The labels for these payments would need to be composed of two different text arrays.