Toimintoja, kuten VLOOKUP, COUNTIF, SUMIF, kutsutaan laskentataulukkofunktioiksi. Yleensä Excelissä ennalta määritetyt ja laskentataulukossa käytettävät toiminnot ovat laskentataulukkofunktioita. Et voi muuttaa tai nähdä näiden toimintojen takana olevaa koodia VBA: ssa.
Toisaalta käyttäjän määrittämät toiminnot ja VBA: lle ominaiset toiminnot, kuten MsgBox tai InputBox, ovat VBA -toimintoja.
Me kaikki osaamme käyttää VBA -toimintoja VBA: ssa. Mutta entä jos haluamme käyttää VLOOKUPia VBA: ssa. Kuinka teemme sen? Tässä artikkelissa tutkimme juuri sitä.
Laskentataulukon toimintojen käyttäminen VBA: ssa
Käyttääksesi laskentataulukkotoimintoa käytämme sovellusluokkaa. Lähes kaikki laskentataulukon toiminnot on lueteltu Application.WorksheetFunction -luokassa. Ja käyttämällä pisteoperaattoria, voit käyttää niitä kaikkia.
Kirjoita mihin tahansa osaan Application.WorksheetFunction. Ja aloita funktion nimen kirjoittaminen. VBA: n intellisense näyttää käytettävissä olevien toimintojen nimet. Kun olet valinnut funktion nimen, se pyytää muuttujia, kuten mikä tahansa Excel -toiminto. Mutta sinun on välitettävä muuttujat VBA -ymmärrettävässä muodossa. Jos haluat esimerkiksi ohittaa alueen A1: A10, sinun on välitettävä se alueobjektina, kuten alue ("A1: A10").
Joten käytämme joitakin laskentataulukkotoimintoja ymmärtääksemme sen paremmin.
VLOOKUP -toiminnon käyttäminen VBA: ssa
Tässä on esimerkkitietoja osoittaakseni, kuinka voit käyttää VLOOKUP -toimintoa VBA: ssa. Minun on näytettävä annetun kirjautumistunnuksen nimi ja kaupunki viestiruudussa VBA: ta käyttäen. Tiedot levitetään alueelle A1: K26.
Avaa VBE ja aseta moduuli painamalla ALT+F11.
Katso alla oleva koodi.
Sub WsFuncitons () Himmennä kirjautumistunnus merkkijonona Dim-nimi, kaupunki nimellä merkkijono loginID = "AHKJ_1-3357042451" 'VLOOKUP-funktion avulla annetun tunnuksen nimi taulukon nimessä = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26") ), 2, 0) 'VLOOKUP -funktion käyttäminen annetun tunnuksen kaupungin saamiseksi taulukosta city = Application.WorksheetFunction.VLookup (loginID, Range ("A1: K26"), 4, 0) MsgBox ("Name:" & name & vbLf & "City:" & city) End Sub
Kun suoritat tämän koodin, saat tämän tuloksen.
Voit nähdä, kuinka nopeasti VBA tulostaa tuloksen sanomaruutuun. Tarkastellaan nyt koodia.
Kuinka se toimii?
1.
Himmennä kirjautumistunnus merkkijonona
Hämärä nimi, kaupunki merkkijono
Ensin olemme ilmoittaneet kaksi merkkijonomuuttujaa VLOOKUP -funktion palauttaman tuloksen tallentamiseksi. Olen käyttänyt merkkijonomuuttujia, koska olen varma, että VLOOKUPin palauttama tulos on merkkijonoarvo. Jos laskentataulukkotoiminnon odotetaan palauttavan arvon, päivämäärän, alueen jne. Arvon arvon, tallenna tulos käyttämällä tällaista muuttujaa. Jos et ole varma, minkälainen arvo laskentataulukkofunktio palauttaa, käytä varianttityyppisiä muuttujia.
2.
loginID = "AHKJ_1-3357042451"
Seuraavaksi olemme käyttäneet loginID -muuttujaa hakuarvon tallentamiseen. Tässä olemme käyttäneet kovakoodattua arvoa. Voit myös käyttää viitteitä. Esimerkiksi. Voit käyttää väliä ("A2"). Arvo päivittää hakuarvon dynaamisesti alueelta A2.
3.
name = Application.WorksheetFunction.VLookup (kirjautumistunnus, alue ("A1: K26"), 2, 0)
Täällä käytämme VLOOKUP -toimintoa. Nyt kun korjaat funktion ja avaat sulkeet, se näyttää tarvittavat argumentit, mutta ei niin kuvaavia kuin Excelissä. Katso itse.
Sinun on muistettava, miten ja mitä muuttujaa sinun on käytettävä. Voit aina palata laskentataulukkoon nähdäksesi kuvaavan muuttujan tiedot.
Tässä hakuarvo on Arg1. Arg1: ssä käytämme loginID -tunnusta. Hakutaulukko on Arg2. Arg2: lle käytimme Rangea ("A1: K26"). Huomaa, että emme käyttäneet suoraan A2: K26: ta kuten Excelissä. Sarakkeiden indeksi on Arg3. Arg3: lle käytimme 2, koska nimi on toisessa sarakkeessa. Hakutyyppi on Arg4. Käytimme Arg4: nä 0: ta.
kaupunki = Application.WorksheetFunction.VLookup (kirjautumistunnus, alue ("A1: K26"), 4, 0)
Samalla tavalla saamme kaupungin nimen.
4.
MsgBox ("Nimi:" & name & vbLf & "Kaupunki:" & kaupunki)
Lopuksi tulostamme nimen ja kaupungin Messageboxin avulla.
Miksi käyttää laskentataulukkotoimintoa VBA: ssa?
Laskentataulukkotoiminnoissa on valtava määrä laskelmia, eikä ole fiksua jättää huomiotta laskentataulukkotoimintojen tehoa. Jos esimerkiksi haluamme tietojoukon keskihajonnan ja haluat kirjoittaa sille kokonaisen koodin, se voi viedä tunteja. Mutta jos osaat käyttää laskentataulukkotoimintoa STDEV.P VBA: ssa saadaksesi laskelman yhdellä kertaa.
Sub GetStdDev () std = Application.WorksheetFunction.StDev_P (Alue ("A1: K26")) Loppuosa
Useiden laskentataulukkotoimintojen käyttäminen VBA
Oletetaan, että meidän on käytettävä indeksihakua joidenkin arvojen hakemiseen. Miten muotoilisit kaavan VBA: ssa. Tätä varmaan kirjoitat:
Sub IndMtch () Val = Application.WorksheetFunction.Index (result_range, _ Application.WorksheetFunction.Match (lookup_value, _ lookup_range, match_type)) End Sub
Tämä ei ole väärin, mutta se on pitkä. Oikea tapa käyttää useita toimintoja on käyttää With -lohkoa. Katso alla oleva esimerkki:
Sub IndMtch () Application.WorksheetFunction Val = .Index (result_range, .Match (lookup_value, lookup_range, match_type)) val2 = .VLookup (arg1, arg2, arg3) val4 = .StDev_P (numerot) End End End
Kuten näette, olen käyttänyt With -lohkoa kertoakseni VBA: lle, että aion käyttää Application.WorksheetFunction -ominaisuuden ominaisuuksia. Joten minun ei tarvitse määritellä sitä kaikkialla. Käytin juuri pisteoperaattoria päästäksesi INDEX-, MATCH-, VLOOKUP- ja STDEV.P -toimintoihin. Kun käytämme End With -lauseketta, emme voi käyttää toimintoja käyttämättä täysin päteviä funktioiden nimiä.
Joten jos sinun on käytettävä useita laskentataulukkotoimintoja VBA: ssa, käytä lohkon kanssa.
Kaikki laskentataulukkotoiminnot eivät ole käytettävissä sovelluksen kautta
Jotkut laskentataulukkotoiminnot ovat suoraan käytettävissä VBA: ssa. Sinun ei tarvitse käyttää Application.WorksheetFunction -objektia.
Esimerkiksi toimintoja, kuten Len (), jota käytetään merkkijonon lukemiseen merkkijonossa, vasemmalle, oikealle, keskelle, leikkaukselle, offsetille jne. Näitä toimintoja voidaan käyttää suoraan VBA: ssa. Tässä on esimerkki.
Sub GetLen () Strng = "Hei" Debug.Print (Len (strng)) Loppuosa
Katso, tässä käytimme LEN -toimintoa käyttämättä Application.WorksheetFunction -objektia.
Samoin voit käyttää muita toimintoja, kuten vasen, oikea, keski, char jne.
Sub GetLen () Strng = "Hei" Debug.Print (Len (strng)) Debug.Print (vasen (strng, 2)) Debug.Print (oikea (strng, 1)) Debug.Print (Mid (strng, 3, 2)) End Sub
Kun suoritat yllä olevan alaosan, se palauttaa:
5 Hän tulee
Joten kyllä kaverit, tällä tavalla voit käyttää Excelin laskentataulukkotoimintoa VBA: ssa. Toivottavasti olin tarpeeksi selittävä ja tämä artikkeli auttoi sinua. Jos sinulla on kysyttävää tästä artikkelista tai mistä tahansa muusta VBA: sta, kysy alla olevasta kommenttiosasta. Siihen asti voit lukea muista aiheeseen liittyvistä aiheista alla.
Mikä on CSng -toiminto Excel VBA: ssa | SCng-toiminto on VBA-toiminto, joka muuntaa minkä tahansa tietotyypin yhden tarkkuuden liukulukuluvuksi ("koska se on numero"). Käytän enimmäkseen CSng -toimintoa tekstin muotoiltujen numeroiden muuntamiseen todellisiksi numeroiksi.
Kuinka saada teksti ja numero käänteisenä VBA: n kautta Microsoft Excelissä | Numeron ja tekstin kääntämiseksi käytämme silmukoita ja keskitoimintoa VBA: ssa. 1234 muunnetaan 4321: ksi, "sinä" muunnetaan "uoy": ksi. Tässä katkelma.
Muotoile tiedot mukautetuilla numeromuodoilla VBA: n avulla Microsoft Excelissä | Jos haluat muuttaa tiettyjen Excel -sarakkeiden lukumuotoa, käytä tätä VBA -katkelmaa. Se peittää määritetyn numeromuodon tiettyyn muotoon yhdellä napsautuksella.
Laskentataulukon muutostapahtuman käyttäminen makron suorittamiseen, kun muutos on tehty | Joten makron suorittamiseen aina, kun taulukko päivittyy, käytämme VBA: n laskentataulukkotapahtumia.
Suorita makro, jos taulukkoon tehdään muutoksia määritetyllä alueella | Käytä tätä VBA -koodia makrokoodin suorittamiseen määritetyn alueen arvon muuttuessa. Se havaitsee määritetylle alueelle tehdyt muutokset ja laukaisee tapahtuman.
Yksinkertaisin VBA -koodi nykyisen rivin ja sarakkeen korostamiseen | Käytä tätä pientä VBA -katkelmaa korostamaan taulukon nykyinen rivi ja sarake.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi | Nopeuta tehtävääsi. Nämä 50 pikanäppäintä tekevät työskentelystäsi entistä nopeampaa Excelissä.
VLOOKUP -toiminto Excelissä | Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja arkeilta.
COUNTIF Excel 2016: ssa | Laske arvot olosuhteilla käyttämällä tätä hämmästyttävää toimintoa. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.
SUMIF -toiminnon käyttäminen Excelissä | Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.