Excel VBA -tapahtumat - helppo (ja täydellinen) opas

Kun luot tai tallennat makron Excelissä, sinun on suoritettava makro suorittaaksesi koodin vaiheet.

Muutamia tapoja suorittaa makroa ovat makrovalintaikkunan käyttäminen, makron määrittäminen painikkeelle, pikavalinnan käyttäminen jne.

Näiden käyttäjien käynnistämien makrojen suorittamisen lisäksi voit käyttää makroa myös VBA-tapahtumien avulla.

Excel VBA -tapahtumat - Johdanto

Selitän ensin, mikä on tapahtuma VBA: ssa.

Tapahtuma on toiminto, joka voi laukaista määritetyn makron suorituksen.

Esimerkiksi kun avaat uuden työkirjan, se on tapahtuma. Kun lisäät uuden laskentataulukon, se on tapahtuma. Kun kaksoisnapsautat solua, se on tapahtuma.

VBA: ssa on monia tällaisia ​​tapahtumia, ja voit luoda näille tapahtumille koodeja. Tämä tarkoittaa, että heti kun tapahtuma tapahtuu ja jos olet määrittänyt koodin kyseiselle tapahtumalle, koodi suoritetaan välittömästi.

Excel tekee tämän automaattisesti heti, kun se havaitsee tapahtuman. Joten sinun tarvitsee vain kirjoittaa koodi ja sijoittaa se oikeaan tapahtuma -aliohjelmaan (tämä käsitellään myöhemmin tässä artikkelissa).

Jos esimerkiksi lisäät uuden laskentataulukon ja haluat, että sillä on vuoden etuliite, voit kirjoittaa sen koodin.

Nyt kun joku lisää uuden laskentataulukon, tämä koodi suoritetaan automaattisesti ja lisätään laskentataulukon nimeen vuositunnus.

Toinen esimerkki voi olla, että haluat muuttaa solun väriä, kun joku kaksoisnapsauttaa sitä. Voit käyttää kaksoisnapsautustapahtumaa tähän.

Samoin voit luoda VBA -koodeja monille tällaisille tapahtumille (kuten näemme myöhemmin tässä artikkelissa).

Alla on lyhyt visualisointi, joka näyttää kaksoisnapsautustapahtuman toiminnassa. Heti kun kaksoisnapsautan solua A1. Excel avaa välittömästi viestiruudun, joka näyttää solun osoitteen.

Kaksoisnapsautus on tapahtuma, ja viestiruudun näyttämisen olen määrittänyt koodissa aina, kun kaksoisnapsautus tapahtuu.

Vaikka yllä oleva esimerkki on hyödytön tapahtuma, toivon, että se auttaa sinua ymmärtämään, mitä tapahtumat todella ovat.

Erilaisia ​​Excel VBA -tapahtumia

Excelissä on erilaisia ​​objekteja - kuten itse Excel (johon usein viitataan sovelluksena), työkirjat, laskentataulukot, kaaviot jne.

Jokaiseen näistä kohteista voi liittyä erilaisia ​​tapahtumia. Esimerkiksi:

• Jos luot uuden työkirjan, se on sovellustason tapahtuma.
• Jos lisäät uuden laskentataulukon, se on työkirjatason tapahtuma.
• Jos muutat taulukon solun arvoa, kyseessä on laskentataulukkotason tapahtuma.

Alla on erilaisia ​​Excelissä olevia tapahtumatyyppejä:

1. Laskentataulukon tason tapahtumat: Nämä ovat tapahtumatyyppejä, jotka käynnistyvät laskentataulukossa tehtyjen toimintojen perusteella. Esimerkkejä näistä tapahtumista ovat laskentataulukon solun muuttaminen, valinnan muuttaminen, solun kaksoisnapsauttaminen, solun napsauttaminen hiiren kakkospainikkeella jne.
2. Työkirjatason tapahtumat: Nämä tapahtumat käynnistyvät työkirjatason toimien perusteella. Esimerkkejä näistä tapahtumista ovat uuden laskentataulukon lisääminen, työkirjan tallentaminen, työkirjan avaaminen, osan tai koko työkirjan tulostaminen jne.
3. Sovellustason tapahtumat: Nämä tapahtumat tapahtuvat Excel -sovelluksessa. Esimerkkejä näistä ovat minkä tahansa avoimen työkirjan sulkeminen tai uuden työkirjan avaaminen.
4. UserForm -tason tapahtumat: Nämä tapahtumat käynnistyvät UserForm -toiminnon perusteella. Esimerkkejä näistä ovat UserFormin alustaminen tai UserFormin painikkeen napsauttaminen.
5. Kaavion tapahtumat: Nämä ovat kaavioarkkiin liittyviä tapahtumia. Kaavioarkki on erilainen kuin laskentataulukko (jossa useimmat meistä ovat tottuneet työskentelemään Excelissä). Kaavioarkkien tarkoitus on pitää kaavio. Esimerkkejä tällaisista tapahtumista ovat kaavion sarjan muuttaminen tai kaavion koon muuttaminen.
6. OnTime- ja OnKey -tapahtumat: Nämä ovat kaksi tapahtumaa, jotka eivät sovi mihinkään yllä olevista luokista. Olen siis listannut nämä erikseen. OnTime -tapahtuman avulla voit suorittaa koodin tiettynä aikana tai tietyn ajan kuluttua. OnKey -tapahtuman avulla voit suorittaa koodin, kun tiettyä näppäintä (tai näppäinyhdistelmien yhdistelmää) käytetään.

Minne laittaa tapahtumiin liittyvä koodi

Yllä olevassa osassa käsitin erilaisia ​​tapahtumia.

Tapahtuman tyypin mukaan sinun on lisättävä koodi asiaankuuluvaan objektiin.

Jos kyseessä on esimerkiksi laskentataulukkoon liittyvä tapahtuma, sen pitäisi mennä laskentataulukko -objektin koodi -ikkunaan. Jos se liittyy työkirjaan, sen pitäisi mennä työkirjaobjektin koodi -ikkunaan.

VBA: ssa eri objekteilla, kuten laskentataulukoilla, työkirjoilla, kaavioarkkeilla, käyttäjämuodoilla jne., On omat koodi -ikkunansa. Sinun täytyy laittaa tapahtumakoodi asianomaisen objektin koodi -ikkunaan. Esimerkiksi - jos kyseessä on työkirjatason tapahtuma, sinulla on oltava tapahtumakoodi Työkirjan koodi -ikkunassa.

Seuraavat kohdat kattavat paikat, joihin voit laittaa tapahtumakoodin:

Laskentataulukon koodi -ikkunassa

Kun avaat VB Editorin (käyttämällä pikanäppäintä ALT + F11), huomaat laskentataulukko -objektin Project Explorerissa. Jokaista työkirjan laskentataulukkoa varten näet yhden objektin.

Kun kaksoisnapsautat laskentataulukko-objektia, johon haluat sijoittaa koodin, se avaa kyseisen laskentataulukon koodi-ikkunan.

Vaikka voit aloittaa koodin kirjoittamisen tyhjästä, on paljon parempi valita tapahtuma vaihtoehtojen luettelosta ja antaa VBA: n lisätä automaattisesti valitun tapahtuman koodi.

Tätä varten sinun on ensin valittava laskentataulukko koodi-ikkunan vasemmassa yläkulmassa olevasta avattavasta valikosta.

Kun olet valinnut laskentataulukon avattavasta valikosta, saat luettelon kaikista laskentataulukkoon liittyvistä tapahtumista. Voit valita haluamasi koodin ikkunan oikeassa yläkulmassa olevasta avattavasta valikosta.

Heti kun valitset tapahtuman, se syöttää automaattisesti valitun tapahtuman koodin ensimmäisen ja viimeisen rivin. Nyt voit lisätä koodisi kahden rivin väliin.

Huomautus: Heti kun valitset laskentataulukon avattavasta valikosta, huomaat, että koodiikkunassa näkyy kaksi koodiriviä. Kun olet valinnut tapahtuman, jolle haluat koodin, voit poistaa oletuksena näytetyt rivit.

Huomaa, että jokaisella laskentataulukolla on oma koodi -ikkuna. Kun lisäät taulukon 1 koodin, se toimii vain, jos tapahtuma tapahtuu taulukossa 1.

Tässä työkirjan koodi -ikkunassa

Aivan kuten laskentataulukot, jos sinulla on työkirjatason tapahtumakoodi, voit sijoittaa sen ThisWorkbook -koodi -ikkunaan.

Kun kaksoisnapsautat tätä työkirjaa, se avaa sen koodi-ikkunan.

Sinun on valittava Työkirja koodi-ikkunan vasemmassa yläkulmassa olevasta avattavasta valikosta.

Kun olet valinnut Työkirja avattavasta valikosta, saat luettelon kaikista työkirjaan liittyvistä tapahtumista. Voit valita haluamasi koodin ikkunan oikeassa yläkulmassa olevasta avattavasta valikosta.

Heti kun valitset tapahtuman, se syöttää automaattisesti valitun tapahtuman koodin ensimmäisen ja viimeisen rivin. Nyt voit lisätä koodisi kahden rivin väliin.

Huomautus: Heti kun valitset avattavasta valikosta Työkirja, huomaat, että koodi-ikkunassa näkyy kaksi koodiriviä. Kun olet valinnut tapahtuman, jolle haluat koodin, voit poistaa oletuksena näytetyt rivit.

Userform Code -ikkunassa

Kun luot UserFormseja Excelissä, voit käyttää UserForm -tapahtumia myös suorittamaan koodeja tiettyjen toimintojen perusteella. Voit esimerkiksi määrittää koodin, joka suoritetaan, kun painiketta napsautetaan.

Vaikka Sheet- ja ThisWorkbook -objektit ovat jo saatavilla, kun avaat VB Editorin, UserForm on jotain, joka sinun on luotava ensin.

Jos haluat luoda UserFormin, napsauta mitä tahansa objektia hiiren kakkospainikkeella, siirry Lisää-kohtaan ja napsauta UserForm.

Tämä lisäisi UserForm -objektin työkirjaan.

Kun kaksoisnapsautat UserFormia (tai mitä tahansa UserFormiin lisäämääsi objektia), se avaa UserFormin koodi-ikkunan.

Nyt, aivan kuten laskentataulukot tai ThisWorkbook, voit valita tapahtuman ja se lisää tapahtuman ensimmäisen ja viimeisen rivin. Ja sitten voit lisätä koodin sen keskelle.

Kaavion koodi -ikkunassa

Excelissä voit myös lisätä kaavioarkkeja (jotka ovat erilaisia ​​kuin laskentataulukot). Kaavioarkin on tarkoitus sisältää vain kaavioita.

Kun olet lisännyt kaavioarkin, näet kaavioarkki -objektin VB -editorissa.

Voit lisätä tapahtumakoodin kaavioarkin koodi -ikkunaan aivan kuten laskentataulukossa.

Kaksoisnapsauta kaaviotaulukko -objektia Project Explorerissa. Tämä avaa kaavioarkin koodi -ikkunan.

Nyt sinun on valittava Kaavio koodi-ikkunan vasemmassa yläkulmassa olevasta avattavasta valikosta.

Kun olet valinnut Kaavio avattavasta valikosta, saat luettelon kaikista kaavioarkkiin liittyvistä tapahtumista. Voit valita haluamasi koodin ikkunan oikeassa yläkulmassa olevasta avattavasta valikosta.

Huomautus: Heti kun valitset Kaavio avattavasta valikosta, huomaat, että koodiikkunassa näkyy kaksi koodiriviä. Kun olet valinnut tapahtuman, jolle haluat koodin, voit poistaa oletuksena näytetyt rivit.

Luokan moduulissa

Luokan moduulit on lisättävä aivan kuten UserForms.

Luokkamoduuli voi sisältää sovellukseen liittyvän koodin - joka olisi itse Excel ja upotetut kaaviot.

Käyn luentomoduulin erillisenä opetusohjelmana tulevina viikkoina.

Huomaa, että OnTime- ja OnKey -tapahtumia lukuun ottamatta mitään yllä olevista tapahtumista ei voi tallentaa tavalliseen VBA -moduuliin.

Tapahtumasarjan ymmärtäminen

Kun käynnistät tapahtuman, se ei tapahdu erikseen. Se voi myös johtaa useiden laukaisimien sarjaan.

Esimerkiksi kun lisäät uuden laskentataulukon, tapahtuu seuraavia asioita:

1. Uusi laskentataulukko lisätään
2. Edellinen laskentataulukko poistetaan käytöstä
3. Uusi laskentataulukko aktivoidaan

Useimmissa tapauksissa sinun ei ehkä tarvitse huolehtia järjestyksestä, mutta jos luot monimutkaisia ​​koodeja, jotka perustuvat tapahtumiin, on parempi tietää järjestys odottamattomien tulosten välttämiseksi.

Argumenttien roolin ymmärtäminen VBA -tapahtumissa

Ennen kuin siirrymme tapahtumaesimerkkeihin ja mahtaviin asioihin, joita voit tehdä sillä, minun on käsiteltävä yksi tärkeä käsite.

VBA -tapahtumissa olisi kahdenlaisia ​​koodeja:

• Ilman perusteluja
• Väitteiden kanssa

Ja tässä osassa haluan käsitellä nopeasti argumenttien roolia.

Alla on koodi, jossa ei ole argumenttia (sulkeet ovat tyhjiä):

Yksityinen alityökirja_Open () MsgBox "Muista täyttää työaikaraportti" End Sub

Yllä olevalla koodilla, kun avaat työkirjan, se näyttää vain viestiruudun, jossa on viesti - "Muista täyttää työaikaraportti".

Katsotaanpa nyt koodia, jolla on argumentti.

Yksityinen alityökirja_Uusi arkki (ByVal Sh as Object) Sh.Range ("A1") = Sh.Name End Sub

Yllä oleva koodi käyttää Sh -argumenttia, joka määritellään objektityypiksi. Sh argumentti voi olla laskentataulukko tai kaavio, koska yllä oleva tapahtuma käynnistyy, kun uusi taulukko lisätään.

Määrittämällä työkirjaan lisättävän uuden taulukon objektimuuttujalle Sh, VBA on antanut meille mahdollisuuden käyttää sitä koodissa. Joten voidakseni viitata uuden arkin nimeen voin käyttää Sh.Name.

Argumenttien käsitteestä on hyötyä, kun käyt läpi VBA -tapahtumaesimerkkejä seuraavissa osissa.

Työkirjatason tapahtumat (selitetty esimerkeillä)

Seuraavassa on työkirjan yleisimmin käytetyt tapahtumat.

TAPAHTUMAN NIMI	MITÄ TAPAHTUMA LÄHTÄÄ
Aktivoida	Kun työkirja on aktivoitu
AfterSave	Kun työkirja on asennettu apuohjelmaksi
EnnenTallenna	Kun työkirja tallennetaan
EnnenSulje	Kun työkirja on suljettu
Ennen tulostusta	Kun työkirja tulostetaan
Poista käytöstä	Kun työkirja on poistettu käytöstä
NewSheet	Kun uusi arkki lisätään
Avata	Kun työkirja avataan
SheetActivate	Kun jokin työkirjan arkki aktivoidaan
SheetBeforeDelete	Kun jokin arkki poistetaan
SheetBeforeDoubleClick	Kun mitä tahansa taulukkoa kaksoisnapsautetaan
SheetBeforeRightClick	Kun mitä tahansa taulukkoa napsautetaan hiiren kakkospainikkeella
SheetCalculate	Kun jokin arkki lasketaan tai lasketaan uudelleen
SheetDeactivate	Kun työkirja on poistettu käytöstä
SheetPivotTableUpdate	Kun työkirja päivitetään
SheetSelectionChange	Kun työkirjaa muutetaan
IkkunaAktivoi	Kun työkirja on aktivoitu
Ikkuna Poista käytöstä	Kun työkirja on poistettu käytöstä

Huomaa, että tämä ei ole täydellinen luettelo. Löydät täydellisen luettelon täältä.

Muista, että työkirjatapahtuman koodi tallennetaan ThisWorkbook -objektikoodi -ikkunaan.

Katsotaanpa nyt joitain hyödyllisiä työkirjan tapahtumia ja miten niitä voidaan käyttää päivittäisessä työssäsi.

Työkirjan avoin tapahtuma

Oletetaan, että haluat näyttää käyttäjälle ystävällisen muistutuksen työaikataulujen täyttämisestä aina, kun hän avaa tietyn työkirjan.

Voit käyttää tätä alla olevan koodin avulla:

Yksityinen alityökirja_Open () MsgBox "Muista täyttää työaikaraportti" End Sub

Nyt kun avaat työkirjan, jossa on tämä koodi, se näyttää viestiruudun, jossa on määritetty viesti.

Kun käytät tätä koodia (tai työkirjan tapahtumakoodeja yleensä), sinun on tiedettävä muutama asia:

• Jos työkirjassa on makro ja haluat tallentaa sen, sinun on tallennettava se .XLSM -muodossa. Muuten makrokoodi menetetään.
• Yllä olevassa esimerkissä tapahtumakoodi suoritetaan vain, kun makrot ovat käytössä. Saatat nähdä keltaisen palkin, joka pyytää lupaa makrojen käyttöönottoon. Ennen kuin tämä on otettu käyttöön, tapahtumakoodia ei suoriteta.
• Työkirjan tapahtumakoodi sijoitetaan ThisWorkbook -objektin koodi -ikkunaan.

Voit tarkentaa tätä koodia ja näyttää viestin vain perjantaina.

Alla oleva koodi toimisi näin:

Yksityinen alityökirja_Open () wkday = Viikonpäivä (päivämäärä) Jos wkday = 6 Sitten MsgBox "Muista täyttää työaikaraportti" End Sub

Huomaa, että viikonpäivätoiminnossa sunnuntai on määritetty arvoksi 1, maanantai on 2 ja niin edelleen.

Olen siis käyttänyt perjantaina 6.

Työkirjan avaustapahtuma voi olla hyödyllinen monissa tilanteissa, kuten:

• Kun haluat näyttää tervetuloviestin henkilölle, kun työkirja avataan.
• Kun haluat näyttää muistutuksen työkirjan avaamisen yhteydessä.
• Kun haluat aina aktivoida tietyn laskentataulukon työkirjassa, kun se avataan.
• Kun haluat avata aiheeseen liittyviä tiedostoja työkirjan kanssa.
• Kun haluat tallentaa päivämäärän ja aikaleiman aina, kun työkirja avataan.

Työkirjan NewSheet -tapahtuma

NewSheet -tapahtuma käynnistyy, kun lisäät uuden taulukon työkirjaan.

Oletetaan, että haluat syöttää päivämäärän ja kellonajan arvon juuri lisätyn taulukon soluun A1. Voit käyttää tätä alla olevan koodin avulla:

Yksityinen osa Työkirja_Uusi-arkki (ByVal Sh objektina) Virheessä Jatka Seuraava Sh.Range ("A1") = Muoto (Nyt, "pp-kk-vvvv tt: mm: ss") Loppuosa

Yllä oleva koodi käyttää "Virhe Jatka seuraavaksi" käsitellä tapauksia, joissa joku lisää kaavioarkin eikä laskentataulukkoa. Koska kaavioarkissa ei ole solua A1, se näyttäisi virheen, jos 'Virhe jatka seuraavaksi' ei käytetä.

Toinen esimerkki voi olla, kun haluat käyttää joitakin perusasetuksia tai muotoiluja uuteen taulukkoon heti, kun se lisätään. Jos esimerkiksi haluat lisätä uuden taulukon ja haluat sen saavan automaattisesti sarjanumeron (enintään 100), voit käyttää alla olevaa koodia.

Private Sub Workbook_NewSheet (ByVal Sh as Object) On Virhe Jatka seuraavaksi Sh.Range ("A1"). Arvo = "S. No." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 to 100 Sh.Range ("A1"). Offset (i, 0) .Arvo = i Seuraava i Sh.Range ("A1", alue ("A1"). End (xlDown)). Borders.LineStyle = xlContinuous End Sub

Yllä oleva koodi tekee myös hieman muotoilua. Se antaa otsikkosolulle sinisen värin ja tekee fontista valkoisen. Se soveltaa myös reunusta kaikkiin täytettyihin soluihin.

Yllä oleva koodi on esimerkki siitä, kuinka lyhyt VBA -koodi voi auttaa sinua varastamaan muutaman sekunnin joka kerta, kun lisäät uuden laskentataulukon (jos tämä on jotain, joka sinun on tehtävä joka kerta).

Työkirja BeforeSave Event

Ennen kuin Tallenna -tapahtuma käynnistyy, kun tallennat työkirjan. Huomaa, että tapahtuma laukaistaan ​​ensin ja sitten työkirja tallennetaan.

Kun tallennat Excel -työkirjan, voi olla kaksi mahdollista skenaariota:

1. Tallennat sen ensimmäistä kertaa ja se näyttää Tallenna nimellä -valintaikkunan.
2. Olet tallentanut sen jo aiemmin, ja se yksinkertaisesti tallentaa ja korvaa jo tallennetun version muutokset.

Katsotaanpa nyt muutamia esimerkkejä, joissa voit käyttää BeforeSave -tapahtumaa.

Oletetaan, että sinulla on uusi työkirja, jonka tallennat ensimmäistä kertaa, ja haluat muistuttaa käyttäjää tallentamaan sen K -asemaan, ja voit käyttää alla olevaa koodia:

Yksityinen alityökirja_BeforeSave (ByVal SaveAsUI Boolen muodossa, Peruuta Booleanina) Jos SaveAsUI Sitten MsgBox "Tallenna tämä tiedosto K -asemaan" End Sub

Jos yllä olevassa koodissa tiedostoa ei ole koskaan tallennettu, SaveAsUI on tosi ja tuo esiin Tallenna nimellä -valintaikkunan. Yllä oleva koodi näyttää viestin ennen kuin Tallenna nimellä -valintaikkuna tulee näkyviin.

Toinen esimerkki voisi olla päivittää päivämäärä ja kellonaika, kun tiedosto tallennetaan tiettyyn soluun.

Alla oleva koodi lisäisi päivämäärän ja aikaleiman taulukon 1 soluun A1 aina, kun tiedosto tallennetaan.

Yksityinen alityökirja_BeforeSave (ByVal SaveAsUI Booleanina, Peruuta Booleanina) laskentataulukot ("Sheet1"). Alue ("A1") = Muoto (Nyt, pp-kk-vvvv tt: mm: ss) Loppuosa

Huomaa, että tämä koodi suoritetaan heti, kun käyttäjä tallentaa työkirjan. Jos työkirja tallennetaan ensimmäistä kertaa, se näyttää Tallenna nimellä -valintaikkunan. Mutta koodi on jo suoritettu, kun näet Tallenna nimellä -valintaikkunan. Jos tässä vaiheessa päätät peruuttaa työkirjan tallentamatta, päivämäärä ja kellonaika syötetään jo soluun.

Työkirja ennen tapahtuman sulkemista

Ennen sulkemista -tapahtuma tapahtuu juuri ennen työkirjan sulkemista.

Alla oleva koodi suojaa kaikki laskentataulukot ennen työkirjan sulkemista.

Yksityinen alityökirja_BeforeClose (Peruuta Booleanina) Dim sh kuin laskentataulukko jokaiselle tämän työkirjan sh: lle.

Muista, että tapahtumakoodi käynnistyy heti, kun suljet työkirjan.

Yksi tärkeä tieto tästä tapahtumasta on, että sillä ei ole väliä, onko työkirja todella suljettu vai ei.

Jos työkirjaa ei ole tallennettu ja näyttöön tulee kehote, jossa kysytään, tallennetaanko työkirja vai ei, ja napsautat Peruuta, se ei tallenna työkirjaasi.Tapahtumakoodi olisi kuitenkin jo suoritettu.

Työkirja ennen tulostustapahtumaa

Kun annat tulostuskomennon (tai Tulostuksen esikatselukomennon), Ennen tulostusta -tapahtuma käynnistyy.

Alla oleva koodi laskee kaikki laskentataulukot uudelleen ennen työkirjan tulostamista.

Yksityinen alityökirja_BeforePrint (Peruuta Booleanina) jokaiselle työkirjan ws: lle ws. Laske seuraavat ws End Sub

Kun käyttäjä tulostaa työkirjaa, tapahtuma käynnistyy, tulostaako hän koko työkirjan tai vain osan siitä.

Toinen esimerkki alla on koodi, joka lisäisi alatunnisteeseen päivämäärän ja kellonajan, kun työkirja tulostetaan.

Yksityinen alityökirja_BeforePrint (Peruuta Booleanina) Dim ws laskentataulukkona jokaiselle tämän työkirjan ws: lle ws.PageSetup.LeftFooter = "Printed On-" & Format (Nyt, "dd-mmm-yyyy hh: mm") Seuraava ws End Sub

Laskentataulukon tason tapahtumat (selitetty esimerkeillä)

Laskentataulukon tapahtumat tapahtuvat laskentataulukon laukaisimien perusteella.

Seuraavassa on taulukon yleisimmin käytetyt tapahtumat.

Tapahtuman nimi	Mikä laukaisee tapahtuman
Aktivoida	Kun laskentataulukko on aktivoitu
EnnenPoista	Ennen kuin laskentataulukko poistetaan
BeforeDoubleClick	Ennen kuin laskentataulukkoa kaksoisnapsautetaan
BeforeRightClick	Ennen kuin laskentataulukkoa napsautetaan hiiren kakkospainikkeella
Laskea	Ennen kuin laskentataulukko lasketaan tai lasketaan uudelleen
Muuttaa	Kun laskentataulukon soluja muutetaan
Poista käytöstä	Kun laskentataulukko on poistettu käytöstä
Pivot -taulukon päivitys	Kun laskentataulukon pivot -taulukko päivitetään
ValintaMuuta	Kun laskentataulukon valintaa muutetaan

Huomaa, että tämä ei ole täydellinen luettelo. Löydät täydellisen luettelon täältä.

Muista, että laskentataulukkotapahtuman koodi tallennetaan laskentataulukko -objektikoodi -ikkunaan (siihen, jossa haluat tapahtuman käynnistyvän). Työkirjassa voi olla useita laskentataulukoita, ja koodisi laukaistaan ​​vain, kun tapahtuma tapahtuu laskentataulukossa, johon se on sijoitettu.

Katsotaanpa nyt joitain hyödyllisiä laskentataulukkotapahtumia ja miten niitä voidaan käyttää päivittäisessä työssäsi.

Laskentataulukko Aktivoi tapahtuma

Tämä tapahtuma käynnistyy, kun aktivoit laskentataulukon.

Alla oleva koodi poistaa taulukon suojan heti, kun se aktivoidaan.

Yksityinen alityöarkki_Activate () ActiveSheet. Unprotect End Sub

Tämän tapahtuman avulla voit myös varmistaa, että tietty solu tai solualue (tai nimetty alue) on valittu heti, kun aktivoit laskentataulukon. Alla oleva koodi valitsee solun D1 heti, kun aktivoit taulukon.

Yksityinen alityöarkki_Activate () ActiveSheet.Range ("D1"). Valitse Lopeta ala

Laskentataulukon muutostapahtuma

Muutostapahtuma käynnistyy aina, kun teet muutoksia laskentataulukkoon.

No… ei aina.

Jotkut muutokset käynnistävät tapahtuman ja toiset eivät. Tässä on luettelo muutoksista, jotka eivät käynnistä tapahtumaa:

• Kun muutat solun muotoilua (kirjasinkoko, väri, reunus jne.).
• Kun yhdistät solut. Tämä on yllättävää, koska joskus solujen yhdistäminen poistaa myös sisällön kaikista soluista paitsi vasemman yläkulman.
• Kun lisäät, poistat tai muokkaat solukommenttia.
• Kun lajittelet solualueen.
• Kun käytät Goal Seek -toimintoa.

Seuraavat muutokset käynnistävät tapahtuman (vaikka luulet, että sen ei pitäisi tapahtua):

• Kopiointi ja liittäminen alustavat tapahtuman.
• Muotoilun tyhjentäminen käynnistää tapahtuman.
• Oikeinkirjoituksen tarkistaminen käynnistää tapahtuman.

Alla on koodi, joka näyttää viestiruudun, jossa on muutetun solun osoite.

Yksityinen alityöarkin_muutos (ByVal -kohde alueena) MsgBox "Olet juuri muuttanut" & Target.Address End Sub

Vaikka tämä on hyödytön makro, se näyttää, kuinka voit käyttää Target -argumenttia selvittääksesi, mitä soluja on muutettu.

Katsotaan nyt pari hyödyllisempää esimerkkiä.

Oletetaan, että sinulla on solualue (esimerkiksi A1: D10) ja haluat näyttää kehotteen ja kysyä käyttäjältä, halusiko hän todella muuttaa solun tällä alueella vai ei, voit käyttää alla olevaa koodia.

Se näyttää kehotteen, jossa on kaksi painiketta - Kyllä ja Ei. Jos käyttäjä valitsee ”Kyllä”, muutos on tehty, muuten se peruutetaan.

Yksityinen alityöarkin_muutos (ByVal -tavoite alueena) Jos Target.Row <= 10 Ja Target.Column <= 4 Sitten Ans = MsgBox ("Teet muutoksia soluihin A1: D10. Haluatko varmasti sen?", vbKyllä Ei

Yllä olevassa koodissa tarkistamme, onko Kohdesolu neljässä ensimmäisessä sarakkeessa ja ensimmäisessä 10 rivissä. Jos näin on, viestiruutu näytetään. Lisäksi jos käyttäjä on valinnut viestiruudussa Ei, muutos peruutetaan (Application.Undo -komennolla).

Huomaa, että olen käyttänyt Application.EnableEvents = False ennen Application.Undo -riviä. Ja sitten käänsin sen käyttämällä Application.EnableEvent = True seuraavalla rivillä.

Tätä tarvitaan, koska kun kumoaminen tapahtuu, se laukaisee myös muutostapahtuman. Jos en aseta EnableEvent -asetusta arvoon False, se käynnistää muutostapahtuman jatkuvasti.

Voit myös seurata muutoksia nimetyllä alueella muuttotapahtuman avulla. Jos sinulla on esimerkiksi nimetty alue nimeltä "DataRange" ja haluat näyttää kehotteen, jos käyttäjä tekee muutoksia tähän nimettyyn alueeseen, voit käyttää alla olevaa koodia:

Yksityinen alityökirjan_muutos (ByVal -tavoite alueena) Dim DRange as Range Set DRange = Range ("DataRange") Jos ei leikkaa (kohde, DRange) ei ole mitään sitten MsgBox "Teit juuri muutoksen tietoalueelle" End If End Sub

Yllä oleva koodi tarkistaa, onko solussa/alueessa, johon olet tehnyt muutokset, mitään tietoalueelle yhteisiä soluja. Jos näin on, se näyttää viestiruudun.

Työkirjan valintaMuuta tapahtuma

Valinnanmuutostapahtuma käynnistyy aina, kun laskentataulukossa tapahtuu valinnan muutos.

Alla oleva koodi laskee taulukon uudelleen heti, kun muutat valintaa.

Yksityinen alityöarkki_SelectionChange (ByVal Target as Range) -sovellus.

Toinen esimerkki tästä tapahtumasta on, kun haluat korostaa valitun solun aktiivisen rivin ja sarakkeen.

Jotain alla olevan kuvan mukaisesti:

Seuraava koodi voi tehdä tämän:

Private Sub Worksheet_SelectionChange (ByVal Target as Range) Solut.Interior.ColorIndex = xlNone ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Päättyy End Sub

Koodi poistaa ensin taustavärin kaikista soluista ja käyttää sitten koodissa mainittua väriä aktiiviseen riviin ja sarakkeeseen.

Ja se on tämän koodin ongelma. Se poistaa värin kaikista soluista.

Jos haluat korostaa aktiivisen rivin/sarakkeen pitäen muiden solujen värit ennallaan, käytä tässä opetusohjelmassa esitettyä tekniikkaa.

Työkirjan DoubleClick -tapahtuma

Tämä on yksi suosikkini laskentataulukkotapahtumista, ja näet paljon opetusohjelmia, joissa olen käyttänyt tätä (kuten tätä tai tätä).

Tämä tapahtuma käynnistyy, kun kaksoisnapsautat solua.

Näytän kuinka mahtavaa tämä on.

Alla olevan koodin avulla voit kaksoisnapsauttaa solua ja se käyttää taustaväriä, muuttaa fonttiväriä ja tekee solun tekstistä lihavoidun;

Yksityinen alityöarkki_BeforeDoubleClick (ByVal -kohdealueena, Peruuta totuusarvona) Peruuta = Tosi kohde.

Tästä voi olla hyötyä, kun käyt läpi soluluetteloa ja haluat korostaa muutamia valittuja soluja. Voit toistaa viimeisen vaiheen F4 -näppäimellä, mutta se voi käyttää vain yhtä muotoilua. Tämän kaksoisnapsautustapahtuman avulla voit käyttää kaikkia kolmea vain kaksoisnapsautuksella.

Huomaa, että yllä olevassa koodissa olen tehnyt arvon Peruuta = Tosi.

Tämä tehdään niin, että kaksoisnapsautuksen oletustoiminto on poistettu käytöstä - päästäksesi muokkaustilaan. Jos Peruuta = Totta, Excel ei päästäisi muokkaustilaan, kun kaksoisnapsautat solua.

Tässä on toinen esimerkki.

Jos sinulla on tehtäväluettelo Excelissä, voit käyttää kaksoisnapsautustapahtumaa ja käyttää yliviivattua muotoa merkitäksesi tehtävän valmiiksi.

Jotain alla olevan kuvan mukaisesti:

Tässä on koodi, joka tekee tämän:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Cancel = True CurrFormat = Target.Font.Strikethrough If CurrFormat then Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End If End Sub

Huomaa, että tässä koodissa olen tehnyt kaksoisnapsautuksen vaihtotapahtumana. Kun kaksoisnapsautat solua, se tarkistaa, onko yliviivattu muoto jo käytössä. Jos se on ollut, kaksoisnapsautus poistaa yliviivatun muodon, ja jos ei ole, niin yliviivausmuotoa käytetään.

Excel VBA OnTime -tapahtuma

Tässä artikkelissa tähän mennessä näkemämme tapahtumat liittyivät johonkin Excel -objektiin, olipa se sitten työkirja, laskentataulukko, kaavioarkki tai UserForms jne.

OnTime -tapahtuma on erilainen kuin muut tapahtumat, koska se voidaan tallentaa tavalliseen VBA -moduuliin (kun taas muut oli sijoitettava objektien, kuten ThisWorkbookin tai laskentataulukoiden tai UserForms -tiedostojen, ikkunaan).

Tavallisessa VBA -moduulissa sitä käytetään sovellusobjektin menetelmänä.

Tapahtuman katsotaan johtuvan siitä, että se voidaan laukaista määrittämäsi ajan perusteella. Jos esimerkiksi haluan, että taulukko lasketaan uudelleen 5 minuutin välein, voin käyttää sitä OnTime -tapahtumassa.

Tai jos haluan näyttää viestin/muistutuksen tiettynä kellonaikana, voin käyttää OnTime -tapahtumaa.

Alla on koodi, joka näyttää viestin klo 14.00 joka päivä.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" Lopeta Sub Sub ShowMessage () MsgBox "On lounasaika" Loppuosa

Muista, että sinun on asetettava tämä koodi tavalliseen VBA -moduuliin,

Lisäksi, vaikka OnTime -tapahtuma laukaistaan ​​määritettynä aikana, sinun on suoritettava makro manuaalisesti milloin tahansa. Kun olet suorittanut makron, se odottaa klo 14.00 ja kutsuu sitten ShowMessage -makron.

ShowMessage -makro näyttää sitten viestin.

OnTime -tapahtuma sisältää neljä argumenttia:

Application.OnTime (Aikaisin aika, Menettely, LatestTime, Ajoittaa)

• Aikaisin aika: Aika, jolloin haluat suorittaa toimenpiteen.
• Menettely: Suoritettavan menettelyn nimi.
• LatestTime (valinnainen): Jos toinen koodi on käynnissä ja määritettyä koodiasi ei voida suorittaa määritettyyn aikaan, voit määrittää LatestTime -ajan, jota sen pitäisi odottaa. Se voi olla esimerkiksi EarliestTime + 45 (mikä tarkoittaa, että se odottaa 45 sekuntia, ennen kuin toinen menettely suoritetaan). Jos toimenpide ei pysty suorittamaan edes 45 sekunnin kuluttua, se hylätään. Jos et määritä tätä, Excel odottaa, kunnes koodi voidaan suorittaa, ja suorittaa sen sitten.
• Aikataulu (valinnainen): Jos asetuksena on Tosi, se ajastaa uuden aikamenettelyn. Jos epätosi, se peruuttaa aiemmin asetetun menettelyn. Oletuksena tämä on totta.

Yllä olevassa esimerkissä käytimme vain kahta ensimmäistä argumenttia.

Katsotaanpa toista esimerkkiä.

Alla oleva koodi päivittää laskentataulukon 5 minuutin välein.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Laske NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () Virhe Jatka seuraavaksi Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Yllä oleva koodi päivittää laskentataulukon 5 minuutin välein.

Se käyttää Nyt -toimintoa nykyisen ajan määrittämiseen ja lisää sitten 5 minuuttia nykyiseen aikaan.

OnTime -tapahtuma jatkuu, kunnes lopetat sen. Jos suljet työkirjan ja Excel -sovellus on edelleen käynnissä (muut työkirjat ovat auki), työkirja, jossa OnTime -tapahtuma on käynnissä, avautuu uudelleen.

Tämä hoidetaan paremmin pysäyttämällä erityisesti OnTime -tapahtuma.

Yllä olevassa koodissa minulla on StopRefresh -koodi, mutta sinun on suoritettava se pysäyttääksesi OnTime -tapahtuman. Voit tehdä tämän manuaalisesti, määrittää sen painikkeelle ja tehdä sen painamalla painiketta tai kutsua sen työkirjan sulkemistapahtumasta.

Yksityinen alityökirja_BeforeClose (Peruuta Booleanina) Call StopRefresh End Sub

Yllä oleva BeforeClose -tapahtumakoodi menee ThisWorkbook -koodi -ikkunaan.

Excel VBA OnKey -tapahtuma

Kun työskentelet Excelin kanssa, se seuraa jatkuvasti käyttämiäsi näppäinpainalluksia. Tämän avulla voimme käyttää näppäinpainalluksia tapahtuman laukaisijana.

OnKey -tapahtumassa voit määrittää näppäinpainalluksen (tai näppäinyhdistelmien yhdistelmän) ja koodin, joka suoritetaan, kun kyseistä näppäintä käytetään. Kun näitä näppäimiä painetaan, se suorittaa koodin sille.

Aivan kuten OnTime -tapahtumassa, sinulla on oltava tapa peruuttaa OnKey -tapahtuma. Lisäksi kun asetat OnKey -tapahtuman tietylle näppäinpainallukselle, se tulee saataville kaikissa avoimissa työkirjoissa.

Ennen kuin näytän sinulle esimerkin OnKey -tapahtuman käytöstä, haluan ensin jakaa avainkoodit, jotka ovat käytettävissäsi VBA: ssa.

AVAIN	KOODI
Askelpalautin	{BACKSPACE} tai {BS}
Tauko	{TAUKO}
Caps Lock	{CAPSLOCK}
Poistaa	{DELETE} tai {DEL}
Alanuoli	{ALAS}
Loppu	{END}
Tulla sisään	~
Enter (nueric -näppäimistöllä)	{TULLA SISÄÄN}
Paeta	{ESCAPE} tai {ESC}
Koti	{KOTI}
Ins	{LISÄÄ}
Vasen nuoli	{VASEN}
Numerolukko	{NUMEROLUKKO}
Sivu alas	{PGDN}
Sivu ylös	{PGUP}
Oikea nuoli	{OIKEA}
Vierityslukko	{SCROLLOCK}
Välilehti	{TAB}
Nuoli ylöspäin	{YLÖS}
F1 - F15	{F1} - {F15}

Kun sinun on käytettävä mitä tahansa onkey -tapahtumaa, sinun on käytettävä sen koodia.

Yllä olevassa taulukossa on yksittäisten näppäinpainallusten koodit.

Voit myös yhdistää nämä seuraaviin koodeihin:

• Siirtää: + (Plus-merkki)
• Ohjaus: ^ (Caret)
• Vaihtoehto: % (Prosenttiosuus)

Esimerkiksi Alt F4: lle sinun on käytettävä koodia: "%{F4}” - jossa % on ALT -näppäin ja {F4} on F4 -näppäin.

Katsotaanpa nyt esimerkkiä (muista, että OnKey -tapahtumien koodi on sijoitettu tavalliseen VBA -moduuliin).

Kun painat PageUp- tai PageDown -näppäintä, se hyppää 29 riviä aktiivisen solun ylä-/alapuolelle (ainakin se tekee sitä kannettavalla tietokoneellani).

Jos haluat, että se hyppää vain 5 riviä kerrallaan, voit käyttää alla olevaa koodia:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" Lopeta Sub Sub PageUpMod () Virhe Jatka seuraava ActiveCell.Offset (-5, 0). Alisivu PageDownMod () On Virhe Jatka seuraava ActiveCell. Offset (5, 0).

Kun suoritat koodin ensimmäisen osan, se suorittaa OnKey -tapahtumat. Kun tämä on suoritettu, PageUp- ja PageDown -näppäinten käyttäminen saa kohdistimen hyppäämään vain 5 riviä kerrallaan.

Huomaa, että olemme käyttäneet "On Error Resume Next" -painiketta varmistaaksemme, että virheet jätetään huomiotta. Nämä virheet voivat ilmetä, kun painat PageUp -näppäintä, vaikka olisit laskentataulukon yläosassa. Koska hyppääviä rivejä ei enää ole, koodi näyttää virheen. Mutta koska olemme käyttäneet "On Error Resume Next", se jätetään huomiotta.

Varmistaaksesi, että nämä OnKey -tapahtumat ovat käytettävissä, sinun on suoritettava koodin ensimmäinen osa. Jos haluat tämän olevan käytettävissä heti, kun avaat työkirjan, voit sijoittaa tämän ThisWorkbook -koodi -ikkunaan.

Private Sub Workbook_Open () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub

Alla oleva koodi palauttaa avaimet normaalitoimintoihinsa.

Sub Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" Lopeta ala

Jos et määritä toista argumenttia OnKey -menetelmässä, se palauttaa näppäinpainalluksen normaaliin toimintaansa.

Jos haluat peruuttaa näppäinpainalluksen toiminnot niin, että Excel ei tee mitään, kun kyseistä näppäintä käytetään, sinun on käytettävä tyhjää merkkijonoa toisena argumenttina.

Alla olevassa koodissa Excel ei tee mitään, kun käytämme PageUp- tai PageDown -näppäimiä.

Ala Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" Lopeta ala

Tapahtumien poistaminen käytöstä VBA: ssa

Joskus sinun on ehkä poistettava tapahtumat käytöstä, jotta koodisi toimisi oikein.

Oletetaan esimerkiksi, että minulla on alue (A1: D10) ja haluan näyttää viestin aina, kun solua muutetaan tällä alueella. Joten näytän viestiruudun ja kysyn käyttäjältä, ovatko he varmoja, että he haluavat tehdä muutoksen. Jos vastaus on Kyllä, muutos tehdään ja jos vastaus on Ei, VBA kumoaa sen.

Voit käyttää alla olevaa koodia:

Yksityinen alityökirjan_muutos (ByVal -tavoite alueena) Jos Target.Row <= 10 Ja Target.Column <= 4 Sitten Ans = MsgBox ("Teet muutoksia soluihin A1: D10. Haluatko varmasti sen?", vbKylläEi) Lopeta, jos Jos Ans = vbEi sitten Sovellus. Kumoa Lopeta, jos Lopeta

Tämän koodin ongelma on se, että kun käyttäjä valitsee viestiruudussa Ei, toiminto peruutetaan (kuten olen käyttänyt Application.Undo).

Kun kumoaminen tapahtuu ja arvo muutetaan takaisin alkuperäiseksi, VBA -muutostapahtuma käynnistyy uudelleen ja käyttäjälle näytetään sama viesti.

Tämä tarkoittaa, että voit jatkaa klikkaamalla EI viestiruudussa ja se näkyy edelleen. Tämä tapahtuu, kun olet juuttunut äärettömään silmukkaan tässä tapauksessa.

Tällaisten tapausten välttämiseksi sinun on poistettava tapahtumat käytöstä, jotta muutostapahtuma (tai muu tapahtuma) ei käynnisty.

Seuraava koodi toimii tässä tapauksessa hyvin:

Yksityinen alityöarkin_muutos (ByVal -tavoite alueena) Jos Target.Row <= 10 Ja Target.Column <= 4 Sitten Ans = MsgBox ("Teet muutoksia soluihin A1: D10. Haluatko varmasti sen?", vbKyllä Ei

Yllä olevassa koodissa, aivan Application.Undo -rivin yläpuolella, olemme käyttäneet - Application.EnableEvents = False.

EnableEvents -asetuksen asettaminen arvoon False ei käynnistä mitään tapahtumaa (nykyisessä tai avoimessa työkirjassa).

Kun olemme lopettaneet kumoamisen, voimme vaihtaa EnableEvents -ominaisuuden takaisin arvoon True.

Muista, että tapahtumien poistaminen käytöstä vaikuttaa kaikkiin avoimiin työkirjoihin (tai avattuihin, kun EnableEvents on asetettu arvoon False). Jos esimerkiksi avaat koodin osana uuden työkirjan, työkirjan avaustapahtuma ei toimi.

Tapahtumien vaikutus Kumoa pino

Kerron ensin, mitä Undo Stack on.

Kun työskentelet Excelissä, se seuraa jatkuvasti toimintojasi. Kun teet virheen, voit aina palata edelliseen vaiheeseen painamalla Control + Z (eli kumota nykyinen toiminto).

Jos painat Control + Z kahdesti, siirryt kaksi askelta taaksepäin. Nämä suorittamasi vaiheet tallennetaan osana Kumoa pinoa.

Kaikki tapahtumat, jotka muuttavat laskentataulukkoa, tuhoavat tämän Kumoa pinon.Tämä tarkoittaa, että jos olen tehnyt viisi asiaa ennen tapahtuman käynnistämistä, en voi käyttää Control + Z -näppäintä palatakseni edellisiin vaiheisiin. Tapahtuman käynnistäminen on tuhonnut tämän pinon minulle.

Alla olevassa koodissa käytän VBA: ta aikaleiman syöttämiseen soluun A1 aina, kun laskentataulukko muuttuu.

Yksityinen alityökirjan_muutos (ByVal-kohde alueena) Application.EnableEvents = Väärä alue ("A1"). Arvo = Muoto (Nyt, "pp-kk-vvvv hh: mm: ss") Application.EnableEvents = Todellinen loppuosa

Koska teen muutoksia laskentataulukkoon, tämä tuhoaa kumoamispinon.

Huomaa myös, että tämä ei rajoitu vain tapahtumiin.

Jos sinulla on koodi, joka on tallennettu tavalliseen VBA -moduuliin ja teet muutoksen laskentataulukkoon, se tuhoaa myös Excelin kumoamispinon.

Esimerkiksi alla oleva koodi kirjoittaa vain tekstin "Hei" soluun A1, mutta jopa tämän suorittaminen tuhoaa kumoamispinon.

AlatyyppiHello () -alue ("A1"). Arvo = "Hei" Loppuosa

Saatat pitää myös seuraavista Excel VBA -opetusohjelmista:

• Solujen ja alueiden käyttö Excel VBA: ssa.
• Työskentely laskentataulukoiden kanssa Excel VBA: ssa.
• Työkirjojen käsittely Excel VBA: ssa.
• Excel VBA Loops - Lopullinen opas.
• IF IF Else Statementin käyttäminen Excel VBA: ssa.
• Seuraavan silmukan Excelissä.
• Käyttäjän määrittämien toimintojen luominen Excel VBA: ssa.
• Lisäosien luominen ja käyttäminen Excelissä.
• Luo ja käytä makroja tallentamalla henkilökohtaiseen makro -työkirjaan.