Arrays

[Larry]

I wonder if someone could direct me to a simple use of an array. I
can't make any sense out of the material on arrays in Word 97 VBA Help.
I would just like to get a idea of what they are and what a practical
use of them would be.

Larry

 

[Jay Freedman]

I wonder if someone could direct me to a simple use of an array. I
can't make any sense out of the material on arrays in Word 97 VBA
Help. I would just like to get a idea of what they are and what a
practical use of them would be.

Hi, Larry,

Think of an array as a table of values, all of the same data type. In the
simplest case, the table is only one row containing some sequence of cells.
Each cell has an index number, so you can go directly to it. If you define
the table to have two or more rows, then each cell has both a row index
number and a column index number.

As a very simple example, say you always open the same three files in the
morning, and you want to write a macro to automate that. You could just
repeat the same statement three times with different file names,

Documents.Open FileName:="c:\docs\alpha.doc"
Documents.Open FileName:="c:\docs\beta.doc"
Documents.Open FileName:="c:\docs\gamma.doc"

or you could create an array to hold the names and then use a For loop to
open them:

Dim FN(3) As String
Dim index As Integer
FN(1) = "c:\docs\alpha.doc"
FN(2) = "c:\docs\beta.doc"
FN(3) = "c:\docs\gamma.doc"
For index = 1 To 3
Documents.Open FileName:=FN(index)
Next index

or, equivalently,

Dim FN(3) As String
Dim index As Integer
FN(1) = "alpha"
FN(2) = "beta"
FN(3) = "gamma"
For index = 1 To 3
Documents.Open FileName:="c:\docs\" & FN(index) & ".doc"
Next index

Of course, this is overkill for three files, but it starts to make sense
when there are a lot more.

Arrays are also useful for populating list boxes and combo boxes in
userforms, and for sorting a list (if you don't want to insert the list in a
document and use TableSort).

Two-, three-, and greater-dimensional arrays are supported in VBA but are
relatively uncommon -- there just isn't that much need for them in a word
processing application.

 

[Larry]

Jay,

I just got around to seeing your reply now, a week late. I tried the
simple macro to open three documents. That's neat. I'm sure this
method will come in handy some time.

However, I notice the sample macro doesn't actually use the Array
function. What is the difference between using it and not using it?

Thanks,
Larry

 

[Jezebel]

The array function creates an array on the fly, that can be assigned to a
variant or passed as a variant argument. If you're using declared arrays you
don't normally need the Array() function.

You can use:

Dim MyArray as variant
MyArray = Array(1,2,3,4)

This is equivalent to

Dim MyArray(0 to 3)
MyArray(0) = 1
MyArray(1) = 2
....

 

[Jay Freedman]

Hi, Larry,

The concept of "an array" and the Array function are two different
things.

Before I get into the Array function, let me explain that VBA has two
kinds of arrays. One of them is the kind I showed before, where you
use a Dim statement and put one or more numbers in parentheses after a
variable name. That tells VBA to make an array using that variable
name, with the number of elements (numbers, strings, whatever the "As"
clause describes) given by the number in parentheses. So in my
example,
Dim FN(3) As String
says there will be three strings called FN(1), FN(2), and FN(3). For
completeness, I should say that there's also an FN(0), but I didn't
use it. Finally, if you want to change the number of elements in an
array, look up the ReDim statement.

The other kind is all contained within one variable that's of type
Variant. This doesn't have parentheses or a number in its declaration.
The number of elements is determined by what assignment you make to
the variable. This is where the Array function comes in. You could do
this:
Dim foo As Variant
foo = Array(2, 4, 6)
and the single variable foo would contain three elements, the integers
2, 4, and 6. You can still name them with an index as foo(0), foo(1),
and foo(2). [By default, arrays start with index 0. You can change
this by putting the statement
Option Base 1
at the top of your module.]

Since Variants can contain pretty much anything, you can also assign
strings:
strFoo = Array("red", "white", "blue", "green")
One neat trick with this is that you can directly assign this kind of
array to the .List property of a list box or combo box in a userform
instead of using the .AddItem method one-by-one:
ListBox1.List = Array("red", "white", "blue", "green")
will put the four strings into the box.

 

[Larry]

Thanks, will study further.

Larry