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.

Example:

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.


Example:


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.

Example:


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.

Example:


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

DOBEŽDAŃ

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

DOBĚŽDAŃP

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

BINDODEC

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

BINDOHEKS

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

BINDOOKT

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

KOMPLEKSNY

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

KONWERTOWAĆ

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

DANTERMDNYSW

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

DANTERMDNY

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

DANTERMDNYPD

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

DANTERMPD

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

DANTERMLIČBA

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

DANTERMPZ

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

KUMDAP

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

KUMKAP

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

DECDOBIN

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

DECDOHEKS

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

DECDOOKT

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

DELTA

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

DISKONTNASADŹBA

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

NOTĚRDEC

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

NOTĚRŁAMK

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

BĚŽNYČAS

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

EDATUM

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

EFEKTIWNY

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

KÓNCMĚSACA

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

ZMYF

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

ZMYFK

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

DWÓJNAFAKULTA

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

PH2

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

NWZD

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

GCYŁALIČBA

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

HEKSDOBIN

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

HEKSDODEC

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

HEKSDOOKT

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

IMABS

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

IMAGINARNY

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

IMARGUMENT

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

IMKONJUGOWANY

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

IMCOS

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

IMKOSHYP

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

IMKOT

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

IMKOSEK

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

IMKOSEKHYP

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

IMDIW

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

IMEKSP

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

IMAPOTENCA

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

IMPRODUKT

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

IMREALNY

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

IMSEK

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

IMSEKHYP

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

IMSIN

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

IMSINHYP

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

IMKORJEŃ

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

IMSUB

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

IMSUMA

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

IMTAN

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

DANSADŹBA

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

JERUNY

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

JENJERUNY

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

NMZD

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

MDURATION

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

MNAKRUŽiĆ

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

POLYNOMIALNY

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

DŹĚŁOWEDNYNETTO

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

NOMINALNY

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

OKTDOBIN

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

OKTDODEC

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

OKTDOHEKS

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

NJEPRAPRĚPŁA

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

NJEPRAPRĚREND

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

NJEPRAPOSPŁA

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

NJEPRAPOSREND

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

PŁAĆIZNA

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

PŁAĆIZNADISK

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

PŁAĆIZNAPŁA

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

KWOCIENT

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

PŘIPADNYWOBWOD

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

DÓSTANY

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

POTENCNYRJAD

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

KORJEŃPI

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

POKLISTEKW

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

PŁAĆIZNAPOKLIST

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

RENDITAPOKLIST

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

KALTYDŽEŃ

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

DŹĚŁOWYDŹEŃ

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

XINTDASA

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

XKAPHÓD

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

DŹĚLNELĚTO

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

RENDITA

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

RENDITADISK

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

RENDITAPŁA

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

DNYWMĚSACU

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

DNYWLĚĆE

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

MĚSACY

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

NJEDŹELE

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

LĚTA

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

ROT13

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

TYDŹENJEWLĚĆE

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


Prošu podpěrajće nas!