If you copy and paste macros, you may get a security warning. Adjust macro security settings by going to Tools→Options→LibreOffice→Security.
There may be an issue with curly quotes vs. straight quotes. You may get an error message from copy and pasting a program from the internet to your LibreOffice Calc Basic editor. If this happens retype the quotes in you editor.
There are only a few Basic functions, but there are many Worksheet functions. We can turn a Worksheet function into a Basic custom function. Three examples are provided here.
First we create the Basic custom function MYACOSH() from the Worksheet function ACOSH(). For many of your needs, for functions with a single parameter (or argument) just substitute the name of your worksheet function for ACOSH() to create a Basic custom function.
Sub main1()
MsgBox MYACOSH(1.0)
End Sub
Function MYACOSH(byVal x As Double) as Double
Dim oFunction As Variant
oFunction = createUnoservice("com.sun.star.sheet.FunctionAccess")
Dim arg(0) as Variant
Dim result as Double
arg(0) = x
result = oFunction.callFunction("ACOSH", arg())
MYACOSH = result
End function
Second we create a Basic Custom function MYFDIST() from the Worksheet function FDIST(). FDIST(x; int1; int2) has parameters int1 and int2, which are positive integers, are the degrees of freedom parameters for the F-distribution. x must be greater than or equal to 0. In our example FDIST(x; 2; 3).
Third we create MYIF() from IF(). IF Returns one of two values, depending on a test condition. In our example we have IF(x>7; Larger than 7; Under7)
Option Explicit
Sub main2()
msgbox(MYFDIST(0.1))
msgbox(MYIF(6))
End Sub
function MYFDIST (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 REM Degree of freedom
aArgument(2)=3 REM Degree of freedom
result = oFunction.callFunction( "FDIST", aArgument() )
MYFDIST = result
end function
function MYIF (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. Conversion needed.
aArgument(1)="Larger than 7"
aArgument(2)="Under 7"
result = oFunction.callFunction( "IF", aArgument() )
MYIF = result
end function
Table of Contents
Ch1-Introduction
Ch2-Loops
Ch3-If Statements
Ch4-Functions
Ch5-Subs & Functions
Ch6-Read & Write
Ch7-Operators
Ch8-Built-in Functions
Ch9-Built-in Examples
Ch10-Debugging
Ch11-Running Subs
Ch12-Sample Programs
Ch13-WS Formulas
Ch14-WS Functions
Ch15-Calc Help Page
