Passing an Array

Many of the standard functions accept ranges as their input, such as =SUM(B1:B3). You can also make macro functions that accept such ranges, but it isn't that obvious.

We will write a volume function which calculates the volume for an object with any number of sides.

Function Volume( Lengths As Variant )
	' determine if we have an array, if we don't then we have an error
	if NOT IsArray( Lengths ) then
		Err = 519
		Volume = "#VALUE"	
	else
		' calculate the size of the provided array (range from lower bound to upper bound)
		Dim length as Integer
		length = UBound(Lengths) - LBound(Lengths)

		' assign the first value to the starting value
		Dim result As Double
		result = Lengths( LBound( Lengths ), 1 )
				
		' iterate through remaing values to get volume
		For i = 1 to length
			result = result * Lengths( LBound( Lengths ) + i, 1 )
		Next i

		' return the resulting volume	
		Volume = result
	end if
End Function

Then create a spreadsheet with three values: B1:57,B2:62,B3:19.2 and set the formula B7:=VOLUME(B1:B3). You should obtain the result 67852.8

This only works when the selected range is a vertical series of cells. The array layout is always an array of rows. Be very careful about the indices for arrays, since they can start at any value, and often start from 1, in the above loop since the For loop includes length it works as desired and doesn't miss the last item (although the assignment to length is actually one less than the original array length)

Returning an Array

Converse to the above situation now you want to create a function which the return value can be given to a Sum command and have all the values summed. The following function creates a Fibonnaci series of numbers.

' Return the sequence of fibonnaci numbers
' assume that count >=2 is to make this code simpler
Function Fibonnaci( Count As Integer )
	Dim result( 1 to Count, 1 ) As Double
	result( 1, 1 ) = 0
	result( 2, 1 ) = 1
	
	For i = 3 to Count
		result( i, 1 ) = result( i - 2, 1 ) + result( i - 1, 1 )
	Next i
	
	Fibonnaci = result()
End Function

To test it you can write the formula =SUM(FIBONNACI(5)) and you should get the value 7.

A Double is used to declare the result rather than an Integer or Long, because both of them overflow fairly easily in the Fibonnaci series. Replace Double with Long and do =SUM(FIBONNACI(50)) and you can see the overflow error for yourself.

Passing and Returning an Array

This example both takes a series of arrays and returns a result array. It functions both with multiple values or single values, but be careful that the data must appear such that Length, Width, and Height are each columns, and not rows.

' calculates the volumes of many boxes at once.  The Length,Width, Height parameters
' are supported as single values, or as arrays: where each one is a column of data,
' it will not work if you pass a row of data as one of those parameters
Function BoxVolumes( Length As Variant, Width As Variant, Height As Variant )
	' in the case where called with only one value
	if NOT IsArray( Length ) then
		BoxVolumes = BoxVolume( Length, Width, Height )		
	else
		' calculate the size of the provided array (range from lower bound to upper bound)
		Dim reslen as Integer
		reslen = UBound(Length) - LBound(Length) + 1

		' create the result array, where all results will be stored
		Dim result( reslen, 1 ) As Double
				
		' iterate through values to get volumes
		' be careful of indices starting at 1, not 0!
		For i = 0 to (reslen-1)
			result( i + 1, 1 ) = BoxVolume( Length( LBound(Length) + i, 1 ),_
				Width( LBound(Width) + i, 1 ),_
				Height( LBound(Height) + i, 1 ) )
		Next i

		' return the resulting volumes
		BoxVolumes = result()
	end if
End Function

Multidimensional Arrays

In the previous examples you notice the odd array syntax that is giving two numbers: the first one is the row, and the second one is the column. This is because all data passed from the spreadsheet to your function is always multidimensional, even if the number of columns is 1.

Warning: The previous functions won't work when selecting data in a row, rather than in a column, nor will they work if you select a multi-column region. This is because they are expecting data in a columnar format only (many rows, 1 column).

The following function helps when dealing with different styles of selection (row, column, matrix). It takes all the input data and returns it in a single easy to use flat array.

' Converts a matrix of values into a single array of the same values
' This is useful to simplify processing of some data from a spreadsheet
Function FlattenMatrix( Values as Variant )
	LengthA = UBound( Values, 1 ) - LBound( Values, 1 ) + 1
	LengthB = UBound( Values, 2 ) - LBound( Values, 2 ) + 1
	
	FlatLength = (LengthA * LengthB)
	Dim newValues( 0 to (FlatLength-1) ) As Variant
	For i = 0 to LengthA-1
		For j = 0 to LengthB-1
			newValues( i * LengthB + j ) = Values( i + LBound(Values,1), j + LBound( Values,2) )
		Next j
	Next i
	
	FlattenMatrix = newValues()
End Function

If you are calling internal functions this is also very important since you can't directly pass the input values from a spreadsheet to these functions. Note: This is a severe limitation of the system which basically prevents you from calling matrix functions as well. Hopefully it will be fixed at some point.

Here is a simple example of a pass-thru sum function.

Function MySum( Values As Variant )
	'Get the FunctionAccess service to call functions with
	oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
	MySum = oFunctionAccess.callFunction( "Sum", FlattenMatrix( Values() ) ) 
End Function