Excel VBA -tapahtumat

Tapahtumia tapahtuu jatkuvasti, kun käyttäjä avaa Excel -työkirjan ja alkaa tehdä erilaisia ​​toimintoja, kuten syöttää tietoja soluihin tai siirtyä arkkien välillä

Visual Basic Editorissa (ALT+F11) on jo määritetty alirutiinit, jotka voivat laukaista, kun käyttäjä tekee jotain esim. tietojen syöttäminen soluun. Alirutiini ei tarjoa mitään toimintakoodia, vain "Sub" -lauseke ja "End Sub" -lauseke, joiden välillä ei ole koodia. Ne ovat tehokkaasti lepotilassa, joten mitään ei tapahdu, ennen kuin syötät jonkin koodin.

Tässä on esimerkki laskentataulukon Muutos -tapahtumaan:

VBA -ohjelmoijana voit lisätä koodia, jotta tiettyjä asioita tapahtuu, kun käyttäjä suorittaa tietyn toiminnon. Tämä antaa sinulle mahdollisuuden hallita käyttäjää ja estää häntä tekemästä toimia, joita et halua heidän tekevän ja jotka voivat vahingoittaa työkirjaasi. Voit esimerkiksi haluta heidän tallentavan työkirjan oman kopionsa toisella nimellä, jotta ne eivät vaikuta alkuperäiseen, jota monet käyttäjät voivat käyttää.

Jos he sulkevat työkirjan, heitä kehotetaan automaattisesti tallentamaan muutokset. Työkirjassa on kuitenkin BeforeClose -tapahtuma, ja voit syöttää koodin, jotta työkirja ei sulkeudu ja tallenna tapahtuma. Voit sitten lisätä painikkeen itse laskentataulukkoon ja laittaa siihen oman Tallenna -rutiinin. Voit myös poistaa Tallenna -rutiinin käytöstä käyttämällä BeforeSave -tapahtumaa

Ymmärtäminen siitä, miten tapahtumat toimivat, on ehdottoman välttämätöntä VBA -ohjelmoijalle.

Tapahtumatyypit

Työkirja Tapahtumat - nämä tapahtumat käynnistetään sen perusteella, mitä käyttäjä tekee työkirjan kanssa. Niihin kuuluvat käyttäjän toimet, kuten työkirjan avaaminen, työkirjan sulkeminen, työkirjan tallentaminen, taulukon lisääminen tai poistaminen

Laskentataulukon tapahtumat - Käyttäjä laukaisee nämä tapahtumat tietyn laskentataulukon toimintojen avulla. Jokaisessa työkirjan laskentataulukossa on oma koodimoduuli, joka sisältää erilaisia ​​tapahtumia erityisesti kyseiselle laskentataulukolle (ei kaikille laskentataulukoille). Näitä ovat käyttäjän toimet, kuten solun sisällön muuttaminen, solun kaksoisnapsauttaminen tai solun napsauttaminen hiiren kakkospainikkeella.

Active X -tapahtumat - Active X -ohjaimet voidaan lisätä laskentataulukkoon Excel -valintanauhan kehittäjä -välilehden Lisää -kuvakkeen avulla. Nämä ovat usein painikkeita, joiden avulla käyttäjä voi suorittaa erilaisia ​​toimintoja koodin hallinnassa, mutta ne voivat olla myös kohteita, kuten pudotusvalikkoja. Active X -ohjainten käyttäminen laskentataulukon lomakeohjainten sijaan antaa mahdollisuuden ohjelmoitavuuteen. Active X -ohjaimet tarjoavat sinulle paljon enemmän joustavuutta ohjelmoinnin kannalta laskentataulukon lomakeohjainten käyttämiseen.

Esimerkiksi laskentataulukossasi voi olla kaksi avattavaa ohjainta. Haluat, että toisen avattavan luettelon käytettävissä oleva luettelo perustuu siihen, mitä käyttäjä valitsi ensimmäisessä avattavassa valikossa. Käyttämällä "Muuta" -tapahtumaa ensimmäisessä avattavassa valikossa voit luoda koodin, jonka avulla käyttäjä voi lukea, mitä hän on valinnut, ja päivittää sitten toisen avattavan valikon. Voit myös poistaa toisen avattavan valikon käytöstä, kunnes käyttäjä on tehnyt valinnan ensimmäisessä avattavassa valikossa

UserForm -tapahtumat - Voit lisätä ja suunnitella ammattimaisen näköisen lomakkeen ponnahdusikkunaksi. Kaikki lomakkeeseen asettamasi ohjaimet ovat Active X -ohjaimia ja niillä on samat tapahtumat kuin Active X -ohjaimilla, jotka voit sijoittaa laskentataulukkoon

Kaavion tapahtumat - Nämä tapahtumat liittyvät vain kaavioarkkiin, eivät laskentataulukon osana olevaan kaavioon. Näitä tapahtumia ovat kaavion koon muuttaminen tai kaavion valitseminen.

Sovellustapahtumat - Nämä käyttävät sovellusobjektia VBA: ssa. Esimerkit sallisivat koodin laukaisun, kun tiettyä näppäintä painetaan tai tietty aika saavutetaan. Voit ohjelmoida tilanteen, jossa työkirja jätetään auki 24/7 ja se tuo tietoja ulkoisesta lähteestä yön yli ennalta määrätyllä hetkellä.

Koodin käytön vaara tapahtumissa

Kun kirjoitat koodia tehdäksesi jotain, kun käyttäjä suorittaa tietyn toiminnon, sinun on pidettävä mielessä, että koodisi saattaa laukaista muita tapahtumia, jotka voivat johtaa koodisi jatkuvaan silmukkaan.

Oletetaan esimerkiksi, että käytät laskentataulukon "Muuta" -tapahtumaa niin, että kun käyttäjä lisää arvon soluun, kyseiseen soluun perustuva laskelma sijoitetaan heti sen oikealla puolella olevaan soluun.

Ongelmana on tässä se, että lasketun arvon sijoittaminen soluun laukaisee toisen "Change" -tapahtuman, joka puolestaan ​​laukaisee vielä uuden "Change" -tapahtuman, ja niin edelleen, kunnes koodisi loppuvat käytettävistä sarakkeista ja heittää ylös virheilmoitus.

Sinun on harkittava huolellisesti, kun kirjoitat tapahtuman koodia, jotta muut tapahtumat eivät käynnisty vahingossa

Poista tapahtumat käytöstä

Koodin avulla voit poistaa tapahtumat käytöstä kiertääksesi tämän ongelman. Sinun on lisättävä koodi tapahtumien poistamiseksi käytöstä tapahtumakoodin ollessa käynnissä ja ottamalla sitten tapahtumat uudelleen käyttöön koodin lopussa. Tässä on esimerkki siitä, miten se tehdään:

1234 Sub DisableEvents ()Application.EnableEvents = VääräApplication.EnableEvents = TosiEnd Sub

Muista, että tämä poistaa kaikki tapahtumat käytöstä suoraan Excel -sovelluksessa, joten tämä vaikuttaisi myös muihin Excelin toimintoihin. Jos käytät tätä jostain syystä, varmista, että tapahtumat kytketään uudelleen päälle myöhemmin.

Parametrien merkitys tapahtumissa

Tapahtumilla on yleensä parametrit, joiden avulla voit saada lisätietoja käyttäjän toiminnasta ja solun sijainnista.

Esimerkiksi laskentataulukon vaihtotapahtuma näyttää tältä:

1 Yksityinen alityöarkin_muutos (ByVal -kohde alueena)

Käyttämällä alueobjektia voit selvittää solurivin/sarakkeen koordinaatit, joissa käyttäjä todella on.

1234 Yksityinen alityöarkin_muutos (ByVal -kohde alueena)MsgBox Target.ColumnMsgBox Target.RowEnd Sub

Jos haluat koodisi toimivan vain tietyn sarakkeen tai rivinumeron kohdalla, lisäät aliohjelmasta poistuvan ehdon, jos sarake ei ole pakollinen.

123 Yksityinen alityöarkin_muutos (ByVal -kohde alueena)Jos kohde. Sarake 2 Sitten Lopeta AlEnd Sub

Tämä kiertää ongelman, jossa koodi käynnistää useita tapahtumia, koska se toimii vain, jos käyttäjä on muuttanut solun sarakkeessa 2 (sarake B)

Esimerkkejä työkirjan tapahtumista (ei tyhjentävä)

Työkirjan tapahtumat löytyvät VBE Project Explorerin ThisWorkbook -objektista. Sinun on valittava "Työkirja" koodi -ikkunan ensimmäisestä avattavasta valikosta ja sitten toinen pudotusvalikko näyttää kaikki saatavilla olevat tapahtumat

Työkirjan avoin tapahtuma

Tämä tapahtuma käynnistyy aina, kun käyttäjä avaa työkirjan. Voit käyttää sitä lähettääksesi tervetuloviestin käyttäjälle tallentamalla käyttäjänimen

123 Yksityinen alityökirja_Avaa ()MsgBox "Tervetuloa" ja Application.UserNameEnd Sub

Voit myös tarkistaa heidän käyttäjätunnuksensa piilotetulla arkilla olevan luettelon perusteella, onko heillä oikeus käyttää työkirjaa. Jos he eivät ole valtuutettu käyttäjä, voit näyttää viestin ja sulkea työkirjan, jotta he eivät voi käyttää sitä.

Työkirjan uuden arkin tapahtuma

Tämä tapahtuma käynnistyy, kun käyttäjä lisää uuden taulukon työkirjaan

Voit käyttää tätä koodia vain itsellesi uuden arkin lisäämiseen sen sijaan, että eri käyttäjät lisäävät arkkeja ja tekevät työkirjasta sotkun

1234567 Yksityinen alityökirja_Uusi -arkki (ByVal Sh objektina)Application.DisplayAlerts = VääräJos Application.UserName "Richard" SittenSh.DeleteLoppu JosApplication.DisplayAlerts = TottaEnd Sub

Huomaa, että sinun on poistettava hälytykset käytöstä, koska käyttäjän varoitus tulee näkyviin, kun taulukko poistetaan, jolloin käyttäjä voi kiertää koodisi. Varmista, että kytket hälytykset takaisin päälle myöhemmin!

Oletko kyllästynyt etsimään esimerkkejä VBA -koodista? Kokeile AutoMacroa!

Työkirja ennen tapahtuman tallentamista

Tämä tapahtuma käynnistyy, kun käyttäjä napsauttaa Tallenna -kuvaketta, mutta ennen kuin Tallenna todella tapahtuu

Kuten aiemmin on kuvattu, haluat ehkä estää käyttäjiä tallentamasta muutoksia alkuperäiseen työkirjaan ja pakottaa heidät luomaan uuden version laskentataulukon painikkeella. Sinun tarvitsee vain muuttaa Peruuta -parametri arvoon Tosi, eikä työkirjaa voi koskaan tallentaa tavanomaisella menetelmällä.

123 Yksityinen alityökirja_BeforeSave (ByVal SaveAsUI as Boolean, Cancel as Boolean)Peruuta = TottaEnd Sub

Työkirja ennen tapahtumaa

Tämän tapahtuman avulla voit estää käyttäjiä sulkemasta työkirjan ja pakottaa heidät poistumaan laskentataulukkopainikkeen kautta. Asetat jälleen "Peruuta" -parametriksi "Tosi". Punainen X Excel-ikkunan oikeassa yläkulmassa ei enää toimi.

123 Yksityinen alityökirja_BeforeClose (Peruuta Booleanina)Peruuta = TottaEnd Sub

Esimerkkejä laskentataulukon tapahtumista (ei tyhjentävä)

Laskentataulukkotapahtumat löytyvät VBE Project Explorerin tietyn arkin nimen objektista. Sinun on valittava "Työarkki" koodi -ikkunan ensimmäisestä avattavasta valikosta ja sitten toinen pudotusvalikko näyttää kaikki saatavilla olevat tapahtumat

Laskentataulukon muutostapahtuma

Tämä tapahtuma käynnistyy, kun käyttäjä tekee muutoksia laskentataulukkoon, kuten kirjoittaa uuden arvon soluun

Tämän tapahtuman avulla voit lisätä lisäarvon tai kommentin muutetun solun viereen, mutta kuten aiemmin keskusteltiin, et halua aloittaa tapahtumasilmukan aloittamista.

12345 Yksityinen alityöarkin_muutos (ByVal -kohde alueena)Jos kohde. Sarake 2 Sitten Lopeta AlActiveSheet.Cells (Target.Row, Target.Column + 1). Arvo = _ActiveSheet.Cells (Target.Row, Target.Column). Arvo * 1.1End Sub

Tässä esimerkissä koodi toimii vain, jos arvo on syötetty sarakkeeseen B (sarake 2). Jos tämä on totta, se lisää numeroon 10% ja sijoittaa sen seuraavaan vapaaseen soluun

Laskentataulukko ennen kaksoisnapsautustapahtumaa

Tämä tapahtuma laukaisee koodin, jos käyttäjä kaksoisnapsauttaa solua. Tämä voi olla erittäin hyödyllistä taloudellisissa raporteissa, kuten taseessa tai tuloslaskelmassa, jossa johtajat todennäköisesti haastavat luvut, varsinkin jos tulos on negatiivinen!

Voit käyttää tätä tarjotaksesi poraustoiminnon, joten kun johtaja haastaa tietyn numeron, hänen tarvitsee vain kaksoisnapsauttaa numeroa ja erittely näkyy osana raporttia.

Tämä on erittäin vaikuttavaa käyttäjän näkökulmasta ja säästää heidät jatkuvasti kysymästä "miksi tämä luku on niin korkea?"

Sinun on kirjoitettava koodi selvittääksesi numeron otsikko / ehdot (käyttämällä kohdeobjektiominaisuuksia) ja suodattamalla sitten taulukkotiedot ja kopioimalla ne sitten raporttiin.

VBA -ohjelmointi | Koodigeneraattori toimii sinulle!

Laskentataulukko Aktivoi tapahtuma

Tämä tapahtuma tapahtuu, kun käyttäjä siirtyy yhdestä taulukosta toiseen. Se koskee uutta taulukkoa, johon käyttäjä siirtyy.

Sitä voitaisiin käyttää varmistamaan, että uusi taulukko on täysin laskettu ennen kuin käyttäjä alkaa tehdä sille mitään. Sitä voidaan myös käyttää vain kyseisen laskentataulukon laskemiseen uudelleen laskematta koko työkirjaa uudelleen. Jos työkirja on suuri ja siinä on monimutkainen kaava, yhden arkin laskeminen uudelleen säästää paljon aikaa

123 Yksityinen alityöarkki_Activate ()ActiveSheet.LaskeEnd Sub

Active X -tapahtumat (ei tyhjentävä)

Kuten aiemmin keskusteltiin, voit lisätä Active X -ohjaimia suoraan laskentataulukkoon. Nämä voivat olla komentopainikkeita, pudotusvalikkoja ja luetteloruutuja

Active X -tapahtumat löytyvät VBE Project Explorerin tietyn arkin nimen objektista (johon lisäsit ohjausobjektin). Sinun on valittava Active X -ohjaimen nimi koodi -ikkunan ensimmäisestä avattavasta valikosta ja sitten toinen pudotusvalikko näyttää kaikki käytettävissä olevat tapahtumat

Komento -painike Napsauta Tapahtuma

Kun olet asettanut komentopainikkeen laskentataulukkoon, haluat sen tekevän jonkin toimenpiteen. Voit tehdä tämän lisäämällä koodin Click -tapahtumaan.

Voit helposti lisätä tähän "Oletko varma viestin?", Jotta tarkistus tehdään ennen koodin suorittamista

12345 Yksityinen alikomentoButton1_Click ()Himmennyspainike Palauta vaihtoehtonaButtonRet = MsgBox ("Oletko varma, että haluat tehdä tämän?", VbQuestion Tai vbYesNo)Jos ButtonRet = vbNo, sulje sitten SubEnd Sub

Pudotusvalikko (yhdistelmäruutu) Muuta tapahtuma

Active X -pudotusvalikossa on muutostapahtuma, joten jos käyttäjä valitsee tietyn kohteen avattavasta luettelosta, voit tallentaa valintasi tällä tapahtumalla ja kirjoittaa sitten koodin, joka mukauttaa taulukon tai työkirjan muita osia vastaavasti.

123 Yksityinen ali -yhdistelmälaatikko1_Muuta ()MsgBox "Valitsit" & ComboBox1.TextEnd Sub

VBA -ohjelmointi | Koodigeneraattori toimii sinulle!

Valitse ruutu (valintaruutu) Napsauta Tapahtuma

Voit lisätä rasti- tai valintaruudun laskentataulukkoon tarjotaksesi käyttäjälle vaihtoehtoja. Voit käyttää sen napsautustapahtumaa nähdäksesi, onko käyttäjä muuttanut mitään tässä. Palautetut arvot ovat tosi tai epätosi sen mukaan, onko se valittu vai ei.

123 Yksityinen alivalintaruutu1_Click ()MsgBox -valintaruutu 1. ArvoEnd Sub

UserForm -tapahtumat (ei tyhjentävä)

Excel tarjoaa sinulle mahdollisuuden suunnitella omia lomakkeita. Näitä voi olla erittäin hyödyllistä käyttää ponnahdusikkunoina tietojen keräämiseen tai tarjota useita vaihtoehtoja käyttäjälle. He käyttävät Active X -ohjaimia, kuten aiemmin on kuvattu, ja niillä on täsmälleen samat tapahtumat, vaikka tapahtumat riippuvat suuresti ohjaustyypistä.

Tässä on esimerkki yksinkertaisesta lomakkeesta:

Kun se näytetään, tältä se näyttää näytöllä

Käyttäisit lomakkeen tapahtumia esimerkiksi yrityksen oletusnimen syöttämiseen, kun lomake avataan, tarkistaaksesi, että yrityksen nimen syöttö on sama kuin jo laskentataulukossa, eikä sitä ole kirjoitettu väärin, ja lisätäksesi koodin napsautukseen tapahtumia OK- ja Peruuta -painikkeilla

Lomakkeen takana olevaa koodia ja tapahtumia voi tarkastella kaksoisnapsauttamalla mitä tahansa lomakkeen kohtaa

Ensimmäisestä avattavasta valikosta pääsee kaikkiin lomakkeen säätimiin. Toinen pudotusvalikko antaa pääsyn tapahtumiin

UserForm Aktivoi tapahtuma

Tämä tapahtuma käynnistyy, kun lomake aktivoidaan, yleensä kun se näytetään. Tätä tapahtumaa voidaan käyttää oletusarvojen määrittämiseen, esim. oletusnimi yrityksen nimen tekstikenttään

123 Yksityinen alikäyttäjäForm_Activate ()TextBox1.Text = "Oma yrityksen nimi"End Sub

VBA -ohjelmointi | Koodigeneraattori toimii sinulle!

Vaihda tapahtuma

Useimmissa lomakkeen säätimissä on muutostapahtuma, mutta tässä esimerkissä yrityksen nimen tekstikenttään voidaan käyttää tapahtumaa rajoittaakseen syötettävän yrityksen nimen pituutta

123456 Yksityinen tekstitekstiBox1_Change ()Jos Len (TextBox1.Text)> 20 SittenMsgBox "Nimi on rajoitettu 20 merkkiin", vbCriticalTextBox1.Text = ""Loppu JosEnd Sub

Napsauta Tapahtuma

Tämän tapahtuman avulla voit toimia, kun käyttäjä napsauttaa lomakkeen ohjaimia tai jopa itse lomaketta

Tässä lomakkeessa on OK -painike, ja kun olemme keränneet yrityksen nimen, haluaisimme sijoittaa sen laskentataulukon soluun tulevaa käyttöä varten

1234 Yksityinen alikomentoButton1_Click ()ActiveSheet.Range ("A1"). Arvo = TextBox1.TextMinä, piilotaEnd Sub

Tämä koodi toimii, kun käyttäjä napsauttaa OK -painiketta. Se laittaa yrityksen nimen syöttökentän arvon aktiivisen taulukon soluun A1 ja piilottaa lomakkeen niin, että käyttäjän ohjaus palautetaan takaisin laskentataulukkoon.

Kaavion tapahtumat

Kaavion tapahtumat toimivat vain kaavioilla, jotka ovat erillisellä kaavioarkilla, eivätkä kaaviossa, joka on sisällytetty tavalliseen laskentataulukkoon

Kaavion tapahtumat ovat jonkin verran rajallisia, eikä niitä voi käyttää laskentataulukossa, jossa voi olla useita kaavioita. Käyttäjät eivät myöskään välttämättä halua siirtyä numeroita sisältävästä laskentataulukosta kaavioarkiksi - tässä ei ole välitöntä visuaalista vaikutusta

Hyödyllisin tapahtuma olisi selvittää kaavion komponentti, jota käyttäjä on napsauttanut esim. segmentti ympyräkaaviossa tai palkki pylväskaaviossa, mutta tämä ei ole vakiotapahtuma -alueella käytettävissä oleva tapahtuma.

Tämä ongelma voidaan ratkaista käyttämällä luokkamoduulia lisäämällä hiiri alas -tapahtuma, joka palauttaa tiedot kaaviosta, jota käyttäjä on napsauttanut. Tätä käytetään laskentataulukon kaaviossa.

Tähän liittyy erittäin monimutkainen koodaus, mutta tulokset ovat upeita. Voit luoda porauslaskuja esim. käyttäjä napsauttaa ympyräkaaviosegmenttiä ja kaavio piilotetaan välittömästi ja tilalle ilmestyy toinen kaavio, joka näyttää alkuperäisen segmentin yksityiskohtaisen ympyräkaavion, tai voit luoda taulukkotiedot, jotka tukevat ympyräkaavion tätä segmenttiä.

Sovellustapahtumat

Voit käyttää VBA: n sovellusobjektia koodin laukaisemiseen tietyn tapahtuman mukaan

VBA -ohjelmointi | Koodigeneraattori toimii sinulle!

Application.OnTime

Tämän avulla voit laukaista koodinpalan säännöllisin väliajoin niin kauan kuin työkirja on ladattu Exceliin. Haluat ehkä tallentaa työkirjasi automaattisesti toiseen kansioon 10 minuutin välein tai jättää laskentataulukon toimimaan yön yli, jotta saat uusimmat tiedot ulkoisesta lähteestä.

Tässä esimerkissä alirutiini syötetään moduuliin. Se näyttää viestiruudun 5 minuutin välein, vaikka tämä voisi helposti olla toinen koodattu menettely. Samalla se nollaa ajastimen nykyiseen aikaan ja 5 minuuttiin lisää.

Aina kun se käynnistyy, ajastin nollautuu käyttämään samaa alirutiinia vielä 5 minuutin kuluttua.

1234 Sub TestOnTime ()MsgBox "OnTimen testaus"Application.OnTime (Nyt () + TimeValue ("00:05:00")), "TestOnTime"End Sub

Application.OnKey

Tämän toiminnon avulla voit suunnitella omia pikanäppäimiäsi. Voit asettaa minkä tahansa näppäinyhdistelmän kutsumaan luomuksesi alirutiiniksi.

Tässä esimerkissä a -kirjain ohjataan uudelleen siten, että sen sijaan, että sijoitettaisiin a -kirjain soluun, se näyttää viestiruudun. Tämä koodi on sijoitettava lisättyyn moduuliin.

123456 AlitestiKeyPress ()Application.OnKey "a", "TestKeyPress"End SubAlitestiKeyPress ()MsgBox "Painoit" a "End Sub

Suoritat ensin alirutiinin "TestKeyPress". Sinun on suoritettava tämä vain kerran. Se kertoo Excelille, että joka kerta, kun "a" kirjainta painetaan, se kutsuu alirutiinia "TestKeyPress". Alirutiini "TestKeyPress" näyttää vain viestiruudun, joka kertoo, että painit näppäintä "a". Se voi tietysti ladata lomakkeen tai tehdä kaikenlaista muuta.

Voit käyttää mitä tahansa näppäinyhdistelmää, jota voit käyttää SendKeys -toiminnon kanssa

Voit peruuttaa tämän toiminnon suorittamalla OnKey -lausekkeen ilman menettelytapaparametria.

123 Sub CancelOnKey ()Application.OnKey "a"End Sub

Kaikki on nyt palannut normaaliksi.

wave wave wave wave wave