I’m trying to see if I can make a function like this LOOKUP with linear interpolation for missing values
and I could probably do it using IF(), LOOKUP() etc type functions, but the macro language is totally different from the language used in cells. LOG10(x) exists in cells, but not in macros, for instance. You have to use log(x)/log(10). Why is this? Is there a way to write new functions using the normal cell function syntax?
Hello,
Your question is really all about programming. Writing macros is writing programs. Macros come in different languages. The most visible language to the user in LibreOffice is “Star Basic” written many years ago by Oracle. Not as visible are languages such as C++, Python and Java all which can be used to write macros.
Functions are macros. They simply return information when called upon. When you use LOG10(x) you are just calling a macro (mini program) with a name of LOG10 and a parameter (variable) of ‘x’. The macro uses ‘x’ in a specific manner to return information to the source of the call.
The language for macros may or may not contain ‘key’ words. LOG10(x) is NOT in Star Basic but IS in Python. Also, these key words may or may not be present depending upon the version of the language used.
One does not write macros using ‘cell functions’ but rather cell functions are created by writing macros.
If you do not already know how to write programs, this can be a very steep learning curve. There are also many side roads involved.
Your reference to the link is already a Function. So what you are stating is to write your own function based upon that already written. You must be careful in this respect as there are License restrictions according to the site where you got this from.
You can use any Calc function in a Basic macro. The mechanism is always the same. The examples below show how to do it. Searching for “FunctionAcess” will likely result in further examples.
For to use in Calc write =MYFKT2(3) into the cell, which should result in the text “kleiner oder gleich 7”.
Functions, which you want to use from a Spreadsheet, need to be in the “Standard” library of Basis scripts. Use all capitals in your function names, that works better.
REM ***** BASIC *****
option explicit
Sub Main
msgbox(MYFKT1(0.1))
msgbox(MYFKT2(6))
End Sub
function MYFKT1 (byVal x as double) as double
dim oFunction as variant
oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim aArgument(2) as variant
dim result as double
aArgument(0)=x
aArgument(1)=2
aArgument(2)=3
result = oFunction.callFunction( "FDIST", aArgument() )
MYFKT1 = result
end function
function MYFKT2 (byVal x as double)
dim oFunction as variant
oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
dim aArgument(2) as variant
dim result as variant
aArgument(0)=CDBL(x>7): Rem Calc expects double, Basic provides true boolean. Converting needed.
aArgument(1)="größer als 7"
aArgument(2)="kleiner oder gleich 7"
result = oFunction.callFunction( "IF", aArgument() )
MYFKT2 = result
end function