Using Calc Functions in Macros /text/sbasic/shared/calc_functions.xhp calling Calc function;macros setting Calc function;macros macros;calling Calc function macros;setting Calc function createUNOservice function;calling Calc function API;sheet.addin.Analysis API;sheet.FunctionAccess

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. 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 ACCRINT com.sun.star.sheet.addin.Analysis.getAccrint ACCRINTM 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 BIN2DEC com.sun.star.sheet.addin.Analysis.getBin2Dec BIN2HEX com.sun.star.sheet.addin.Analysis.getBin2Hex BIN2OCT com.sun.star.sheet.addin.Analysis.getBin2Oct COMPLEX com.sun.star.sheet.addin.Analysis.getComplex CONVERT 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 com.sun.star.sheet.addin.Analysis.getDec2Bin DEC2HEX com.sun.star.sheet.addin.Analysis.getDec2Hex DEC2OCT com.sun.star.sheet.addin.Analysis.getDec2Oct 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 com.sun.star.sheet.addin.Analysis.getEdate EFFECT com.sun.star.sheet.addin.Analysis.getEffect EOMONTH com.sun.star.sheet.addin.Analysis.getEomonth ERF com.sun.star.sheet.addin.Analysis.getErf ERFC com.sun.star.sheet.addin.Analysis.getErfc FACTDOUBLE com.sun.star.sheet.addin.Analysis.getFactdouble FVSCHEDULE com.sun.star.sheet.addin.Analysis.getFvschedule GCD com.sun.star.sheet.addin.Analysis.getGcd GESTEP com.sun.star.sheet.addin.Analysis.getGestep HEX2BIN com.sun.star.sheet.addin.Analysis.getHex2Bin HEX2DEC com.sun.star.sheet.addin.Analysis.getHex2Dec HEX2OCT com.sun.star.sheet.addin.Analysis.getHex2Oct IMABS com.sun.star.sheet.addin.Analysis.getImabs IMAGINARY com.sun.star.sheet.addin.Analysis.getImaginary IMARGUMENT com.sun.star.sheet.addin.Analysis.getImargument IMCONJUGATE 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 IMEXP 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 IMPOWER com.sun.star.sheet.addin.Analysis.getImpower IMPRODUCT 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 IMSQRT com.sun.star.sheet.addin.Analysis.getImsqrt IMSUB com.sun.star.sheet.addin.Analysis.getImsub IMSUM com.sun.star.sheet.addin.Analysis.getImsum IMTAN com.sun.star.sheet.addin.Analysis.getImtan INTRATE com.sun.star.sheet.addin.Analysis.getIntrate ISEVEN com.sun.star.sheet.addin.Analysis.getIseven ISODD com.sun.star.sheet.addin.Analysis.getIsodd LCM com.sun.star.sheet.addin.Analysis.getLcm MDURATION com.sun.star.sheet.addin.Analysis.getMduration MROUND com.sun.star.sheet.addin.Analysis.getMround MULTINOMIAL com.sun.star.sheet.addin.Analysis.getMultinomial NETWORKDAYS com.sun.star.sheet.addin.Analysis.getNetworkdays NOMINAL com.sun.star.sheet.addin.Analysis.getNominal OCT2BIN com.sun.star.sheet.addin.Analysis.getOct2Bin OCT2DEC com.sun.star.sheet.addin.Analysis.getOct2Dec OCT2HEX 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 com.sun.star.sheet.addin.Analysis.getPrice PRICEDISC com.sun.star.sheet.addin.Analysis.getPricedisc PRICEMAT com.sun.star.sheet.addin.Analysis.getPricemat QUOTIENT com.sun.star.sheet.addin.Analysis.getQuotient RANDBETWEEN com.sun.star.sheet.addin.Analysis.getRandbetween RECEIVED com.sun.star.sheet.addin.Analysis.getReceived SERIESSUM com.sun.star.sheet.addin.Analysis.getSeriessum SQRTPI 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 com.sun.star.sheet.addin.Analysis.getWeeknum WORKDAY com.sun.star.sheet.addin.Analysis.getWorkday XIRR com.sun.star.sheet.addin.Analysis.getXirr XNPV com.sun.star.sheet.addin.Analysis.getXnpv YEARFRAC 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

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 DAYSINMONTH com.sun.star.sheet.addin.DateFunctions.getDaysInMonth DAYSINYEAR com.sun.star.sheet.addin.DateFunctions.getDaysInMonth MONTHS com.sun.star.sheet.addin.DateFunctions.getDiffMonths WEEKS com.sun.star.sheet.addin.DateFunctions.getDiffWeeks YEARS com.sun.star.sheet.addin.DateFunctions.getDiffYears ROT13 com.sun.star.sheet.addin.DateFunctions.getRot13 WEEKSINYEAR 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