Raba funkcij programa Calc v makrih
Poleg domorodnih funkcij BASIC lahko v svojih makrih in skriptih kličete funkcije programa Calc in določite funkcije Calc v formulah celic.
Klicanje internih funkcij programa Calc v Basicu
Uporabite funkcijo CreateUnoService za dostop do storitve com.sun.star.sheet.FunctionAccess.
Spodnji primer kode ustvari funkcijo MyVlook, ki kliče funkcijo VLOOKUP modula Calc z podatkovno matriko, ki je podana kot argument, in vrne vrednost, ki jo funkcija najde.
Function MyVlook(Lookup, DataArray As Object, Index As Integer, SortedRangeLookup as Byte)
Dim oService As Object
Set oService = CreateUnoService("com.sun.star.sheet.FunctionAccess")
' Vedno uporabite angleško ime funkcije
MyVlook = oService.callFunction("VLOOKUP", Array(Lookup, DataArray, Index, SortedRangeLookup))
End Function
Spodnji makro predstavlja primer, kako lahko kličete funkcijo MyVlook. Najprej ustvari matriko 5x2, nato kliče funkcijo MyVlook in izpiše vrnjeno vrednost z uporabi ukaza MsgBox.
Sub CallingMyVlook()
' Ustvari matriko 5 x 2 in jo zapolni s podatki
Dim myData(1 to 5, 1 to 2) as Variant
myData(1, 1) = 1 : myData(1, 2) = "Močno nasprotujem"
myData(2, 1) = 3 : myData(2, 2) = "Nasprotujem"
myData(3, 1) = 5 : myData(3, 2) = "Neodločeno"
myData(4, 1) = 7 : myData(4, 2) = "Podpiram"
myData(5, 1) = 9 : myData(5, 2) = "Močno podpiram"
' Poišče podatkovno matriko
Dim result as String
result = MyVlook(4, myData, 2, 1)
' Pokaže sporočilo "Nasprotujem"
MsgBox result
End Sub
Določanje formul v celicah z internimi funkcijami programa Calc
Uporabite formulo v besedilnem nizu, da dodate formulo v celico preglednice.
Vse funkcije programa Calc je potrebno izraziti z njihovimi angleškimi imeni.
Sub AssignFormulaToCell
REM Dodaj formulo v celico A1. Ime funkcije mora biti v angleščini.
oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
oCell.Formula = "=SUM(B1:B10)"
REM Celica A1 prikaže prevedeno ime funkcije, če prevod obstaja.
End Sub
Klicanje funkcij dodatka programa Calc v Basicu
Funkcije dodatkov programa Calc so v storitvah UNO com.sun.star.sheet.addin.Analysis, com.sun.star.sheet.addin.DateFunctions in com.sun.star.sheet.addin.PricingFunctions.
REM Primer klicanja funkcije dodatka SQRTPI
Function MojSQRTPI(arg as double) as double
Dim oService as Object
oService = CreateUnoService("com.sun.star.sheet.addin.Analysis")
MojSQRTPI = oStoritev.getSqrtPi(arg)
End Function
Določanje formul v celicah s funkcijami dodatka
Funkcijo dodatka je potrebno izraziti z imenom njene storitve UNO.
Sub AssignAddInFormulaToCell
REM Dodaj formulo dodatka v celico A1. Ime funkcije je ime storitve UNO.
oCell = ThisComponent.Sheets.getByIndex(0).getCellRangeByName("A1")
oCell.Formula = "=com.sun.star.sheet.addin.Analysis.getBin2Dec(B1)"
REM Celica A1 prikaže prevedeno ime funkcije, če prevod obstaja.
End Sub
Imena storitev UNO za funkcije dodatka za analizo
Spodnja tabela predstavlja seznam vseh funkcij dodatka za analizo programa Calc in ustrezna imena njihovih storitev UNO.
|
Ime funkcije programa Calc |
Ime storitve UNO |
|---|---|
|
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 |
Imena storitev UNO za funkcije dodatka za datume
Spodnja tabela predstavlja seznam vseh funkcij dodatka Calc za datume in ustrezna imena njihovih storitev UNO.
|
Ime funkcije programa Calc |
Ime storitve UNO |
|---|---|
|
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 |
Imena storitev UNO za funkcije dodatka za ocenjevanje
Spodnja tabela predstavlja seznam vseh funkcij dodatka Calc za ocenjevanje in ustrezna imena njihovih storitev UNO.
|
Ime funkcije programa Calc |
Ime storitve 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 |