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.

Calling Internal Calc functions in Basic

Use the CreateUnoService function to access the com.sun.star.sheet.FunctionAccess service.

Exempel:

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. It 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.


Exempel:


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

Calling Add-In Calc Functions in BASIC

The Calc Add-In functions are in the UNO services com.sun.star.sheet.addin.Analysis, com.sun.star.sheet.addin.DateFunctions and com.sun.star.sheet.addin.PricingFunctions.

Exempel:


REM Example calling Add-in function 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

Setting Cell Formulas with Add-In Functions

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

Exempel:


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

UNO Service Names for Analysis Add-In Functions

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

Calc Function name

UNO service name

UPPLRÄNTA

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

UPPLOBLRÄNTA

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

AMORDEGRC

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

AMORLINC

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

BIN.TILL.DEC

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

BIN.TIll.HEX

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

BIN.TILL.OKT

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

KOMPLEX

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

KONVERTERA

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

KUPDAGBB

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

KUPDAGB

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

KUPDAGNK

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

KUPNKD

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

KUPANT

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

KUPFKD

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

KUMRÄNTA

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

KUMPRIS

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

DEC.TILL.BIN

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

DEC.TILL.HEX

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

DEC.TILL.OKT

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

DELTA

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

DISK

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

DECTAL

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

BRÅK

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

LÖPTID

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

EDATUM

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

EFFRÄNTA

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

SLUTMÅNAD

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

FELF

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

FELFK

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

DUBBELFAKULTET

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

FÖRRÄNTNING

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

SGD

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

SLSTEG

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

HEX.TILL.BIN

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

HEX.TILL.DEC

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

HEX.TILL.OKT

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

IMABS

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

IMAGINÄR

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

IMARGUMENT

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

IMKONJUGAT

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

IMCOS

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

IMCOSH

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

IMCOT

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

IMCSC

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

IMCSCH

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

IMDIV

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

IMEUPPHÖJT

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

IMUPPHÖJT

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

IMPRODUKT

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

IMREAL

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

IMSEC

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

IMSECH

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

IMSIN

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

IMSINH

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

IMROT

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

IMDIFF

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

IMSUM

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

IMTAN

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

ÅRSRÄNTA

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

ÄRJÄMN

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

ÄRUDDA

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

MGM

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

MLÖPTID

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

MAVRUNDA

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

MULTINOMIAL

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

NETTOARBETSDAGAR

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

NOMRÄNTA

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

OKT.TILL.BIN

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

OKT.TILL.DEC

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

OKT.TILL.HEX

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

UDDAFPRIS

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

UDDAFAVKASTNING

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

UDDASPRIS

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

UDDASAVKASTNING

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

PRIS

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

PRISDISK

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

PRISFÖRF

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

KVOT

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

SLUMP.MELLAN

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

BELOPP

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

SERIESSUMMA

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

ROTPI

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

SSVXEKV

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

SSVXPRIS

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

SSVXRÄNTA

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

VECKONR

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

ARBETSDAGAR

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

XIRR

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

XNUVÄRDE

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

ÅRDEL

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

NOMAVK

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

NOMAVKDISK

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

NOMAVKFÖRF

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


UNO Service Names for Date Add-In Functions

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

Calc Function name

UNO service name

DAGARIMÅNADEN

com.sun.star.sheet.addin.DateFunctions.getDaysInMonth

DAGARPÅÅRET

com.sun.star.sheet.addin.DateFunctions.getDaysInMonth

ANTALMÅNADER

com.sun.star.sheet.addin.DateFunctions.getDiffMonths

ANTALVECKOR

com.sun.star.sheet.addin.DateFunctions.getDiffWeeks

ANTALÅR

com.sun.star.sheet.addin.DateFunctions.getDiffYears

ROT13

com.sun.star.sheet.addin.DateFunctions.getRot13

VECKORPERÅR

com.sun.star.sheet.addin.DateFunctions.getWeeksInYear


UNO Service Names for Pricing Add-In Functions

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

Calc Function name

UNO service name

OPT_BARRIER

com.sun.star.sheet.addin.PrincingFunctions.getOptBarrier

OPT_PROB_HIT

com.sun.star.sheet.addin.PrincingFunctions.getOptProbHit

OPT_PROB_INMONEY

com.sun.star.sheet.addin.PrincingFunctions.getOptProbInMoney

OPT_TOUCH

com.sun.star.sheet.addin.PrincingFunctions.getOptTouch


Stötta oss!