Using Calc Functions in Macros

In addition to the native BASIC functions, you can call Calc functions in your macros and scripts and set Calc functions in cell formulas.

Aufrufen von internen Calc-Funktionen in Basic

Verwenden Sie die Funktion CreateUNOService, um auf den Dienst com.sun.star.sheet.FunctionAccess zuzugreifen.

Beispiel:

The example below creates a function named MyVlook that calls the VLOOKUP Calc function over a data array passed as argument and returns the value found by the function.


    Function MyVlook(Lookup, DataArray As Object, Index As Integer, SortedRangeLookup as Byte)
        Dim oService As Object
        Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")
        ' Always use the function name in English
        MyVlook = oService.callFunction("VLOOKUP", Array(Lookup, DataArray, Index, SortedRangeLookup))
    End Function
  

The macro below presents an example of how the MyVlook function can be called. If first creates a 5-by-2 data array and then calls the function MyVlook and shows the returned value using MsgBox.


    Sub CallingMyVlook()
        ' Creates a 5 by 2 array and fills it with data
        Dim myData(1 to 5, 1 to 2) as Variant
        myData(1, 1) = 1 : myData(1, 2) = "Strongly disagree"
        myData(2, 1) = 3 : myData(2, 2) = "Disagree"
        myData(3, 1) = 5 : myData(3, 2) = "Undecided"
        myData(4, 1) = 7 : myData(4, 2) = "Agree"
        myData(5, 1) = 9 : myData(5, 2) = "Strongly agree"
        ' Looks up the data array
        Dim result as String
        result = MyVlook(4, myData, 2, 1)
        ' Shows the message "Disagree"
        MsgBox result
    End Sub
  

Setting Cell Formulas Containing Internal Calc Functions

Use the formula text string to add a formula to a spreadsheet cell.

note

All Calc functions must be expressed with their English names.


Beispiel:


Sub AssignFormulaToCell
REM Add a formula to cell A1. Function name must be in English.
    oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
    oCell.Formula = "=SUM(B1:B10)"
REM Cell A1 displays the localized function name
End Sub

Aufrufen von Add-In-Berechnungsfunktionen in BASIC

Die Calc-Add-In-Funktionen befinden sich im Dienst com.sun.star.sheet.addin.Analysis.

Beispiel:


REM Example calling Add-in function SQRTPI
Function MeineWURZELPI(arg as double) as double
   Dim oService as Object
   oService = createUNOService("com.sun.star.sheet.addin.Analysis")
   MeineWURZELPI = oService.getSqrtPi(arg)
End Function

Setting Cell Formulas with Add-In Functions

The Add-In function must be expressed by its UNO service name.

Beispiel:


Sub AssignAddInFormulaToCell
REM Add an Add-In formula to cell A1. Function name is the UNO service name.
    oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
    oCell.Formula = "=com.sun.star.sheet.addin.Analysis.getBin2Dec(B1)"
REM Cell A1 displays the localized function name
End Sub

Add-In Functions UNO service Names

The table below presents a list of all Calc Add-In functions and their respective UNO service names.

Calc Function name

UNO service name

AUFGELZINS

com.sun.star.sheet.addin.Analysis.getAccrint

AUFGELZINSF

com.sun.star.sheet.addin.Analysis.getAccrintm

AMORDEGRK

com.sun.star.sheet.addin.Analysis.getAmordegrc

AMORLINEARK

com.sun.star.sheet.addin.Analysis.getAmorlinc

BESSELI

com.sun.star.sheet.addin.Analysis.getBesseli

BESSELJ

com.sun.star.sheet.addin.Analysis.getBesselj

BESSELK

com.sun.star.sheet.addin.Analysis.getBesselk

BESSELY

com.sun.star.sheet.addin.Analysis.getBessely

BININDEZ

com.sun.star.sheet.addin.Analysis.getBin2Dec

BININHEX

com.sun.star.sheet.addin.Analysis.getBin2Hex

BININOKT

com.sun.star.sheet.addin.Analysis.getBin2Oct

KOMPLEXE

com.sun.star.sheet.addin.Analysis.getComplex

UMRECHNEN

com.sun.star.sheet.addin.Analysis.getConvert

ZINSTERMTAGVA

com.sun.star.sheet.addin.Analysis.getCoupdaybs

ZINSTERMTAGE

com.sun.star.sheet.addin.Analysis.getCoupdays

ZINSTERMTAGNZ

com.sun.star.sheet.addin.Analysis.getCoupdaysnc

ZINSTERMNZ

com.sun.star.sheet.addin.Analysis.getCoupncd

ZINSTERMZAHL

com.sun.star.sheet.addin.Analysis.getCoupnum

ZINSTERMVZ

com.sun.star.sheet.addin.Analysis.getCouppcd

KUMZINSZ

com.sun.star.sheet.addin.Analysis.getCumipmt

KUMKAPITAL

com.sun.star.sheet.addin.Analysis.getCumprinc

DEZINBIN

com.sun.star.sheet.addin.Analysis.getDec2Bin

DEZINHEX

com.sun.star.sheet.addin.Analysis.getDec2Hex

DEZINOKT

com.sun.star.sheet.addin.Analysis.getDec2Oct

DELTA

com.sun.star.sheet.addin.Analysis.getDelta

DISAGIO

com.sun.star.sheet.addin.Analysis.getDisc

NOTIERUNGDEZ

com.sun.star.sheet.addin.Analysis.getDollarde

NOTIERUNGBRU

com.sun.star.sheet.addin.Analysis.getDollarfr

LAUFZEIT

com.sun.star.sheet.addin.Analysis.getDuration

EDATUM

com.sun.star.sheet.addin.Analysis.getEdate

EFFEKTIV

com.sun.star.sheet.addin.Analysis.getEffect

MONATSENDE

com.sun.star.sheet.addin.Analysis.getEomonth

GAUSSFEHLER

com.sun.star.sheet.addin.Analysis.getErf

GAUSSFKOMPL

com.sun.star.sheet.addin.Analysis.getErfc

ZWEIFAKULTÄT

com.sun.star.sheet.addin.Analysis.getFactdouble

ZW2

com.sun.star.sheet.addin.Analysis.getFvschedule

GGT

com.sun.star.sheet.addin.Analysis.getGcd

GGANZZAHL

com.sun.star.sheet.addin.Analysis.getGestep

HEXINBIN

com.sun.star.sheet.addin.Analysis.getHex2Bin

HEXINDEZ

com.sun.star.sheet.addin.Analysis.getHex2Dec

HEXINOKT

com.sun.star.sheet.addin.Analysis.getHex2Oct

IMABS

com.sun.star.sheet.addin.Analysis.getImabs

IMAGINÄRTEIL

com.sun.star.sheet.addin.Analysis.getImaginary

IMARGUMENT

com.sun.star.sheet.addin.Analysis.getImargument

IMKONJUGIERT

com.sun.star.sheet.addin.Analysis.getImconjugate

IMCOS

com.sun.star.sheet.addin.Analysis.getImcos

IMCOSHYP

com.sun.star.sheet.addin.Analysis.getImcosh

IMCOT

com.sun.star.sheet.addin.Analysis.getImcot

IMCOSEC

com.sun.star.sheet.addin.Analysis.getImcsc

IMCOSECHYP

com.sun.star.sheet.addin.Analysis.getImcsch

IMDIV

com.sun.star.sheet.addin.Analysis.getImdiv

IMEXP

com.sun.star.sheet.addin.Analysis.getImexp

IMLN

com.sun.star.sheet.addin.Analysis.getImln

IMLOG10

com.sun.star.sheet.addin.Analysis.getImlog10

IMLOG2

com.sun.star.sheet.addin.Analysis.getImlog2

IMPOTENZ

com.sun.star.sheet.addin.Analysis.getImpower

IMPRODUKT

com.sun.star.sheet.addin.Analysis.getImproduct

IMREALTEIL

com.sun.star.sheet.addin.Analysis.getImreal

IMSEC

com.sun.star.sheet.addin.Analysis.getImsec

IMSECHYP

com.sun.star.sheet.addin.Analysis.getImsech

IMSIN

com.sun.star.sheet.addin.Analysis.getImsin

IMSINHYP

com.sun.star.sheet.addin.Analysis.getImsinh

IMWURZEL

com.sun.star.sheet.addin.Analysis.getImsqrt

IMSUB

com.sun.star.sheet.addin.Analysis.getImsub

IMSUMME

com.sun.star.sheet.addin.Analysis.getImsum

IMTAN

com.sun.star.sheet.addin.Analysis.getImtan

ZINSSATZ

com.sun.star.sheet.addin.Analysis.getIntrate

ISTGERADE

com.sun.star.sheet.addin.Analysis.getIseven

ISTUNGERADE

com.sun.star.sheet.addin.Analysis.getIsodd

KGV

com.sun.star.sheet.addin.Analysis.getLcm

MLAUFZEIT

com.sun.star.sheet.addin.Analysis.getMduration

VRUNDEN

com.sun.star.sheet.addin.Analysis.getMround

POLYNOMIAL

com.sun.star.sheet.addin.Analysis.getMultinomial

NETTOARBEITSTAGE

com.sun.star.sheet.addin.Analysis.getNetworkdays

NOMINAL

com.sun.star.sheet.addin.Analysis.getNominal

OKTINBIN

com.sun.star.sheet.addin.Analysis.getOct2Bin

OKTINDEZ

com.sun.star.sheet.addin.Analysis.getOct2Dec

OKTINHEX

com.sun.star.sheet.addin.Analysis.getOct2Hex

UNREGERKURS

com.sun.star.sheet.addin.Analysis.getOddfprice

UNREGERREND

com.sun.star.sheet.addin.Analysis.getOddfyield

UNREGLEKURS

com.sun.star.sheet.addin.Analysis.getOddlprice

UNREGLEREND

com.sun.star.sheet.addin.Analysis.getOddlyield

KURS

com.sun.star.sheet.addin.Analysis.getPrice

KURSDISAGIO

com.sun.star.sheet.addin.Analysis.getPricedisc

KURSFÄLLIG

com.sun.star.sheet.addin.Analysis.getPricemat

QUOTIENT

com.sun.star.sheet.addin.Analysis.getQuotient

ZUFALLSBEREICH

com.sun.star.sheet.addin.Analysis.getRandbetween

AUSZAHLUNG

com.sun.star.sheet.addin.Analysis.getReceived

POTENZREIHE

com.sun.star.sheet.addin.Analysis.getSeriessum

WURZELPI

com.sun.star.sheet.addin.Analysis.getSqrtpi

TBILLÄQUIV

com.sun.star.sheet.addin.Analysis.getTbilleq

TBILLKURS

com.sun.star.sheet.addin.Analysis.getTbillprice

TBILLRENDITE

com.sun.star.sheet.addin.Analysis.getTbillyield

KALENDERWOCHE

com.sun.star.sheet.addin.Analysis.getWeeknum

ARBEITSTAG

com.sun.star.sheet.addin.Analysis.getWorkday

XINTZINSFUSS

com.sun.star.sheet.addin.Analysis.getXirr

XKAPITALWERT

com.sun.star.sheet.addin.Analysis.getXnpv

BRTEILJAHRE

com.sun.star.sheet.addin.Analysis.getYearfrac

RENDITE

com.sun.star.sheet.addin.Analysis.getYield

RENDITEDIS

com.sun.star.sheet.addin.Analysis.getYielddisc

RENDITEFÄLL

com.sun.star.sheet.addin.Analysis.getYieldmat