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

Toles funciones de Calc han espresase colos nomes n'inglés.


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.

Nome de la función de Calc

Nome del serviciu UNO

INT.ACUM

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

INT.ACUM.V

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

AMORTIZ.PROGRE

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

AMORTIZ.LIN

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.A.DEC

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

BIN.A.HEX

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

BIN.A.OCT

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

COMPLEXU

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

CONVERTIR

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

CUPON.DIAS.L1

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

CUPON.DIAS

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

CUPON.DIAS.L2

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

CUPON.FECHA.L2

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

CUPON.NUM

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

CUPON.FECHA.L1

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

PAGU.INT.ENTE

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

PAGU.PRINC.ENTE

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

DEC.A.BIN

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

DEC.A.HEX

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

DEC.A.OCT

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

DELTA

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

TASA.DESC

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

MONEDA.DEC

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

MONEDA.FRAC

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

DURACION

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

FECHA.MES

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

INT.EFEUTIVU

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

FIN.MES

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

FUNERROR

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

FUN.ERRU.COMPL

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

FACT.DOBLE

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

VF.PLAN

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

M.C.D

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

MAYOR.O.IGUAL

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

HEX.A.BIN

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

HEX.A.DEC

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

HEX.A.OCT

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

IM.ABS

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

IMAXINARIU

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

IM.ANGULO

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

IM.CONXUGADA

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

COSIM

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

IM.DIV

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

IM.EXP

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

IM.LN

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

IM.LOG10

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

IM.LOG2

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

IM.POT

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

IM.PRODUCT

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

IM.REAL

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

IMSEC

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

IMSECH

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

SENUIM

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

IMSINH

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

IM.RAIZ2

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

IM.SUSTR

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

IM.SUM

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

IMTAN

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

TASA.INT

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

ESPAR

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

ESIMPAR

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

M.C.M

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

DURACION.MODIF

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

REDOND.MULT

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

MULTINOMIAL

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

DIAS.LAB

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

TASA.NOMINAL

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

OCT.A.BIN

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

OCT.A.DEC

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

OCT.A.HEX

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

PRECIU.PER.IRREGULAR.1

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

RENDTO.PER.IRREGULAR.1

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

PRECIU.PER.IRREGULAR.2

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

RENDTO.PER.IRREGULAR.2

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

PRECIU

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

PRECIU.DESCUENTU

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

PRECIU.VENCIMIENTU

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

COCIENTE

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

ALEATORIU.ENTE

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

CANTIDÁ.RECIBIDA

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

SUMA SERIES

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

RAIZ2PI

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

LLETRA.DE.TES.EQV.A.BONO

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

LLETRA.DE.TES.PRECIU

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

LLETRA.DE.TES.RENDTO

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

NÚM.SELMANA

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

DIA.LAB

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

TIR.NON.PER

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

VNA.NON.PER

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

FRAC.AÑU

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

RENDTO

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

RENDTO.DESC

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

RENDTO.VENCTO

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.

Nome de la función de Calc

Nome del serviciu UNO

DÍASENMES

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

DÍASENAÑO

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

MESES

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

SELMANES

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

AÑOS

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

ROT13

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

SEMANASENAÑO

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.

Nome de la función de Calc

Nome del serviciu UNO

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


Please support us!