Vaikka VBA tarjoaa kunnollisen valikoiman sisäänrakennettuja toimintoja, et välttämättä aina löydä juuri sitä mitä tarvitset. Onneksi voit myös käyttää useimpia Excelin taulukkofunktioita VBA-proseduureissasi. Ainoat taulukkofunktiot, joita et voi käyttää, ovat ne, joissa on vastaava VBA-toiminto. Et voi esimerkiksi käyttää Excelin RAND-funktiota (joka luo satunnaisluvun), koska VBA:ssa on vastaava funktio: Rnd.
VBA tuo Excelin taulukkofunktiot saataville WorksheetFunction-objektin kautta, joka sisältyy Application-objektiin. Tässä on esimerkki siitä, kuinka voit käyttää Excelin SUMMA-funktiota VBA-käskyssä:
Yhteensä = Application.WorksheetFunction.SUM(Alue("A1:A12")
Voit jättää pois lausekkeen joko Application-osan tai WorksheetFunction-osan. Kummassakin tapauksessa VBA selvittää, mitä olet tekemässä. Toisin sanoen nämä kolme ilmaisua toimivat kaikki täsmälleen samalla tavalla:
Yhteensä = Application.WorksheetFunction.SUM(Alue("A1:A12")
Yhteensä = Työtaulukkofunktio.SUMMA(alue("A1:A12")
Yhteensä = Sovellus.SUMMA(alue("A1:A12")
Henkilökohtainen mieltymykseni on käyttää WorksheetFunction-osaa vain tehdäkseni täysin selväksi, että koodi käyttää Excel-toimintoa.
Esimerkkejä laskentataulukon funktioista
Täällä opit käyttämään laskentataulukkofunktioita VBA-lausekkeissasi.
Alueen enimmäisarvon löytäminen
Tässä on esimerkki, joka näyttää, kuinka Excelin MAX-laskentataulukkofunktiota käytetään VBA-menettelyssä. Tämä toimenpide näyttää suurimman arvon aktiivisen laskentataulukon sarakkeessa A:
Työtaulukkofunktion käyttäminen VBA-koodissasi.
Sub ShowMax()
Dim TheMax As Double
TheMax = Työtaulukkofunktio.MAX(alue("A:A")
MsgBox TheMax
Lopeta ala
Voit käyttää MIN-toimintoa saadaksesi alueen pienimmän arvon. Ja kuten voit odottaa, voit käyttää muita laskentataulukon toimintoja samalla tavalla. Voit esimerkiksi käyttää LARGE-funktiota määrittääksesi alueen k: nnen suurimman arvon. Seuraava ilmaisu osoittaa tämän:
SecondHighest = Työtaulukkofunktio.SUURI(alue("A:A"),2)
Huomaa, että LARGE-funktio käyttää kahta argumenttia. Toinen argumentti edustaa k: nnettä osaa — 2, tässä tapauksessa (toiseksi suurin arvo).
Asuntolainan maksun laskeminen
Seuraava esimerkki käyttää PMT-laskentataulukkofunktiota asuntolainan maksun laskemiseen. Kolmea muuttujaa käytetään Pmt-funktiolle argumentteina välitettävien tietojen tallentamiseen. Viestiruutu näyttää lasketun maksun.
Sub PmtCalc()
Himmeä IntRate Double
Dim LoanAmt As Double
Himmeä jaksot yhtä pitkät
IntRate = 0,0625 / 12
Jaksot = 30 * 12
Lainasumma = 150 000
MsgBox WorksheetFunction.PMT(IntRate, Periods, -LainaAmt)
Lopeta ala
Kuten seuraava lause osoittaa, voit myös lisätä arvot suoraan funktion argumenteiksi:
MsgBox WorksheetFunction.PMT(0,0625 /12, 360, -150000)
Kuitenkin muuttujien käyttö parametrien tallentamiseen helpottaa koodin lukemista ja tarvittaessa muokkaamista.
Hakutoiminnon käyttäminen
Seuraava esimerkki käyttää VBA:n InputBox- ja MsgBox-toimintoja sekä Excelin VLOOKUP-toimintoa. Se pyytää osanumeron ja saa sitten hinnan hakutaulukosta. Alla alueen A1:B13 nimi on PriceList.
Hintalista-niminen valikoima sisältää osien hinnat.
Sub GetPrice()
Dim PartNum Varianttina
Tuplahinta
PartNum = InputBox ("Anna osanumero")
Sheets ("hinnat"). Aktivoi
Hinta = WorksheetFunction.VLOOKUP(Osanumero, Alue("Hinnalista"), 2, False)
MsgBox PartNum & "kustannukset" & hinta
Lopeta ala
GetPrice-menettely toimii seuraavasti:
-
VBA:n InputBox-toiminto pyytää käyttäjältä osanumeron.
-
Osanumero, jonka käyttäjä syöttää, on liitetty PartNum-muuttujaan.
-
Seuraava lause aktivoi Hinnat-laskentataulukon siltä varalta, että se ei ole jo aktiivinen taulukko.
-
Koodi käyttää VLOOKUP-toimintoa löytääkseen osanumeron taulukosta.
-
Huomaa, että tässä lauseessa käyttämäsi argumentit ovat samat kuin ne, joita käyttäisit funktion kanssa laskentataulukon kaavassa. Tämä lauseke määrittää funktion tuloksen Hinta-muuttujalle.
-
Koodi näyttää osan hinnan MsgBox-toiminnon kautta.
Tässä toimenpiteessä ei ole virheenkäsittelyä, ja se epäonnistuu surkeasti, jos annat olemattoman osanumeron. (Kokeile.) Jos tämä olisi todellinen sovellus, jota käytetään todellisessa liiketoiminnassa, sinun kannattaa lisätä joitakin lauseita, jotka käsittelevät virheitä sulavammin.
Työtaulukon toimintojen syöttäminen
Et voi käyttää Excelin liittämistoiminto -valintaikkunaa laskentataulukkofunktion lisäämiseen VBA-moduuliin. Sen sijaan syötä tällaiset toiminnot vanhaan tapaan: käsin. Voit kuitenkin myös käyttää Liitä Function valintaikkuna tunnistaa toiminto, jota haluat käyttää ja selvittää sen argumentit.
Voit myös hyödyntää VBE:n Auto List Members -vaihtoehtoa, joka näyttää avattavan luettelon kaikista laskentataulukon toiminnoista. Kirjoita Application.WorksheetFunction ja sen jälkeen piste. Sitten näet luettelon toiminnoista, joita voit käyttää. Jos tämä ominaisuus ei toimi, valitse VBE:n Työkalut → Asetukset -komento, napsauta Editor-välilehteä ja laita valintamerkki kohtaan Auto List Members.
Luettelon saaminen taulukkofunktioista, joita voit käyttää VBA-koodissasi.
Lisätietoja laskentataulukkotoimintojen käytöstä
VBA:n uudet tulokkaat sekoittavat usein VBA:n sisäänrakennetut toiminnot ja Excelin työkirjatoiminnot. Hyvä sääntö muistaa, että VBA ei yritä keksiä pyörää uudelleen. Suurimmaksi osaksi VBA ei kopioi Excel-laskentataulukon toimintoja.
Useimmille taulukkofunktioille, jotka eivät ole käytettävissä WorksheetFunction-objektin menetelminä, voit käyttää vastaavaa VBA:n sisäänrakennettua operaattoria tai funktiota. Esimerkiksi MOD-laskentataulukkotoiminto ei ole käytettävissä WorksheetFunction-objektissa, koska VBA:lla on vastaava: sen sisäänrakennettu Mod-operaattori.
Lopputulos? Jos haluat käyttää toimintoa, selvitä ensin, onko VBA:ssa jotain tarpeitasi vastaavaa. Jos ei, tarkista laskentataulukon toiminnot. Jos mikään muu epäonnistuu, voit ehkä kirjoittaa mukautetun funktion VBA:n avulla.