[Solved] Using Calc built in Array functions in Basic

[Ron Richards]

I would like to be able to call the following Calc Array functions from a macro: MMULT,MINVERSE,TRANSPOSE,MDETERM and MUNIT. Bear in mind I am just starting with Calc, so a practical example would be nice.

[Charlie Young]

Hi Ron. I hadn't tried that before, but it isn't overwhelmingly difficult. I've done the MMULT example in the attached spreadsheet. I have used the spreadsheet for the data input, but I hope it will be clear that arrays within Basic could be used just as well. I set up two quite arbitrary 3x3 matrices (A and B) on Sheet 1, below that I use MMULT as it would normally be used, and below that I show the result of this code - that is, from running TestMatrix:

Code: Select all

Function Sheet_matrixmultiply(A, B)
	Dim svc As Object
	svc = createUnoService("com.sun.star.sheet.FunctionAccess")  
	Sheet_matrixmultiply = svc.callFunction("MMULT",Array(A, B))
End Function

Sub TestMatrix
	Dim Doc As Object
	Dim Sheet As Object
	Dim A As Object
	Dim B As Object
	Dim OutRange As Object
	Dim M
	Dim aRow
	Dim i As Integer
	Dim J As Integer
	Doc = ThisComponent
	Sheet = Doc.Sheets.getByName("Sheet1")
	
	A = sheet.getCellRangeByName("A8:C10")
	B = sheet.getCellRangeByName("A13:C15")
	OutRange = sheet.getCellRangeByName("A23:C25")
	M = Sheet_matrixmultiply(A.DataArray, B.DataArray)
	
	for i = 0 to 2
		aRow = M(i)
		for j = 0 to 2
			OutRange.getCellByPosition(j, i).setValue(aRow(j))
		next j
	next i
	
End Sub

I hope it will be clear from this how to implement the other functions. When I want to do a matrix multiplication in a program, I usually just use the standard idiom (though sometimes built-in routines are more efficient):

Code: Select all

Function MatrixMultiply(A, B)
	Dim ra As Integer
	Dim ca As Integer
	Dim rb As Integer
	Dim cb As Integer
	Dim i As Integer
	Dim j As Integer
	Dim k As Integer
	
	ra = UBound(A, 1)
	ca = UBound(A, 2)
	rb = UBound(B, 1)
	cb = UBound(B, 2)
	
	Dim M(ra, cb) As Double
	
	for i = 1 to ra
		for j = 1 to cb
			M(i, j) = 0
			for k = 1 to ca
				M(i, j) = M(i, j) + A(i, k) * B(k, j)
			next k
		next j
	next i  
	MatrixMultiply = M
End Function

Trusting myself to be sure that the matrices are conformable.

I also have used the idiom for transpose, but I'll leave that as a homework assignment.