# How to write my own function to work in matrix formulas? [closed]

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-20 21:12:12.922233

Do you want to write a macro? In BASIC? Or do you want to make your own build with a function in core?

( 2014-04-16 00:34:51 +0200 )edit

As a user I want to write a macro in BASIC. I do not care about performance and calculations are simple. If it is not feasible I would like to know which technology should be used. I know C++ and perhaps writting add-on based on a good example would do. As you pointed adding function in core should work, but I think it is too heavy for me and not easily portable.

( 2014-04-16 08:31:20 +0200 )edit

Sort by » oldest newest most voted

You can do it using a BASIC macro of type function.

If you call a function with a cell range, this parameter is interpreted as array in BASIC. It is an array of rows, and each row is an array itself. When you assign an array to the function name you can fill a cell range, if you use the function in matrix context by entering it with Ctrl+Shift+Enter. That is the same as with the build-in matrix functions. Beyond that you need to get familiar with the API.

Example, call it e.g as array function {=MYCOLSUM(A1:C2)}, output is a row


function MYCOLSUM (byval matrixIn as variant) as variant
rem causion, BASIC arrays are zero-based
rem cell range arrays are one-based
dim iRow as integer
dim iCol as integer
dim nColCount as integer: nColCount = UBound(matrixIn,2)
dim nRowCount as integer: nRowCount = UBound(matrixIn,1)
dim aColSum(nColCount-1)
dim fSum as double
for iCol = 1 to nColCount
fSum = 0.0
for iRow=1 to nRowCount
fSum = fSum+matrixIn(iRow,iCol)
next iRow
aColSum(iCol-1) = fSum
next iCol
MYCOLSUM = aColSum
end function

more

That it is! Thanks for your prompt answer. As simple as you said, just assign an array to the function name as the return.

Thanks for pointing this active bug although I was aware some of it because it was mentioned in the document 0300CG3-CalcGuideLibO3.pdf. In the details the described impact there was slightly different that calc does not recalculate cells unless you edit them. Your pointed error proved what I was suspecting it is impossible to build independent library of Calc functions.

( 2014-04-16 19:02:00 +0200 )edit

Unfortunetely this my first post and I can not upvote your answer due to lacking in points.

( 2014-04-16 19:07:29 +0200 )edit