Calc-functies in macro's gebruiken

Naast de interne BASIC-functies, kunt u ook Calc-functies in uw macro's, scripts en in de formules in de cellen gebruiken.

Interne Calc-functies aanroepen in Basic

Gebruik de functie CreateUNOService om de service com.sun.star.sheet.FunctionAccess te gebruiken.

Voorbeeld:

In dit voorbeeld wordt een functie MyVlook gedefinieerd die de Calc-functie VLOOKUP aanroept met als argument een matrix. De gevonden waarde wordt geretourneerd.


    Function MyVlook(Lookup, DataArray As Object, Index As Integer, SortedRangeLookup as Byte)
        Dim oService As Object
        Set oService = createUnoService("com.sun.star.sheet.FunctionAccess")
        ' Gebruik altijd de Engelse functie-naam
        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()
        ' Maakt en vult een 5x2 matrix
        Dim myData(1 to 5, 1 to 2) as Variant
        myData(1, 1) = 1 : myData(1, 2) = "Daar ben ik het helemaal niet meer eens"
        myData(2, 1) = 3 : myData(2, 2) = "Niet mee eens"
        myData(3, 1) = 5 : myData(3, 2) = "Neutraal"
        myData(4, 1) = 7 : myData(4, 2) = "Mee eens"
        myData(5, 1) = 9 : myData(5, 2) = "Helemaal mee eens"
        'Zoeken in de matrix
        Dim result as String
        result = MyVlook(4, myData, 2, 1)
        ' Uitvoer: "Niet mee eens"
        MsgBox result
    End Sub
  

Interne Calc-functies in formules gebruiken

Gebruik de formule-tekst om een formule in een cel toe te voegen

note

Bij alle Calc-functies moet de Engelse naam worden gebruikt.


Voorbeeld:


Sub AssignFormulaToCell
REM Plaats een formule in cel A1.
    oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
    oCell.Formula = "=SUM(B1:B10)"
REM Cel A1 toont de functie-naam in de eigen taal
End Sub

AddIn Calc-functies in BASIC aanroepen

De functies maken onderdeel uit van de service com.sun.star.sheet.addin.Analysis.

Voorbeeld:


REM Voorbeeld aanroep functie SQRTPI
Function MySQRTPI(arg as double) as double
   Dim oService as Object
   oService = createUNOService("com.sun.star.sheet.addin.Analysis")
   MySQRTPI = oService.getSqrtPi(arg)
End Function

Addin Calc-functies in formules gebruiken

De functie moet worden aangeroepen via de bijbehorende UNO-service-naam.

Voorbeeld:


Sub AssignAddInFormulaToCell
REM Plaatst een addin-functie in cel A1. Als functie-naam wordt de UNO-service-naam gebruikt.
    oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
    oCell.Formula = "=com.sun.star.sheet.addin.Analysis.getBin2Dec(B1)"
REM Cel A1 toont de functie-naam in de eigen taal
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

ACCRINT

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

ACCRINTM

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

AMORDEGRC / AMORDEGRC

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

AMORLINC / AMORLINC

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

BESSELI / BESSEL.I

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

BESSELJ / BESSEL.J

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

BESSELK / BESSEL.K

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

BESSELY / BESSEL.Y

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

BIN2DEC / BIN.2.DEC

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

BIN2HEX / BIN.N.HEX

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

BIN2OCT / BIN.N.OCT

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

COMPLEX / COMPLEX

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

CONVERT / CONVERTEREN

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

COUPDAYBS

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

COUPDAYS

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

COUPDAYSNC

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

COUPNCD

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

COUPNUM

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

COUPPCD

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

CUMIPMT

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

CUMPRINC

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

DEC2BIN / DEC.N.BIN

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

DEC2HEX / DEC.N.HEX

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

DEC2OCT / DEC.N.OCT

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

DELTA / DELTA

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

DISC / ?

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

DOLLARDE / ?

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

DOLLARFR / ?

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

DURATION / ?

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

EDATE/ ZELFDE.DAG

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

EFFECT / EFFECT

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

EOMONTH / LAATSTE.DAG

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

ERF / FOUTFUNCTIE

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

ERFC / FOUT.COMPLEMENT

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

FACTDOUBLE / DUBBELE.FACULTEIT

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

FVSCHEDULE / ?

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

GCD / GGD

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

GESTEP / GROTER.DAN

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

HEX2BIN / HEX.N.BIN

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

HEX2DEC / HEX.N.DEC

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

HEX2OCT / HEX.N.OCT

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

IMABS / C.ABS

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

IMAGINARY / C.IM.DEEL

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

IMARGUMENT / C.ARGUMENT

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

IMCONJUGATE/ C.TOEGEVOEGD

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

IMCOS / C.COS

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

IMCOSH / IMCOSH

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

IMCOT / IMCOT

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

IMCSC / IMCOSEC

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

IMCSCH / IMCOSECHYP

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

IMDIV / C.QUOTIENT

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

IMEXP / C.EXP

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

IMLN / C.LN

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

IMLOG10 / C.LOG10

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

IMLOG2 / C.LOG2

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

IMPOWER / C.MACHT

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

IMPRODUCT / C.PRODUCT

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

IMREAL / C.REEEL.DEEL

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

IMSEC / IMSEC

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

IMSECH / IMSECH

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

IMSIN / C.SIN

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

IMSINH / IMSINH

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

IMSQRT / C.WORTEL

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

IMSUB / C.VERSCHIL

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

IMSUM / C.SOM

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

IMTAN / IMTAN

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

INTRATE / ?

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

ISEVEN / IS.EVEN

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

ISODD / IS.ONEVEN

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

LCM / KGV

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

MDURATION / ?

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

MROUND / AFRONDEN.N.VEELVOUD

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

MULTINOMIAL / MULTINOMIAAL

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

NETWORKDAYS / NETTO.WERKDAGEN

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

NOMINAL / NOMINALE.RENTE

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

OCT2BIN / OCT.N.BIN

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

OCT2DEC / OCT.N.DEC

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

OCT2HEX / OCT.N.HEX

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

ODDFPRICE / ?

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

ODDFYIELD / ?

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

ODDLPRICE / ?

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

ODDLYIELD / ?

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

PRICE / PRIJS.NOM

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

PRICEDISC / PRIJS.DISCONTO

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

PRICEMAT / PRIJS.VERVALDAG

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

QUOTIENT / QUOTIENT

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

RANDBETWEEN / ASELECTTUSSEN

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

RECEIVED / ?

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

SERIESSUM / SOM.MACHTREEKS

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

SQRTPI / WORTEL.PI

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

TBILLEQ / ?

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

TBILLPRICE / ?

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

TBILLYIELD / ?

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

WEEKNUM / WEEKNUMMER

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

WORKDAY / WERKDAG

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

XIRR / ?

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

XNPV / ?

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

YEARFRAC / JAAR.DEEL

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

YIELD / ?

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

YIELDDISC / ?

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

YIELDMAT / ?

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