Nors VBA siūlo nemažą integruotų funkcijų asortimentą, ne visada galite rasti būtent tai, ko jums reikia. Laimei, VBA procedūrose taip pat galite naudoti daugumą „Excel“ darbalapio funkcijų. Vienintelės darbalapio funkcijos, kurių negalite naudoti, yra tos, kurios turi lygiavertę VBA funkciją. Pavyzdžiui, negalite naudoti Excel funkcijos RAND (kuri generuoja atsitiktinį skaičių), nes VBA turi lygiavertę funkciją: Rnd.
VBA suteikia „Excel“ darbalapio funkcijas pasiekiamoms per objektą WorksheetFunction, kuris yra programos objekte. Štai pavyzdys, kaip galite naudoti „Excel“ funkciją SUM VBA sakinyje:
Iš viso = Application.WorksheetFunction.SUM (diapazonas ("A1:A12")
Galite praleisti išraiškos dalį Application arba WorksheetFunction. Bet kuriuo atveju VBA išsiaiškina, ką darote. Kitaip tariant, visos šios trys išraiškos veikia lygiai taip pat:
Iš viso = Application.WorksheetFunction.SUM (diapazonas ("A1:A12")
Iš viso = WorksheetFunction.SUM (diapazonas ("A1:A12")
Iš viso = Application.SUM (diapazonas ("A1:A12")
Mano asmeninis pasirinkimas yra naudoti WorksheetFunction dalį, kad būtų visiškai aišku, jog kodas naudoja „Excel“ funkciją.
Darbalapio funkcijų pavyzdžiai
Čia sužinosite, kaip naudoti darbalapio funkcijas savo VBA išraiškose.
Didžiausios vertės nustatymas diapazone
Štai pavyzdys, rodantis, kaip naudoti „Excel“ MAX darbalapio funkciją VBA procedūroje. Ši procedūra rodo didžiausią reikšmę aktyvaus darbalapio A stulpelyje:
Darbalapio funkcijos naudojimas VBA kode.
Sub ShowMax()
Dim TheMax As Double
TheMax = WorksheetFunction.MAX (diapazonas (A:A))
MsgBox TheMax
Pabaigos sub
Galite naudoti funkciją MIN, kad gautumėte mažiausią reikšmę diapazone. Ir kaip galima tikėtis, panašiai galite naudoti kitas darbalapio funkcijas. Pavyzdžiui, galite naudoti funkciją LARGE norėdami nustatyti k -ąją didžiausią diapazono reikšmę. Tai parodo tokia išraiška:
SecondHighest = Darbalapio funkcija.DIDELIS (diapazonas (A:A),2)
Atkreipkite dėmesį, kad funkcija LARGE naudoja du argumentus. Antrasis argumentas reiškia k -ąją dalį – 2, šiuo atveju (antra pagal dydį reikšmė).
Hipotekos įmokos apskaičiavimas
Kitame pavyzdyje hipotekos mokėjimui apskaičiuoti naudojama PMT darbalapio funkcija. Trys kintamieji naudojami duomenims, kurie perduodami funkcijai Pmt kaip argumentai, saugoti. Pranešimo laukelyje rodomas apskaičiuotas mokėjimas.
Sub PmtCalc()
Dim IntRate kaip dvigubas
Dim LoanAmt As Double
Neryškūs laikotarpiai
Intrate = 0,0625 / 12
Laikotarpiai = 30 * 12
Paskolos suma = 150 000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LoanAmt)
Pabaigos sub
Kaip rodo šis teiginys, reikšmes taip pat galite įterpti tiesiogiai kaip funkcijos argumentus:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Tačiau naudojant kintamuosius parametrams saugoti, kodą lengviau skaityti ir, jei reikia, modifikuoti.
Naudojant paieškos funkciją
Šiame pavyzdyje naudojamos VBA „InputBox“ ir „MsgBox“ funkcijos bei „Excel“ funkcija VLOOKUP. Jis ragina įvesti dalies numerį ir tada gauna kainą iš paieškos lentelės. Žemiau diapazonas A1:B13 pavadintas Kainų sąrašas.
Asortimente, pavadintame PriceList, pateikiamos dalių kainos.
Sub GetPrice ()
Dim PartNum kaip variantas
Maža kaina kaip dviguba
PartNum = InputBox („Įveskite dalies numerį“)
Skaičiuoklės („Kainos“). Suaktyvinkite
Kaina = WorksheetFunction.VLOOKUP (dalies numeris, diapazonas (kainoraštis), 2, klaidinga)
MsgBox PartNum ir „kaina“ ir kaina
Pabaigos sub
Štai kaip veikia GetPrice procedūra:
-
VBA InputBox funkcija prašo vartotojo dalies numerio.
-
Dalies numeris, kurį vartotojas įveda, priskiriamas kintamajam PartNum.
-
Kitas sakinys suaktyvina Kainų darbalapį, tik tuo atveju, jei tai dar nėra aktyvus lapas.
-
Kodas naudoja VLOOKUP funkciją, kad rastų dalies numerį lentelėje.
-
Atkreipkite dėmesį, kad argumentai, kuriuos naudojate šiame teiginyje, yra tokie patys kaip ir tie, kuriuos naudotumėte su funkcija darbalapio formulėje. Šis teiginys priskiria funkcijos rezultatą Kainos kintamajam.
-
Kode per MsgBox funkciją rodoma detalės kaina.
Ši procedūra neapdoroja jokių klaidų ir apgailėtinai nepavyksta, jei įvesite neegzistuojantį dalies numerį. (Išbandykite.) Jei tai būtų tikra programa, naudojama tikrame versle, turėtumėte pridėti keletą teiginių, kurie grakščiau nagrinėja klaidas.
Darbalapio funkcijų įvedimas
Negalite naudoti dialogo lango „Excel“ įklijavimo funkcija, kad įterptumėte darbalapio funkciją į VBA modulį. Vietoj to, įveskite tokias funkcijas senoviniu būdu: ranka. Tačiau galite naudoti dialogo langą Įklijuoti funkciją, norėdami nustatyti funkciją, kurią norite naudoti, ir sužinoti apie jos argumentus.
Taip pat galite pasinaudoti VBE automatinio sąrašo narių parinktimi, kuri rodo išskleidžiamąjį visų darbalapio funkcijų sąrašą. Tiesiog įveskite Application.WorksheetFunction ir tašką. Tada pamatysite funkcijų, kurias galite naudoti, sąrašą. Jei ši funkcija neveikia, pasirinkite VBE komandą Įrankiai → Parinktys, spustelėkite skirtuką Redagavimo priemonė ir pažymėkite varnelę šalia Automatinio sąrašo narių.
Darbalapio funkcijų, kurias galite naudoti savo VBA kode, sąrašas.
Daugiau apie darbalapio funkcijų naudojimą
Naujokai VBA dažnai painioja VBA integruotas funkcijas ir Excel darbaknygės funkcijas. Gera taisyklė, kurią reikia atsiminti, yra ta, kad VBA nemėgina išradinėti dviračio. Dažniausiai VBA nedubliuoja „Excel“ darbalapio funkcijų.
Daugeliui darbalapio funkcijų, kurios nepasiekiamos kaip objekto WorksheetFunction metodai, galite naudoti lygiavertį VBA integruotą operatorių arba funkciją. Pavyzdžiui, MOD darbalapio funkcijos nėra WorksheetFunction objekte, nes VBA turi atitikmenį: įtaisytąjį Mod operatorių.
Apatinė eilutė? Jei jums reikia naudoti funkciją, pirmiausia nustatykite, ar VBA yra kažkas, kas atitinka jūsų poreikius. Jei ne, patikrinkite darbalapio funkcijas. Jei visa kita nepavyks, galite parašyti pasirinktinę funkciją naudodami VBA.