Exceli VBA sündmused

Sündmused toimuvad kogu aeg, kui kasutaja avab Exceli töövihiku ja hakkab tegema erinevaid toiminguid, näiteks sisestama andmeid lahtritesse või liikuma lehtede vahel

Visual Basic Editoris (ALT+F11) on juba seadistatud alamrutiinid, mis võivad vallanduda, kui kasutaja teeb midagi, nt. andmete sisestamine lahtrisse. Alamrutiin ei anna ühtegi tegevuskoodi, vaid alamlause ja avaldus „lõpp”, mille vahel pole koodi. Need on tegelikult uinuvad, nii et midagi ei juhtu enne, kui sisestate mõne koodi.

Siin on näide töölehe sündmuse „Muuda” põhjal:

VBA programmeerijana saate lisada koodi, et teatud asjad juhtuksid, kui kasutaja konkreetse toimingu teeb. See annab teile võimaluse kasutajat kontrollida ja takistada tal tegutsemast, mida te ei soovi, et ta teeks ja mis võiks teie töövihikut kahjustada. Näiteks võite soovida, et nad salvestaksid töövihikust oma individuaalse koopia teise nime all, nii et need ei mõjutaks originaali, mida paljud kasutajad võivad kasutada.

Kui nad töövihiku sulgevad, palutakse neil automaatselt muudatused salvestada. Töövihikul on aga sündmus „Enne sulgemist” ja saate sisestada koodi, et vältida töövihiku sulgemist ja sündmuse „Salvesta” käivitamist. Seejärel saate töölehele lisada nupu ja panna sellele oma salvestamisrutiini. Saate salvestamise rutiini keelata ka sündmuse „BeforeSave” abil

Ürituste toimimise mõistmine on VBA programmeerija jaoks hädavajalik.

Sündmuste tüübid

Töövihik Sündmused - need sündmused vallandatakse selle põhjal, mida kasutaja teeb töövihikuga ise. Need hõlmavad kasutaja toiminguid, nagu töövihiku avamine, töövihiku sulgemine, töövihiku salvestamine, lehe lisamine või kustutamine

Töölehe sündmused - neid sündmusi käivitab kasutaja, kes teeb konkreetse töölehe toiminguid. Igal töövihiku töölehel on individuaalne koodimoodul, mis sisaldab erinevaid sündmusi just selle töölehe jaoks (mitte kõigi töölehtede jaoks). Nende hulka kuuluvad kasutaja toimingud, näiteks lahtri sisu muutmine, lahtril topeltklõps või lahtril paremklõps.

Aktiivsed X juhtimissündmused - Active X -juhtelemente saab töölehele lisada Exceli lindi vahekaardi „Arendaja” ikooni „Lisa” abil. Need on sageli nupukontrollid, mis võimaldavad kasutajal teie koodi kontrolli all erinevaid toiminguid teha, kuid need võivad olla ka objektid, näiteks rippmenüüd. Active X juhtelementide kasutamine töölehel vormi juhtelementide asemel annab programmeeritavusele täieliku ulatuse. Active X juhtelemendid annavad teile programmeerimise seisukohast palju rohkem paindlikkust, kui kasutada töölehel vormi juhtelemente.

Näiteks võib teie töölehel olla kaks rippmenüüd. Soovite, et teises rippmenüüs saadaolev loend põhineks sellel, mida kasutaja valis esimeses rippmenüüs. Kasutades esimesel rippmenüül sündmust „Muuda”, saate luua koodi, mille abil saate lugeda, mida kasutaja on valinud, ja seejärel värskendada teist rippmenüüd. Võite ka teise rippmenüü deaktiveerida, kuni kasutaja on esimeses rippmenüüs valiku teinud

UserFormi sündmused - Saate sisestada ja kujundada professionaalse välimusega vormi, mida kasutada hüpikaknana. Kõik vormile paigutatud juhtelemendid on Active X -juhtelemendid ja neil on samad sündmused kui Active X -juhtelementidel, mille võite töölehele paigutada

Diagrammi sündmused - Need sündmused on seotud ainult diagrammilehega, mitte graafikuga, mis kuvatakse töölehe osana. Need sündmused hõlmavad diagrammi suuruse muutmist või diagrammi valimist.

Rakendussündmused - Need kasutavad VBA -s rakenduse objekti. Näited võimaldaksid koodi vallandada teatud klahvi vajutamisel või teatud aja saabumisel. Saate programmeerida olukorra, kus töövihik jäetakse ööpäevaringselt avatuks ja see impordib andmed välisallikast üleöö etteantud ajal.

Sündmustel koodi kasutamise ohud

Kui kirjutate koodi, et teha midagi, kui kasutaja teeb teatud toimingu, peate meeles pidama, et teie kood võib käivitada muid sündmusi, mis võivad teie koodi pidevasse tsüklisse viia.

Oletame näiteks, et kasutate töölehel sündmust „Muuda”, nii et kui kasutaja paneb lahtrisse väärtuse, paigutatakse selle lahtri põhjal tehtud arvutus sellest paremal asuvasse lahtrisse.

Probleem on selles, et arvutatud väärtuse paigutamine lahtrisse käivitab teise sündmuse „Muuda”, mis omakorda käivitab veel ühe sündmuse „Muuda” ja nii edasi, kuni teie koodil on kasutatavad veerud otsas ja viskab üles veateade.

Sündmuse koodi kirjutamisel peate hoolikalt mõtlema, et vältida muude sündmuste tahtmatut käivitamist

Keela sündmused

Selle probleemi lahendamiseks saate sündmuste keelamiseks kasutada koodi. Peate lisama koodi sündmuste keelamiseks ajal, mil teie sündmuskood töötab, ja lubama sündmused koodi lõpus uuesti. Siin on näide, kuidas seda teha:

1234	Sub DisableEvents ()Application.EnableEvents = ValeApplication.EnableEvents = TõsiEnd Sub

Pidage meeles, et see keelab kõik sündmused kogu Exceli rakenduses, nii et see mõjutaks ka teisi Exceli funktsioone. Kui kasutate seda mingil põhjusel, veenduge, et sündmused oleksid pärast seda uuesti sisse lülitatud.

Parameetrite tähtsus sündmustel

Sündmustel on tavaliselt parameetrid, mille abil saate lisateavet selle kohta, mida kasutaja teeb, ja lahtri asukoha, kus nad asuvad.

Näiteks töölehe muutmise sündmus näeb välja selline:

1	Privaatne alamlehe muutmine (ByVal -i sihtmärk vahemikuna)

Vahemiku objekti kasutades saate teada lahtri rea/veeru koordinaate, milles kasutaja tegelikult asub.

1234	Privaatne alamlehe muutmine (ByVal Target kui vahemik)MsgBox Target. VeergMsgBoxi sihtmärk. RidaEnd Sub

Kui soovite, et teie kood töötaks ainult teatud veeru või rea numbril, lisage tingimus, mis väljub alamprogrammist, kui veerg pole nõutav.

123	Privaatne alamlehe muutmine (ByVal Target kui vahemik)Kui sihtmärk. Veerg 2, seejärel välju alammenüüstEnd Sub

See lahendab probleemi, mille korral kood käivitab mitu sündmust, kuna see toimib ainult siis, kui kasutaja on veeru 2 lahtrit (veerg B) muutnud

Töövihiku sündmuste näited (pole ammendavad)

Töövihiku sündmused leiate VBE Project Exploreri objekti „ThisWorkbook” alt. Koodiakna esimeses rippmenüüs peate valima "Töövihik" ja seejärel näitab teine ​​rippmenüü kõiki saadaolevaid sündmusi

Töövihiku avatud sündmus

See sündmus käivitatakse alati, kui kasutaja avab töövihiku. Saate seda kasutada kasutajale kasutajanime jäädvustamiseks tervitussõnumi saatmiseks

123	Privaatne alamtöövihik_Avatud ()MsgBox "Tere tulemast" ja Application.UserNameEnd Sub

Samuti saate nende kasutajanime võrrelda peidetud lehel oleva loendiga, et näha, kas neil on töövihikule juurdepääs. Kui nad pole volitatud kasutajad, saate kuvada sõnumi ja sulgeda töövihiku, et nad seda kasutada ei saaks.

Töövihiku uue lehe sündmus

See sündmus käivitatakse, kui kasutaja lisab töövihikusse uue lehe

Selle koodi abil saate lubada endale ainult uue lehe lisamist, selle asemel et eri kasutajad kõik lehti lisada ja töövihikut segi ajada

1234567

Privaatne alamtöövihiku_leht (ByVal Sh kui objekt)Application.DisplayAlerts = ValeKui Application.UserName "Richard" SiisSh.DeleteLõpp KuiApplication.DisplayAlerts = TõsiEnd Sub

Pange tähele, et peate hoiatused välja lülitama, kuna lehe kustutamisel ilmub kasutaja hoiatus, mis võimaldab kasutajal teie koodist mööda hiilida. Veenduge, et lülitate hoiatused hiljem uuesti sisse!

Kas olete väsinud VBA koodinäidete otsimisest? Proovige AutoMacrot!

Töövihik enne sündmuse salvestamist

See sündmus käivitatakse siis, kui kasutaja klõpsab ikoonil „Salvesta”, kuid enne seda, kui salvestamine tegelikult toimub

Nagu varem kirjeldatud, võiksite takistada kasutajatel muudatuste salvestamist algsesse töövihikusse ja sundida neid töölehel oleva nupu abil uue versiooni looma. Kõik, mida peate tegema, on muuta parameetri „Tühista” väärtuseks Tõene ja töövihikut ei saa tavapärase meetodiga kunagi salvestada.

123	Privaatne alamtöövihik_BeforeSave (ByVal SaveAsUI kui Boolean, Cancel as Boolean)Tühista = TõsiEnd Sub

Töövihik enne ürituse sulgemist

Selle sündmuse abil saate takistada kasutajatel töövihiku sulgemist ja sundida neid uuesti töölehe nupu kaudu väljuma. Jällegi määrate parameetri „Tühista” väärtuseks „Tõene”. Punane X Exceli akna paremas ülanurgas ei tööta enam.

123	Privaatne alamtöövihik_BeforeClose (Tühista kui Boolean)Tühista = TõsiEnd Sub

Töölehe sündmuste näited (pole ammendavad)

Töölehe sündmused leiate konkreetse lehe nimeobjekti alt VBE Project Exploreris. Koodiakna esimeses rippmenüüs peate valima „Tööleht” ja seejärel näitab teine ​​rippmenüü kõiki saadaolevaid sündmusi

Töölehe muutmise sündmus

See sündmus käivitatakse, kui kasutaja teeb töölehel muudatusi, näiteks sisestab lahtrisse uue väärtuse

Seda sündmust saate kasutada muudetud lahtri kõrvale lisaväärtuse või kommentaari lisamiseks, kuid nagu varem arutatud, ei soovi te sündmuste ringi käivitamist alustada.

12345

Privaatne alamlehe muutmine (ByVal Target kui vahemik)Kui sihtmärk. Veerg 2, seejärel välju alammenüüstActiveSheet.Cells (Target.Row, Target.Column + 1). Väärtus = _ActiveSheet.Cells (Target.Row, Target.Column). Väärtus * 1.1End Sub

Selles näites töötab kood ainult siis, kui väärtus sisestatakse veergu B (veerg 2). Kui see on tõsi, lisab see arvule 10% ja paigutab selle järgmisse saadaolevasse lahtrisse

Tööleht enne topeltklõpsu sündmust

See sündmus käivitab koodi, kui kasutaja topeltklõpsab lahtrit. See võib olla väga kasulik selliste finantsaruannete jaoks nagu bilanss või kasumiaruanne, kus juhid vaidlustavad arvud, eriti kui tulemus on negatiivne!

Saate seda kasutada põhjalikuma teabe pakkumiseks, nii et kui haldur vaidlustab konkreetse numbri, ei pea nad muud tegema kui topeltklõpsama numbrit ja jaotus kuvatakse aruande osana.

See on kasutaja seisukohast väga muljetavaldav ja säästab neid pidevalt küsides: „Miks on see arv nii suur?”

Peaksite kirjutama koodi, et teada saada numbri pealkiri / kriteeriumid (kasutades sihtobjekti atribuute), seejärel filtreerige tabeliandmed ja kopeerige need seejärel aruandesse.

VBA programmeerimine | Koodigeneraator töötab teie jaoks!

Tööleht Aktiveeri sündmus

See sündmus ilmneb siis, kui kasutaja liigub ühelt lehelt teisele. See kehtib uue lehe kohta, kuhu kasutaja liigub.

Seda saab kasutada uue lehe täieliku arvutamise tagamiseks enne, kui kasutaja sellega midagi tegema hakkab. Seda saab kasutada ka selle konkreetse lehe uuesti arvutamiseks ilma kogu töövihiku uuesti arvutamiseta. Kui töövihik on suur ja selles on keeruline valem, säästab ühe lehe uuesti arvutamine palju aega

123	Privaatne alamleht_Activate ()ActiveSheet. ArvutageEnd Sub

Aktiivse X juhtimissündmused (pole ammendavad)

Nagu varem arutatud, saate Active X juhtelemente lisada otse töölehele. Need võivad olla käsunupud, rippmenüüd ja loendiboksid

Active X sündmused leiate konkreetse lehe nimeobjekti alt (kuhu juhtelemendi lisasite) VBE Project Exploreris. Koodiakna esimeses rippmenüüs peate valima Active X juhtelemendi nime ja seejärel näitab teine ​​rippmenüü kõiki saadaolevaid sündmusi

Käsunupp Klõpsake sündmust

Kui olete arvutustabelisse käsunupu pannud, soovite, et see teeks teatud toiminguid. Selleks lisage kliki sündmusele kood.

Sellele saate hõlpsalt panna märke „Kas olete kindel sõnum?”, Et enne koodi käivitamist kontrollida

12345

Privaatne alamkäsk Button1_Click ()Dim ButtonRet VariantinaButtonRet = MsgBox ("Kas olete kindel, et soovite seda teha?", VbQuestion Või vbYesNo)Kui ButtonRet = vbNo, siis väljuge alammenüüstEnd Sub

Rippmenüü (liitkast) muutmise sündmus

Active X rippmenüül on muutmise sündmus, nii et kui kasutaja valib ripploendist konkreetse üksuse, saate selle sündmuse abil jäädvustada oma valiku ja seejärel kirjutada koodi lehe või töövihiku muude osade kohandamiseks.

123	Privaatne alamkombinatsioonikast1_muutus ()MsgBox "Teie valisite" & ComboBox1.TextEnd Sub

VBA programmeerimine | Koodigeneraator töötab teie jaoks!

Märkige ruut (märkeruut) Klõpsake nuppu Sündmus

Saate töölehele lisada linnukese või märkeruudu, et pakkuda kasutajale valikuvõimalusi. Selle klõpsamisürituse abil saate näha, kas kasutaja on selles midagi muutnud. Tagastatud väärtused on tõesed või valed vastavalt sellele, kas see on märgitud või mitte.

123	Privaatne alamkontrollBox1_Click ()MsgBox CheckBox1.VäärtusEnd Sub

UserFormi sündmused (pole ammendavad)

Excel pakub teile võimalust oma vorme kujundada. Need võivad olla väga kasulikud teabe kogumiseks hüpikakendena või kasutajale mitme valiku pakkumiseks. Nad kasutavad Active X juhtelemente, nagu eelnevalt kirjeldatud, ja neil on täpselt samad sündmused, kuigi sündmused sõltuvad suuresti juhtelemendi tüübist.

Siin on näide lihtsast vormist:

Kui see kuvatakse, näeb see ekraanil välja

Vormis olevate sündmuste abil saaksite teha näiteks ettevõtte avamisel vaikimisi ettevõtte nime, kontrollida, kas ettevõtte nime sisend nõustub arvutustabelis juba sisalduva nimega ja pole õigesti kirjutatud, ning lisada klõpsule kood sündmusi nuppudel „OK” ja „Tühista”

Vormi taga olevat koodi ja sündmusi saab vaadata topeltklõpsuga mis tahes kohas vormil

Esimene rippmenüü annab juurdepääsu kõigile vormi juhtelementidele. Teine rippmenüü võimaldab juurdepääsu sündmustele

UserForm Aktiveeri sündmus

See sündmus käivitatakse vormi aktiveerimisel, tavaliselt siis, kui see kuvatakse. Seda sündmust saab kasutada vaikeväärtuste seadistamiseks, nt. ettevõtte nime tekstikasti ettevõtte vaikenimi

123	Privaatne alamkasutajaForm_Activate ()TextBox1.Text = "Minu ettevõtte nimi"End Sub

VBA programmeerimine | Koodigeneraator töötab teie jaoks!

Muuda sündmust

Enamikul vormi juhtelementidel on muutussündmus, kuid selles näites saab ettevõtte nime tekstikasti sündmuse abil piirata sisestatud ettevõtte nime pikkust.

123456

Privaatne alamtekstiBox1_Change ()Kui Len (TextBox1.Text)> 20 SiisMsgBox "Nimi on piiratud 20 tähemärgiga", vbCriticalTextBox1.Text = ""Lõpp KuiEnd Sub

Klõpsake nuppu Sündmus

Selle sündmuse abil saate toiminguid teha, kui kasutaja klõpsab vormi juhtelemente või isegi vormi ennast

Sellel vormil on nupp „OK” ja pärast ettevõtte nime kogumist soovime selle paigutada arvutustabeli lahtrisse edaspidiseks kasutamiseks

1234	Privaatne alamkäsk Button1_Click ()ActiveSheet.Range ("A1"). Väärtus = TextBox1. TekstMina. PeidaEnd Sub

See kood toimib siis, kui kasutaja klõpsab nuppu OK. See paneb ettevõtte nime sisendkasti väärtuse aktiivse lehe lahtrisse A1 ja seejärel peidab vormi nii, et kasutajajuhtimine tagastatakse töölehele.

Diagrammi sündmused

Diagrammi sündmused töötavad ainult diagrammidel, mis asuvad eraldi diagrammilehel, mitte aga diagrammil, mis on lisatud tavalisele töölehele

Diagrammi sündmused on mõnevõrra piiratud ja neid ei saa kasutada töölehel, kus teil võib olla mitu diagrammi. Samuti ei taha kasutajad ilmtingimata numbreid sisaldavalt töölehelt diagrammilehele üle minna - siin pole otsest visuaalset mõju

Kõige kasulikum sündmus oleks diagrammi komponendi väljaselgitamine, mille kasutaja on klõpsanud nt. sektsioon sektordiagrammis või tulpdiagrammi tulp, kuid see ei ole sündmus, mis on saadaval tavalises sündmustevahemikus.

Selle probleemi saab lahendada, kasutades klassimoodulit, et lisada sündmus „Hiir alla”, mis tagastab kasutaja klõpsatud diagrammikomponendi üksikasjad. Seda kasutatakse töölehe diagrammil.

See hõlmab väga keerulist kodeerimist, kuid tulemused on suurepärased. Saate luua puurvigu nt. kasutaja klõpsab sektordiagrammi segmendil ja koheselt on see diagramm peidetud ning selle asemele ilmub teine ​​diagramm, mis näitab algse segmendi üksikasjalikku diagrammi, või võite koostada tabeliandmed, mis toetavad seda diagrammi segmenti.

Rakendussündmused

Rakenduse objekti saate VBA -s kasutada koodi käivitamiseks vastavalt konkreetsele sündmusele

VBA programmeerimine | Koodigeneraator töötab teie jaoks!

Rakendus. OnTime

See võimaldab teil kooditüki korrapäraste ajavahemike järel välja lülitada seni, kuni töövihik on Excelisse laaditud. Võimalik, et soovite oma töövihiku automaatselt iga 10 minuti järel mõnda teise kausta salvestada või jätta töölehe üleöö töötama, et tuua sisse uusimad andmed välisest allikast.

Selles näites sisestatakse moodulisse alamrutiin. See kuvab sõnumikast iga 5 minuti järel, kuigi see võib kergesti olla teine ​​kodeeritud protseduur. Samal ajal lähtestab taimer praeguse aja pluss veel 5 minutit.

Iga kord, kui see töötab, lähtestab taimer sama alamrutiini veel 5 minuti pärast.

1234	Sub TestOnTime ()MsgBox "OnTime'i testimine"Application.OnTime (nüüd () + TimeValue ("00:05:00")), "TestOnTime"End Sub

Rakendus. OnKey

See funktsioon võimaldab teil kujundada oma kiirklahve. Saate panna mis tahes klahvikombinatsiooni helistama oma loomingu alamrutiiniks.

Selles näites suunatakse täht „a” ümber nii, et lahtrisse „a” paigutamise asemel kuvatakse sõnumikast. See kood tuleb sisestada sisestatud moodulisse.

123456

AlamkatseKlahvi ()Application.OnKey "a", "TestKeyPress"End SubAlamkatseKlahvi ()MsgBox "Vajutasite" a "End Sub

Esiteks käivitate alamrutiini „TestKeyPress”. Peate seda käivitama ainult üks kord. See ütleb Excelile, et iga kord, kui vajutatakse tähte „a”, nimetab see alamrutiini „TestKeyPress”. Alamrutiin „TestKeyPress” kuvab lihtsalt sõnumikast, mis ütleb teile, et vajutasite klahvi „a”. See võib muidugi vormi laadida või teha igasuguseid muid asju.

Saate kasutada mis tahes klahvikombinatsiooni, mida saate kasutada funktsiooniga „SendKeys”

Selle funktsionaalsuse tühistamiseks käivitate avalduse „OnKey” ilma parameetrita „Protseduur”.

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

Nüüd on kõik tagasi normaalseks.