From ed5640d8b587fbcfed7dd7967f3de04b37a76f26 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sun, 7 Apr 2024 11:06:44 +0200 Subject: Adding upstream version 4:7.4.7. Signed-off-by: Daniel Baumann --- .../source/text/sbasic/shared/calc_functions.xhp | 1037 ++++++++++++++++++++ 1 file changed, 1037 insertions(+) create mode 100644 helpcontent2/source/text/sbasic/shared/calc_functions.xhp (limited to 'helpcontent2/source/text/sbasic/shared/calc_functions.xhp') diff --git a/helpcontent2/source/text/sbasic/shared/calc_functions.xhp b/helpcontent2/source/text/sbasic/shared/calc_functions.xhp new file mode 100644 index 000000000..fa495681f --- /dev/null +++ b/helpcontent2/source/text/sbasic/shared/calc_functions.xhp @@ -0,0 +1,1037 @@ + + + + + + + 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 + + +
+
+
+ +
+ + -- cgit v1.2.3