Arrays in OpenOffice are neither simple nor easy to understand. There are many pitfalls and counter intuitive aspects to them, some of which can be avoided, but many not. Try to avoid getting frustrated and remember it's probably not your fault, OpenOffice arrays truly are insidious little creatures.

Creating Arrays

There are two different ways to create an array variable in basic: use the Dim syntax, or the Array function.

Array is easier to understand, it simply takes a list of values and creates an array from them. These values can be whatever type you what: strings, numbers, etc...

myArray = Array( 1, "two", 3.333 )

Dim is a little more powerful as it allows you to create an array without filling in the values first -- that is, you can say what the values are later.

	Dim myArray( 1 to 3 )
	myArray(1) = 1
	myArray(2) = "two"
	myArray(3) = 3.33

The Dim method takes a lower and upper bound to your array. This may be useful since sometimes you don't wish to start at 1.

	Dim myArray( 5 to 7 )
	myArray(5) = 1
	myArray(6) = "two"
	myArray(7) = 3.33

This creates a problem however when you wish to look at the values in an array, since you don't actually know the starting and ending index. This leads into the next section.

There is an additional form of Dim which takes only a single argument. This simply sets the lower bound to 0. While this may seem okay, it is very illogical and will eventually break your code, since you'll eventually write Dim myArray( 2 ) and expect you have an array with 2 elements, though your array is actually 3 long. Avoid this notation, it is not helpful!

Enumerating Arrays

Obviously if you have an array you'll want to see what is inside of it. In other languages you would simply get the length of the array and starting from 0, or 1, look at each element in turn. In OpenOffice however, as noted previously, arrays don't need to start at any particular index!

Thus you need to determine what the first index is, and the last index in order to get the length. Here is an example that will print out all the items in an array.

[Sub PrintArray( Array )
	Length = UBound(Array) - LBound(Array) + 1
	
	For i = 0 to Length-1
		Print Array(i + LBound(Array))
	Next i
End Sub

Function Sample( )
	Dim myArray( 5 to 7 )
	myArray(5) = 1
	myArray(6) = "two"
	myArray(7) = 3.33
	
	PrintArray( myArray() )
End Function

UBound returns the highest numbered element in the array whereas LBound returns the lowest numbered element. If you want to get the length you perform the subtraction and add 1.

If you are new to programming, or otherwise don't understand why you need to add 1, then work out a few examples on paper, using various ranges of array indexes, and it should become apparent. Note: It is of course vital that you understand why, otherwise looping and other constructs will also not make sense.

The loop could be from 1 to Length, but since we need to add it to the lower bound always, it is better to start at 0.

The Sample function is just there to show how the PrintArray function is working. It is important to notice the myArray() syntax: it has parenthesis on the end of the name, this is required if you wish to pass arrays to other functions.

Multi-Dimensional Arrays

Adding a second dimension to your array is fairly straight forward, as is enumerating the elements of a multidimensional array. You use the same functions but with extra arguments.

	Dim myArray( 1 to 3, 1 to 2 )
	myArray(1,1) = 1
	myArray(2,1) = "two"
	myArray(3,1) = 3.33
	myArray(1,2) = 100
	myArray(2,2) = "two hundred"
	myArray(3,2) = 333

Notice that both the Dim command and the indexing onf myArray simply add a second argument. The above creates a two-dimensional array, also known as a matrix and used heavily in Calc.

If you wish to enumerate the elements in this matrix, you'll need slight modification to the PrintArray function.

Sub PrintArray( Array )
	LengthA = UBound(Array,1) - LBound(Array,1) + 1
	LengthB = UBound(Array,2) - LBound(Array,2) + 1
	
	For i = 0 to LengthA-1
		For j = 0 to LengthB-1
			Print Array(i + LBound(Array,1), j + LBound(Array,2) )
		Next j
	Next i
End Sub

Function Sample( )
	Dim myArray( 1 to 3, 1 to 2 )
	myArray(1,1) = 1
	myArray(2,1) = "two"
	myArray(3,1) = 3.33
	myArray(1,2) = 100
	myArray(2,2) = "two hundred"
	myArray(3,2) = 333
	
	PrintArray( myArray() )
End Function

UBound and LBound take an extra parameter which states of which dimension you wish to get the bound. Note that the first dimension is 1, not 0.

Pay particular attention to the ordering of the indices specified on myArray. It is logical, the first dimension comes first, then the second dimension, but this has the effect that in calc indices have the form myArray( row; column ) whereas one would probably expect the opposite ordering (which is how cells are referenced by the user, that is C3 and not 3C). You will need to understand and get used to this opposite ordering.