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.

Пример:

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.


Пример:


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.

Пример:


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.

Пример:


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

НАКОПДОХОД

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

НАКОПДОХОДПОГАШ

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

АМОРУМ

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

АМОРУВ

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

БЕССЕЛЬ.I

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

БЕССЕЛЬ.J

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

БЕССЕЛЬ.K

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

БЕССЕЛЬ.Y

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

ДВ.В.ДЕС

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

ДВ.В.ШЕСТН

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

ДВ.В.ВОСЬМ

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

КОМПЛЕКСН

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

ПРЕОБР

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

ДНЕЙКУПОНДО

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

ДНЕЙКУПОН

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

ДНЕЙКУПОНПОСЛЕ

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

ДАТАКУПОНПОСЛЕ

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

ЧИСЛКУПОН

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

ДАТАКУПОНДО

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

ОБЩПЛАТ

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

ОБЩДОХОД

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

ДЕС.В.ДВ

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

ДЕС.В.ШЕСТН

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

ДЕС.В.ВОСЬМ

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

ДЕЛЬТА

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

СКИДКА

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

РУБЛЬ.ДЕС

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

РУБЛЬ.ДРОБЬ

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

ДЛИТ

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

ДАТАМЕС

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

ЭФФЕКТ

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

КОНМЕСЯЦА

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

ФОШ

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

ДФОШ

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

ДВФАКТР

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

БЗРАСПИС

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

НОД

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

ПОРОГ

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

ШЕСТН.В.ДВ

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

ШЕСТН.В.ДЕС

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

ШЕСТН.В.ВОСЬМ

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

МНИМ.ABS

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

МНИМ.ЧАСТЬ

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

МНИМ.АРГУМЕНТ

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

МНИМ.СОПРЯЖ

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

МНИМ.COS

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

МНИМ.COSH

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

МНИМ.COT

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

МНИМ.CSC

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

МНИМ.CSCH

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

МНИМ.ДЕЛ

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

МНИМ.EXP

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

МНИМ.LN

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

МНИМ.LOG10

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

МНИМ.LOG2

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

МНИМ.СТЕПЕНЬ

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

МНИМ.ПРОИЗВ

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

МНИМ.ВЕЩ

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

МНИМ.SEC

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

МНИМ.SECH

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

МНИМ.SIN

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

МНИМ.SINH

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

МНИМ.КОРЕНЬ

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

МНИМ.РАЗН

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

МНИМ.СУММ

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

МНИМ.TAN

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

ИНОРМА

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

ЕЧЁТН

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

ЕНЕЧЁТ

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

НОК

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

МДЛИТ

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

ОКРУГЛТ

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

МУЛЬТИНОМ

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

ЧИСТРАБДНИ

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

НОМИНАЛ

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

ВОСЬМ.В.ДВ

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

ВОСЬМ.В.ДЕС

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

ВОСЬМ.В.ШЕСТН

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

ЦЕНАПЕРВНЕРЕГ

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

ДОХОДПЕРВНЕРЕГ

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

ЦЕНАПОСЛНЕРЕГ

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

ДОХОДПОСЛНЕРЕГ

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

ЦЕНА

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

ЦЕНАСКИДКА

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

ЦЕНАПОГАШ

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

ЧАСТНОЕ

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

СЛУЧМЕЖДУ

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

ПОЛУЧЕНО

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

РЯД.СУММ

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

КОРЕНЬПИ

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

РАВНОКЧЕК

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

ЦЕНАКЧЕК

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

ДОХОДКЧЕК

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

НОМНЕДЕЛИ

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

РАБДЕНЬ

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

ЧИСТВНДОХ

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

ЧИСТНЗ

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

ДОЛЯГОДА

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

ДОХОД

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

ДОХОДСКИДКА

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

ДОХОДПОГАШ

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

ДНЕЙВМЕС

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

ДНЕЙВГОДУ

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

МЕСЯЦЫ

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

НЕДЕЛИ

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

ГОДЫ

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

ROT13

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

НЕДЕЛЬВГОДУ

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


Пожалуйста, поддержите нас!