Exceli VBA sündmused - lihtne (ja täielik) juhend

Kui loote või salvestate Excelis makro, peate koodi toimingute tegemiseks makro käivitama.

Mõned viisid makro käitamiseks hõlmavad makro dialoogiboksi kasutamist, makro määramist nupule, otsetee kasutamist jne.

Lisaks nendele kasutaja algatatud makro täitmistele saate makro käivitamiseks kasutada ka VBA sündmusi.

Exceli VBA sündmused - sissejuhatus

Lubage mul kõigepealt selgitada, mis on sündmus VBA -s.

Sündmus on toiming, mis võib käivitada määratud makro täitmise.

Näiteks kui avate uue töövihiku, on see sündmus. Kui sisestate uue töölehe, on see sündmus. Kui topeltklõpsate lahtril, on see sündmus.

VBA -s on palju selliseid sündmusi ja nende sündmuste jaoks saate luua koode. See tähendab, et niipea kui sündmus toimub ja kui olete sellele sündmusele koodi määranud, täidetakse see koheselt.

Excel teeb seda automaatselt kohe, kui märkab sündmuse toimumist. Nii et teil on vaja ainult kood kirjutada ja paigutada see õigesse sündmuste alamprogrammi (seda käsitletakse selles artiklis hiljem).

Näiteks kui sisestate uue töölehe ja soovite, et sellel oleks aasta eesliide, saate selle koodi kirjutada.

Nüüd, kui keegi lisab uue töölehe, täidetakse see kood automaatselt ja lisatakse töölehe nimele aasta eesliide.

Teine näide võib olla see, et soovite muuta lahtri värvi, kui keegi sellel topeltklõpsab. Selleks saate kasutada topeltklõpsuga sündmust.

Samamoodi saate paljude selliste sündmuste jaoks luua VBA -koode (nagu me näeme selles artiklis hiljem).

Allpool on lühike visuaal, mis näitab topeltklõpsu sündmust tegevuses. Niipea kui topeltklõpsan lahtril A1. Excel avab koheselt sõnumikasti, mis näitab lahtri aadressi.

Topeltklõps on sündmus ja sõnumikasti kuvamine on koodis määratud, kui topeltklõpsu sündmus aset leiab.

Kuigi ülaltoodud näide on kasutu sündmus, loodan, et see aitab teil mõista, mis sündmused tegelikult on.

Erinevad Exceli VBA sündmuste tüübid

Excelis on erinevaid objekte - näiteks Excel ise (millele me sageli viidame kui rakendusele), töövihikud, töölehed, diagrammid jne.

Igaüks neist objektidest võib olla seotud erinevate sündmustega. Näiteks:

• Kui loote uue töövihiku, on see rakendustaseme sündmus.
• Kui lisate uue töölehe, on see töövihiku taseme sündmus.
• Kui muudate lehe lahtri väärtust, on see töölehe taseme sündmus.

Allpool on Excelis eksisteerivad erinevat tüüpi sündmused.

1. Töölehe taseme sündmused: Need on sündmuste tüübid, mis käivitatakse töölehel tehtud toimingute põhjal. Nende sündmuste näideteks on töölehe lahtri muutmine, valiku muutmine, lahtril topeltklõps, lahtril paremklõps jne.
2. Töövihiku taseme sündmused: Need sündmused käivitatakse töövihiku tasemel toimingute põhjal. Nende sündmuste näited hõlmavad uue töölehe lisamist, töövihiku salvestamist, töövihiku avamist, osa või kogu töövihiku printimist jne.
3. Rakendustaseme sündmused: Need on sündmused, mis toimuvad Exceli rakenduses. Nende hulka kuuluvad näiteks avatud töövihikute sulgemine või uue töövihiku avamine.
4. UserFormi taseme sündmused: Need sündmused käivitatakse kasutajavormi toimingute põhjal. Nende hulka kuuluvad näiteks UserFormi initsialiseerimine või UserFormi nupul klõpsamine.
5. Diagrammi sündmused: Need on diagrammilehega seotud sündmused. Diagrammileht erineb töölehest (kus enamik meist on harjunud Excelis töötama). Diagrammilehtede eesmärk on diagrammi hoida. Selliste sündmuste näited hõlmavad diagrammi seeria muutmist või diagrammi suuruse muutmist.
6. OnTime ja OnKey sündmused: Need on kaks sündmust, mis ei sobi ühtegi ülaltoodud kategooriasse. Nii et ma loetlesin need eraldi. Sündmus „OnTime” võimaldab teil koodi käivitada kindlal ajal või pärast teatud aja möödumist. Sündmus „OnKey” võimaldab teil koodi käivitada, kui kasutatakse konkreetset klahvivajutust (või klahvivajutuste kombinatsiooni).

Kuhu sündmustega seotud kood panna?

Ülaltoodud osas käsitlesin erinevaid sündmusi.

Sündmuse tüübi põhjal peate koodi sisestama vastavasse objekti.

Näiteks kui see on töölehega seotud sündmus, peaks see minema töölehe objekti koodiaknasse. Kui see on seotud töövihikuga, peaks see minema töövihiku objekti koodi aknasse.

VBA -s on erinevatel objektidel - näiteks töölehed, töövihikud, diagrammilehed, kasutajavormid jne - oma koodiaknad. Peate sündmuse koodi sisestama vastava objekti koodi aknasse. Näiteks - kui see on töövihiku taseme sündmus, siis peab töövihiku koodi aknas olema sündmuse kood.

Järgmised jaotised hõlmavad kohti, kuhu saate sündmuse koodi sisestada:

Töölehe koodi aknas

Kui avate VB redaktori (kasutades kiirklahvi ALT + F11), märkate Project Exploreris töölehtede objekti. Töövihiku iga töölehe jaoks näete ühte objekti.

Kui topeltklõpsate töölehe objektil, kuhu soovite koodi paigutada, avaneb see selle töölehe koodiaken.

Kuigi saate koodi kirjutamist alustada nullist, on palju parem valida sündmus valikute loendist ja lasta VBA -l automaatselt sisestada valitud sündmuse jaoks vastav kood.

Selleks peate esmalt valima töölehe koodiakna vasakus ülanurgas olevast rippmenüüst.

Pärast rippmenüüst töölehe valimist näete kõigi töölehega seotud sündmuste loendit. Koodiakna paremas ülanurgas olevast rippmenüüst saate valida selle, mida soovite kasutada.

Niipea kui valite sündmuse, sisestab see automaatselt valitud sündmuse koodi esimese ja viimase rea. Nüüd saate oma koodi kahe rea vahele lisada.

Märkus. Niipea kui valite rippmenüüst Tööleht, märkate, et koodiaknas kuvatakse kaks koodirida. Kui olete valinud sündmuse, mille koodi soovite, saate vaikimisi ilmunud read kustutada.

Pange tähele, et igal töölehel on oma koodiaken. Kui sisestate lehe 1 koodi, töötab see ainult siis, kui sündmus toimub lehel 1.

Selle töövihiku koodi aknas

Nii nagu töölehed, kui teil on töövihiku taseme sündmuse kood, saate selle paigutada ThisWorkbooki koodi aknasse.

Kui topeltklõpsate sellel tööraamatul, avaneb selle jaoks koodiaken.

Koodiakna vasakus ülanurgas olevast rippmenüüst peate valima Töövihik.

Pärast rippmenüüst Töövihiku valimist näete kõigi töövihikuga seotud sündmuste loendit. Koodiakna paremas ülanurgas olevast rippmenüüst saate valida selle, mida soovite kasutada.

Niipea kui valite sündmuse, sisestab see automaatselt valitud sündmuse koodi esimese ja viimase rea. Nüüd saate oma koodi kahe rea vahele lisada.

Märkus. Niipea kui valite rippmenüüst Töövihik, märkate, et koodi aknasse ilmub kaks koodirida. Kui olete valinud sündmuse, mille koodi soovite, saate vaikimisi ilmunud read kustutada.

Kasutajavormi koodi aknas

Kui loote Excelis UserFormsi, saate UserFormi sündmusi kasutada ka konkreetsete toimingute põhjal koodide täitmiseks. Näiteks saate määrata koodi, mis käivitatakse nupule klõpsamisel.

Kuigi leheobjektid ja käesoleva töövihiku objektid on juba VB -redaktori avamisel saadaval, on UserForm midagi, mida peate esmalt looma.

UserFormi loomiseks paremklõpsake ükskõik millisel objektil, minge Insert ja klõpsake UserForm.

See sisestaks töövihikusse objekti UserForm.

Kui topeltklõpsate UserFormil (või mõnel objektil, mille lisate UserFormile), avaneb see UserFormi koodi aken.

Nüüd, nagu töölehed või ThisWorkbook, saate sündmuse valida ja see lisab selle sündmuse esimese ja viimase rea. Ja siis saate selle keskele koodi lisada.

Diagrammi koodi aknas

Excelis saate sisestada ka diagrammilehti (mis erinevad töölehtedest). Diagrammileht peab sisaldama ainult diagramme.

Kui olete diagrammilehe sisestanud, näete diagrammilehe objekti VB redaktoris.

Sündmuse koodi saate diagrammilehe koodi aknasse lisada täpselt nii, nagu tegime töölehel.

Topeltklõpsake diagrammilehe objekti Project Exploreris. See avab diagrammilehe koodiakna.

Nüüd peate koodiakna vasakus ülanurgas olevast rippmenüüst valima Diagramm.

Pärast rippmenüüst Diagrammi valimist näete kõigi diagrammilehega seotud sündmuste loendit. Koodiakna paremas ülanurgas olevast rippmenüüst saate valida selle, mida soovite kasutada.

Märkus. Niipea kui valite rippmenüüst Diagramm, märkate, et koodiaknas kuvatakse kaks koodirida. Kui olete valinud sündmuse, mille koodi soovite, saate vaikimisi ilmunud read kustutada.

Klassi moodul

Klassimoodulid tuleb sisestada täpselt nagu UserForms.

Klassimoodul võib hoida rakendusega seotud koodi - näiteks Excel ise ja manustatud diagramme.

Klassimooduli katan lähinädalatel eraldi õpetusena.

Pange tähele, et peale OnTime'i ja OnKey sündmuste ei saa ühtegi ülaltoodud sündmust tavalisse VBA moodulisse salvestada.

Sündmuste jada mõistmine

Kui käivitate sündmuse, ei toimu see eraldi. See võib põhjustada ka mitme päästiku jada.

Näiteks uue töölehe sisestamisel juhtub järgmist.

1. Lisatakse uus tööleht
2. Eelmine tööleht deaktiveeritakse
3. Uus tööleht aktiveeritakse

Ehkki enamikul juhtudel ei pea te järjestuse pärast muretsema, kui loote sündmustele tuginevaid keerulisi koode, on ootamatute tulemuste vältimiseks parem järjestust teada.

Argumentide rolli mõistmine VBA sündmustel

Enne kui näeme sündmuste näiteid ja suurepäraseid asju, mida saate sellega teha, on mul vaja käsitleda ühte olulist kontseptsiooni.

VBA sündmustel on kahte tüüpi koode:

• Ilma igasuguste argumentideta
• Argumentidega

Ja selles osas tahan kiiresti kajastada argumentide rolli.

Allpool on kood, millel pole argumente (sulud on tühjad):

Privaatne alamtöövihik_Open () MsgBox "Ärge unustage tööaega täita" Lõpu alam

Ülaltoodud koodiga kuvatakse töövihiku avamisel lihtsalt sõnumikast sõnumiga - „Ärge unustage tööajatabelit täita”.

Nüüd vaatame koodi, millel on argument.

Private Sub Workbook_NewSheet (ByVal Sh as Object) Sh.Range ("A1") = Sh.Name End Sub

Ülaltoodud kood kasutab argumenti Sh, mis on määratletud objektitüübina. Argument Sh võib olla tööleht või diagrammileht, kuna ülaltoodud sündmus käivitatakse uue lehe lisamisel.

Määrates töövihikusse lisatud uue lehe objekti muutujale Sh, on VBA võimaldanud meil seda koodis kasutada. Nii et uue lehe nimele viitamiseks võin kasutada Sh.Name.

Argumentide kontseptsioon on kasulik, kui vaatate järgmistes jaotistes VBA sündmuste näiteid.

Töövihiku taseme sündmused (näidetega selgitatud)

Järgnevalt on töövihikus kõige sagedamini kasutatavad sündmused.

SÜNDMUSE NIMI	MIS SÜNDMUSE VALDAB
Aktiveeri	Kui töövihik on aktiveeritud
AfterSave	Kui töövihik on lisandmoodulina installitud
BeforeSave	Kui töövihik on salvestatud
EnneSulge	Kui töövihik on suletud
BeforePrint	Kui töövihik on trükitud
Lülita välja	Kui töövihik on deaktiveeritud
NewSheet	Kui lisatakse uus leht
Avatud	Kui töövihik avatakse
SheetActivate	Kui mõni töövihiku leht on aktiveeritud
SheetBeforeDelete	Kui mõni leht kustutatakse
SheetBeforeDoubleClick	Kui ükskõik millisel lehel tehakse topeltklõps
SheetBeforeRightClick	Kui mõni leht on paremklõpsatud
SheetCalculate	Kui mõni leht arvutatakse või arvutatakse uuesti
SheetDeactivate	Kui töövihik on deaktiveeritud
SheetPivotTableUpdate	Kui töövihikut värskendatakse
SheetSelectionChange	Kui töövihikut muudetakse
AkenAktiveeri	Kui töövihik on aktiveeritud
WindowDeactivate	Kui töövihik on deaktiveeritud

Pange tähele, et see pole täielik nimekiri. Täieliku nimekirja leiate siit.

Pidage meeles, et töövihiku sündmuse kood salvestatakse ThisWorkbooki objektide koodi aknasse.

Nüüd vaatame mõningaid kasulikke töövihikuüritusi ja vaatame, kuidas neid oma igapäevatöös kasutada.

Töövihiku avatud sündmus

Oletame, et soovite näidata kasutajale sõbralikku meeldetuletust ajagraafikute täitmiseks, kui nad konkreetse töövihiku avavad.

Selleks saate kasutada järgmist koodi:

Privaatne alamtöövihik_Open () MsgBox "Ärge unustage tööajalehte täita" Lõpu alam

Niipea kui avate selle koodiga töövihiku, kuvatakse teile sõnumikast koos selle sõnumiga.

Selle koodiga (või töövihiku sündmuste koodidega üldiselt) töötamisel peate teadma mõnda asja.

• Kui töövihikul on makro ja soovite selle salvestada, peate selle salvestama .XLSM -vormingus. Muidu oleks makrokood kadunud.
• Ülaltoodud näites käivitatakse sündmuse kood ainult siis, kui makrod on lubatud. Võite näha kollast riba, mis küsib luba makrosid lubada. Kuni see pole lubatud, sündmuse koodi ei täideta.
• Töövihiku sündmuse kood paigutatakse käesoleva töövihiku objekti koodi aknasse.

Saate seda koodi veelgi täpsustada ja näidata sõnumit ainult reedel.

Allolev kood teeks seda:

Privaatne alamtöövihik_Open () wkday = nädalapäev (kuupäev) Kui wkday = 6 siis MsgBox "Ärge unustage tööaega täita" Lõpu alam

Pange tähele, et nädalapäeva funktsioonis määratakse pühapäevale väärtus 1, esmaspäev on 2 jne.

Seega olen reedeks kasutanud 6.

Töövihiku avamise sündmus võib olla kasulik paljudes olukordades, näiteks:

• Kui soovite töövihiku avamisel inimesele näidata tervitussõnumit.
• Kui soovite töövihiku avamisel meeldetuletust kuvada.
• Kui soovite töövihikus selle avamisel alati aktiveerida ühe kindla töölehe.
• Kui soovite avada seotud failid koos töövihikuga.
• Kui soovite jäädvustada kuupäeva ja kellaaja templi iga kord, kui töövihik avatakse.

Töövihiku uue lehe sündmus

NewSheet sündmus käivitatakse, kui sisestate töövihikusse uue lehe.

Oletame, et soovite sisestada kuupäeva ja kellaaja väärtuse äsja sisestatud lehe lahtrisse A1. Selleks saate kasutada järgmist koodi:

Private Sub Workbook_NewSheet (ByVal Sh as Object) On Error Resume Next Sh.Range ("A1") = Format (Now, "dd-mmm-yyyy hh: mm: ss") End Sub

Ülaltoodud kood kasutab käsku „On Error Resume Next”, kui keegi lisab diagrammilehe, mitte töölehe. Kuna diagrammilehel pole lahtrit A1, näitaks see tõrget, kui suvandit „On Error Resume Next” ei kasutata.

Teine näide võib olla see, kui soovite uuele lehele kohe pärast selle lisamist rakendada mõnda põhiseadet või vormingut. Näiteks kui soovite lisada uue lehe ja soovite, et see saaks automaatselt seerianumbri (kuni 100), saate kasutada allolevat koodi.

Privaatne alamtöövihiku_NewSheet (ByVal Sh kui objekt) Tõrke korral jätkake järgmiselt Sh.Range'iga ("A1") .Väärtus = "S. Nr." .Interior.Color = vbBlue .Font.Color = vbWhite End With For i = 1 to 100 Sh.Range ("A1"). Offset (i, 0) .Väärtus = i Järgmine i Sh.Range ("A1", vahemik ("A1"). Lõpp (xlDown)). Borders.LineStyle = xlPidev lõppalam

Ülaltoodud kood teeb ka natuke vormindamist. See annab päise lahtrile sinise värvi ja muudab fondi valgeks. Samuti rakendab see äärist kõigile täidetud lahtritele.

Ülaltoodud kood on näide sellest, kuidas lühike VBA -kood aitab teil iga kord uue töölehe sisestamisel mõni sekund varastada (juhuks, kui peate seda iga kord tegema).

Töövihik BeforeSave Event

Enne töövihiku salvestamisel käivitub sündmus Salvesta. Pange tähele, et esmalt käivitatakse sündmus ja seejärel töövihik salvestatakse.

Exceli töövihiku salvestamisel võib olla kaks võimalikku stsenaariumi.

1. Salvestate selle esmakordselt ja kuvatakse dialoogiboks Salvesta nimega.
2. Olete selle juba varem salvestanud ja see lihtsalt salvestab ja kirjutab juba salvestatud versiooni muudatused üle.

Vaatame nüüd mõnda näidet, kus saate sündmust BeforeSave kasutada.

Oletame, et teil on uus töövihik, mille salvestate esimest korda, ja soovite kasutajale meelde tuletada, et see salvestatakse K -draivi, siis võite kasutada järgmist koodi:

Privaatne alamtöövihik_BeforeSave (ByVal SaveAsUI kui Boolean, Cancel as Boolean) Kui SaveAsUI Siis MsgBox "Salvesta see fail K -draivi" End Sub

Kui ülaltoodud koodis pole faili kunagi salvestatud, on SaveAsUI tõene ja avab dialoogiboksi Salvesta nimega. Ülaltoodud kood kuvab sõnumi enne dialoogiboksi Salvesta nime ilmumist.

Teine näide võib olla kuupäeva ja kellaaja värskendamine, kui fail on teatud lahtrisse salvestatud.

Allolev kood sisestaks kuupäeva ja kellaaja templi lehe 1 lahtrisse A1 iga kord, kui fail salvestatakse.

Privaatne alamtöövihik_BeforeSave (ByVal SaveAsUI kui Boolean, Cancel as Boolean) Töölehed ("Sheet1"). Vahemik ("A1") = Formaat (nüüd, "pp-kk-aaaa hh: mm: ss") Alamosa

Pange tähele, et see kood käivitatakse kohe, kui kasutaja töövihiku salvestab. Kui töövihikut salvestatakse esimest korda, kuvatakse sellel dialoogiboks Salvesta nimega. Kuid kood on juba täidetud selleks ajaks, kui näete dialoogiboksi Salvesta nimega. Kui otsustate töövihiku tühistada ja mitte salvestada, sisestatakse kuupäev ja kellaaeg lahtrisse.

Töövihik enne sündmuse sulgemist

Sündmus Enne sulgemist toimub vahetult enne töövihiku sulgemist.

Allolev kood kaitseb kõiki töölehti enne töövihiku sulgemist.

Private Sub Workbook_BeforeClose (Cancel as Boolean) Dim sh kui tööleht Iga sh selles töövihikus. Töölehed sh. Kaitsta järgmise sh lõpu alam

Pidage meeles, et sündmuse kood käivitatakse kohe pärast töövihiku sulgemist.

Üks oluline asi selle sündmuse kohta on see, et see ei hooli sellest, kas töövihik on tegelikult suletud või mitte.

Kui töövihikut pole salvestatud ja teile kuvatakse viip, milles küsitakse, kas töövihik salvestada või mitte, ja klõpsate nuppu Tühista, ei salvesta see teie töövihikut.Sündmuse kood oleks selleks ajaks aga juba täidetud.

Töövihik enne printimise sündmust

Kui annate printimiskäsu (või käsu Prindi eelvaade), käivitatakse sündmus Enne printimist.

Allolev kood arvutaks enne töölehe printimist kõik töölehed uuesti.

Privaatne alamtöövihik_BeforePrint (tühistatakse tõeväärtusena) iga töölehe ws jaoks ws. Arvutage järgmised ws End Sub

Kui kasutaja töövihikut trükib, käivitatakse sündmus, olenemata sellest, kas ta prindib kogu töövihiku või ainult selle osa.

Teine näide allpool on kood, mis lisab töövihiku printimisel jalusele kuupäeva ja kellaaja.

Private Sub Workbook_BeforePrint (Cancel as Boolean) Dim ws töölehena iga töö jaoks käesolevas töövihikus. Töölehed ws.PageSetup.LeftFooter = "Printed On-" & Format (Now, "dd-mmm-yyyy hh: mm") Järgmine ws End Sub

Töölehe taseme sündmused (näidetega selgitatud)

Töölehe sündmused toimuvad töölehel käivitavate tegurite alusel.

Järgnevalt on töölehel kõige sagedamini kasutatavad sündmused.

Sündmuse nimi	Mis sündmuse käivitab
Aktiveeri	Kui tööleht on aktiveeritud
Enne Kustuta	Enne töölehe kustutamist
BeforeDoubleClick	Enne töölehe topeltklõpsamist
BeforeRightClick	Enne töölehe paremklõpsamist
Arvutama	Enne töölehe arvutamist või ümberarvutamist
Muuda	Kui töölehe lahtreid muudetakse
Lülita välja	Kui tööleht on deaktiveeritud
PivotTableUpdate	Kui töölehe liigendtabelit värskendatakse
ValikMuuda	Kui töölehe valikut muudetakse

Pange tähele, et see pole täielik nimekiri. Täieliku nimekirja leiate siit.

Pidage meeles, et töölehe sündmuse kood salvestatakse töölehe objekti koodi aknasse (sellesse, milles soovite sündmuse käivitada). Töövihikus võib olla mitu töölehte ja teie kood käivitatakse alles siis, kui sündmus toimub töölehel, kuhu see paigutatakse.

Nüüd vaatame mõningaid kasulikke töölehe sündmusi ja vaatame, kuidas neid saab oma igapäevatöös kasutada.

Tööleht Aktiveeri sündmus

See sündmus käivitatakse töölehe aktiveerimisel.

Allolev kood eemaldab lehe kaitse kohe pärast selle aktiveerimist.

Privaatne alamleht_Activate () ActiveSheet. Lõpeta alamkaitse tühistamine

Selle sündmuse abil saate veenduda, et töölehe aktiveerimisel valitakse kohe konkreetne lahter või lahtrivahemik (või nimega vahemik). Allolev kood valiks lahtri D1 kohe, kui aktiveerite lehe.

Privaatne alamleht_Activate () ActiveSheet.Range ("D1"). Valige Lõpeta alam

Töölehe muutmise sündmus

Muudatussündmus käivitatakse alati, kui teete töölehel muudatusi.

Noh… mitte alati.

Sündmust käivitavad mõned muudatused ja mõned mitte. Siin on nimekiri mõningatest muudatustest, mis sündmust ei käivita:

• Kui muudate lahtri vormingut (fondi suurus, värv, äär jne).
• Lahtrite ühendamisel. See on üllatav, sest mõnikord eemaldab lahtrite ühendamine ka sisu kõigist lahtritest, välja arvatud vasakpoolne ülaosa.
• Lahtrikommentaari lisamisel, kustutamisel või muutmisel.
• Lahtrite vahemiku sortimisel.
• Kui kasutate eesmärgiotsingut.

Järgmised muudatused käivitaksid sündmuse (kuigi arvate, et see ei tohiks seda teha):

• Vormindamise kopeerimine ja kleepimine käivitaks sündmuse.
• Vorminduse kustutamine käivitaks sündmuse.
• Õigekirjakontrolli käivitamine käivitaks sündmuse.

Allpool on kood, mis näitaks sõnumikast muudetud lahtri aadressiga.

Privaatne alamlehe_muutmine (ByVal -sihtmärk vahemikuna) MsgBox "Sa just muutsid" ja sihtmärk. Aadress Lõpu alam

Kuigi see on kasutu makro, näitab see teile, kuidas kasutada argumenti Sihtmärk, et teada saada, milliseid lahtreid on muudetud.

Vaatame nüüd paari kasulikumat näidet.

Oletame, et teil on lahtrite vahemik (oletame, et A1: D10) ja soovite kuvada viiba ning küsida kasutajalt, kas ta tõesti soovis selles vahemikus lahtrit muuta või mitte, saate kasutada allolevat koodi.

See kuvab kahe nupuga - Jah ja Ei.

Privaatne alamlehe muutmine (vahemik ByVal Target) Kui sihtmärk.Rida <= 10 Ja sihtmärk.veerg <= 4 Siis Ans = MsgBox ("Teete muutusi lahtrites A1: D10. Kas olete kindel, et soovite seda?", vbYesNo) End if If Ans = vbNo then Application.EnableEvents = False Application. Undo Application.EnableEvents = True End If End Sub

Ülaltoodud koodis kontrollime, kas sihtmärgi lahter asub esimeses 4 veerus ja esimeses 10 reas. Kui see nii on, kuvatakse sõnumikast. Samuti, kui kasutaja valis sõnumiboksis Ei, tühistatakse muudatus (käsk Application.Undo).

Pange tähele, et olen kasutanud Application.EnableEvents = Vale enne Application.Undo rida. Ja siis pöörasin selle tagasi, kasutades rakendust Application.EnableEvent = True järgmisel real.

Seda on vaja, kuna tagasivõtmise korral käivitab see ka muutussündmuse. Kui ma ei määra EnableEvent väärtuseks False, käivitab see jätkuvalt muutussündmuse.

Samuti saate nimega vahemiku muudatusi muudatussündmuse abil jälgida. Näiteks kui teil on nimega vahemik nimega „DataRange” ja soovite kuvada viiba juhuks, kui kasutaja teeb selles nimevahemikus muudatusi, võite kasutada järgmist koodi:

Privaatne alamlehe_muutmine (ByVal sihtmärk vahemikuna) Dim DRange kui vahemik Määra DRange = Vahemik ("DataRange") Kui mitte ristuda (sihtmärk, DRange) pole midagi, siis MsgBox "Tegite just andmevahemiku muutmise" End If End Sub

Ülaltoodud kood kontrollib, kas lahtris/vahemikus, kus olete muudatused teinud, on andmevahemikule ühiseid lahtreid. Kui see nii on, kuvatakse sõnumikast.

Töövihiku valiku muutmise sündmus

Valiku muutmise sündmus käivitatakse alati, kui töölehel on tehtud valiku muutmine.

Allpool olev kood arvutaks lehe uuesti kohe, kui muudate valikut.

Privaatse alamlehe_lehe muutmine (ByVal Target as Range) rakendus. Arvutage lõpp -alam

Selle sündmuse teine ​​näide on see, kui soovite esile tõsta valitud lahtri aktiivse rea ja veeru.

Midagi, nagu allpool näidatud:

Seda saab teha järgmise koodiga:

Privaatne alamtöölehe_valiku muutmine (ByVal Target As Range) Lahtrid.Interior.ColorIndex = xlNone ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Lõpp End Sub

Kood eemaldab kõigepealt taustavärvi kõigist lahtritest ja rakendab seejärel koodis mainitud aktiivsele reale ja veerule.

Ja see on selle koodi probleem. Et see eemaldab kõikidest rakkudest värvi.

Kui soovite esile tõsta aktiivset rida/veergu, hoides teiste lahtrite värvi puutumatuna, kasutage selles õpetuses näidatud tehnikat.

Töövihiku DoubleClicki sündmus

See on üks minu lemmik töölehe sündmusi ja näete palju õpetusi, kus olen seda kasutanud (näiteks see või see).

See sündmus käivitatakse lahtri topeltklõpsamisel.

Las ma näitan teile, kui fantastiline see on.

Alloleva koodiga saate topeltklõpsata lahtril ja see rakendab taustavärvi, muudab fondi värvi ja muudab lahtris oleva teksti paksuks;

Privaatne alamtööleht_BeforeDoubleClick (ByVal Target as Range, Cancel as Boolean) Cancel = True with Target .Interior.Color = vbBlue .Font.Color = vbWhite .Font.Bold = True End With End Sub

See võib olla kasulik, kui vaatate lahtrite loendit ja soovite esile tuua mõned valitud. Viimase sammu kordamiseks võite kasutada klahvi F4, kuid see saab rakendada ainult ühte tüüpi vormingut. Selle topeltklõpsuga sündmuse korral saate kõik kolm rakendada vaid topeltklõpsuga.

Pange tähele, et ülaltoodud koodis olen muutnud väärtuse Cancel = True.

Seda tehakse nii, et topeltklõpsu vaiketoiming on keelatud - see tähendab redigeerimisrežiimi sisenemist. Kui tühistate = Tõsi, ei vii Excel lahtrisse topeltklõpsamisel redigeerimisrežiimi.

Siin on veel üks näide.

Kui teil on Excelis ülesannete loend, saate topeltklõpsuga sündmuse abil läbikriipsutusvormingu rakendada, et märkida ülesanne lõpetatuks.

Midagi, nagu allpool näidatud:

Siin on kood, mis seda teeb:

Privaatne alamtööleht_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

Pange tähele, et selles koodis olen lülitussündmusena teinud topeltklõpsu. Kui topeltklõpsate lahtril, kontrollib see, kas läbikriipsutusvorming on juba rakendatud. Kui seda on tehtud, eemaldab topeltklõps läbikriipsutusvormingu ja kui seda pole tehtud, rakendatakse läbikriipsutusvormingut.

Exceli VBA OnTime'i sündmus

Sündmused, mida oleme selles artiklis seni näinud, olid seotud ühe Exceli objektiga, olgu see siis töövihik, tööleht, diagrammileht või UserForms jne.

Sündmus OnTime erineb teistest sündmustest, kuna seda saab salvestada tavalisse VBA moodulisse (teised aga paigutati objektide, nagu ThisWorkbook või töölehed või UserForms, koodiaknasse).

Tavalises VBA moodulis kasutatakse seda rakendusobjekti meetodina.

Seda peetakse sündmuseks seetõttu, et selle saab käivitada teie määratud aja alusel. Näiteks kui tahan, et leht arvutataks ümber iga 5 minuti järel, võin selle jaoks kasutada sündmust OnTime.

Või kui soovin näidata sõnumit/meeldetuletust kindlal kellaajal, võin kasutada sündmust OnTime.

Allpool on kood, mis näitab iga päev kell 14.00 sõnumit.

Sub MessageTime () Application.OnTime TimeValue ("14:00:00"), "ShowMessage" End Sub Show ShowMessage () MsgBox "It's Lunch Time" End Sub

Pidage meeles, et peate selle koodi sisestama tavalisse VBA moodulisse,

Samuti, kuigi sündmus OnTime käivitatakse määratud ajal, peate makro igal ajal käsitsi käivitama. Kui olete makro käivitanud, ootab see kella 14.00 ja helistab seejärel makrole „ShowMessage”.

Makro ShowMessage kuvaks siis sõnumi.

Sündmus OnTime sisaldab nelja argumenti.

Application.OnTime (Varaseim aeg, Menetlus, LatestTime, Ajakava)

• Varaseim aeg: Aeg, millal soovite protseduuri käivitada.
• Protseduur: Protseduuri nimi, mida tuleks käivitada.
• LatestTime (valikuline): Kui mõni teine ​​kood töötab ja teie määratud koodi ei saa määratud ajal käivitada, saate määrata LatestTime, mida see peaks ootama. Näiteks võib see olla EarliestTime + 45 (see tähendab, et ootab 45 sekundit, kuni teine ​​protseduur lõpule jõuab). Kui isegi 45 sekundi pärast ei saa protseduuri käivitada, loobutakse sellest. Kui te seda ei määra, ootab Excel koodi käivitamiseni ja käivitab selle.
• Ajakava (valikuline): Kui see on seatud väärtusele Tõene, ajastatakse see uus ajaprotseduur. Kui vale, tühistab see eelnevalt määratud protseduuri. Vaikimisi on see tõene.

Ülaltoodud näites kasutasime ainult kahte esimest argumenti.

Vaatame teist näidet.

Allolev kood värskendaks töölehte iga 5 minuti järel.

Dim NextRefresh as Date Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Arvuta NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () Viga Jätka järgmisel Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Ülaltoodud kood värskendaks töölehte iga 5 minuti järel.

See kasutab praeguse aja määramiseks funktsiooni Now ja lisab seejärel praegusele ajale 5 minutit.

Sündmuse OnTime käitamist jätkatakse seni, kuni te selle peatate. Kui sulgete töövihiku ja Exceli rakendus töötab endiselt (teised töövihikud on avatud), avaneb töövihik, milles on OnTime'i sündmus, käimas.

Seda saab paremini lahendada, peatades OnTime'i sündmuse.

Ülaltoodud koodis on mul StopRefresh kood, kuid peate selle OnTime sündmuse peatamiseks täitma. Saate seda teha käsitsi, määrata selle nupule ja teha seda, vajutades nuppu või helistades sellele töövihiku sulgemise sündmuselt.

Privaatne alamtöövihik_BeforeClose (Tühista kui Boolean) Helista StopRefresh End Sub

Ülaltoodud sündmuse kood „BeforeClose“ läheb ThisWorkbooki koodi aknasse.

Exceli VBA OnKey sündmus

Kui töötate Exceliga, jälgib see pidevalt teie kasutatavaid klahvivajutusi. See võimaldab meil sündmuse käivitajana kasutada klahvivajutusi.

OnKey sündmusega saate määrata klahvivajutuse (või klahvivajutuste kombinatsiooni) ja koodi, mis tuleks selle klahvivajutuse kasutamisel täita. Kui vajutate neid klahvivajutusi, käivitab see selle koodi.

Nagu OnTime'i sündmus, peab teil olema võimalus OnKey sündmus tühistada. Samuti, kui määrate OnKey sündmuse konkreetseks klahvivajutuseks, muutub see kättesaadavaks kõikides avatud töövihikutes.

Enne kui näitan teile OnKey sündmuse kasutamise näidet, lubage mul kõigepealt jagada võtmekoode, mis on teile VBA -s saadaval.

VÕTI	KOOD
Backspace	{BACKSPACE} või {BS}
Paus	{BREAK}
Caps Lock	{CAPSLOCK}
Kustuta	{DELETE} või {DEL}
Allanool	{DOWN}
Lõpp	{LÕPP}
Sisenema	~
Sisestage (nuuriklaviatuuril)	{SISENEMA}
Põgeneda	{ESCAPE} või {ESC}
Kodu	{KODU}
Ins	{INSERT}
Vasak nool	{LEFT}
Numeratsioonilukk	{NUMERATSIOONILUKK}
PageDown	{PGDN}
PageUp	{PGUP}
RightNool	{RIGHT}
Kerimislukk	{SCROLLOCK}
Tab	{TAB}
Ülesnool	{ÜLES}
F1 kuni F15	{F1} kuni {F15}

Kui peate kasutama mis tahes onkey -sündmust, peate kasutama selle koodi.

Ülaltoodud tabelis on üksikute klahvivajutuste koodid.

Neid saab kombineerida ka järgmiste koodidega:

• Vahetus: + (Plussmärk)
• Kontroll: ^ (Caret)
• Alt: % (Protsent)

Näiteks Alt F4 jaoks peate kasutama koodi: "%{F4}” - kus % tähistab klahvi ALT ja {F4} klahvi F4.

Vaatame nüüd näidet (pidage meeles, et OnKey sündmuste kood on paigutatud tavalisse VBA moodulisse).

Kui vajutate klahvi PageUp või PageDown, hüppab see 29 rida aktiivse lahtri kohale/alla (vähemalt seda teeb see minu sülearvutis).

Kui soovite, et see liiguks korraga ainult 5 rida, võite kasutada järgmist koodi:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" Alam alamlehekülgUpMod () Viga Tõrge Jätka järgmisel ActiveCell.Offset (-5, 0). Aktiveeri lõpp Alam alamlehtDownMod () On Error Jätka järgmisel ActiveCell. Offset (5, 0). Aktiveeri End Sub

Kui käivitate koodi esimese osa, käivitab see OnKey sündmused. Kui see on täidetud, kasutaks PageUp ja PageDown klahvi kasutamine kursorit ainult 5 rida korraga.

Pange tähele, et oleme kasutanud valikut „On Error Resume Next”, et veenduda vigade eiramises. Need vead võivad ilmneda, kui vajutate klahvi PageUp isegi siis, kui olete töölehe ülaosas. Kuna enam ridu hüpata pole, näitaks kood viga. Kuid kuna oleme kasutanud nuppu „Vea jätkamine järgmisena”, ignoreeritakse seda.

Nende OnKey sündmuste kättesaadavuse tagamiseks peate käivitama koodi esimese osa. Kui soovite, et see oleks saadaval kohe pärast töövihiku avamist, saate selle paigutada ThisWorkbooki koodi aknasse.

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

Allolev kood tagastab võtmed nende tavapärase funktsionaalsuse.

Alam Cancel_PageUpDownKeysMod () Application.OnKey "{PgUp}" Application.OnKey "{PgDn}" Lõpu alam

Kui te ei määra OnKey meetodis teist argumenti, tagastab see klahvivajutuse tavapärase funktsionaalsuse.

Kui soovite tühistada klahvivajutuse funktsionaalsuse ja Excel ei tee selle klahvivajutuse kasutamisel midagi, peate teise argumendina kasutama tühja stringi.

Allolevas koodis ei teeks Excel midagi, kui kasutame klahve PageUp või PageDown.

Alam Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" Lõpu alam

Sündmuste keelamine VBA -s

Mõnikord peate koodi nõuetekohaseks toimimiseks sündmused keelama.

Oletame näiteks, et mul on vahemik (A1: D10) ja ma tahan näidata sõnumit alati, kui selles vahemikus lahtrit muudetakse. Seega näitan sõnumikasti ja küsin kasutajalt, kas ta on kindel, et soovib muudatust teha. Kui vastus on jaatav, siis muudatus tehakse ja kui vastus on ei, tühistab VBA selle.

Võite kasutada järgmist koodi:

Privaatne alamlehe muutmine (vahemik ByVal Target) Kui sihtmärk.Rida <= 10 Ja sihtmärk.veerg <= 4 Siis Ans = MsgBox ("Teete muutusi lahtrites A1: D10. Kas olete kindel, et soovite seda?", vbJahEi) Lõpeta, kui Ans = vbEi, siis rakendus. Võta tagasi, kui lõpeb alam

Selle koodi probleem on see, et kui kasutaja valib sõnumiboksis Ei, toiming pööratakse ümber (nagu ma olen rakendust Application.Undo kasutanud).

Kui tagasivõtmine toimub ja väärtus muudetakse tagasi algsele, käivitatakse uuesti VBA muutmise sündmus ja kasutajale kuvatakse uuesti sama sõnumikast.

See tähendab, et saate jätkata sõnumikastil EI klõpsamist ja see kuvatakse jätkuvalt. See juhtub sel juhul, kui olete sel juhul lõpmatusse silmusesse kinni jäänud.

Selliste juhtumite vältimiseks peate sündmused keelama, et muutussündmus (või mõni muu sündmus) ei käivituks.

Järgmine kood toimiks sel juhul hästi:

Privaatne alamlehe muutmine (vahemik ByVal Target) Kui sihtmärk.Rida <= 10 Ja sihtmärk.veerg <= 4 Siis Ans = MsgBox ("Teete muutusi lahtrites A1: D10. Kas olete kindel, et soovite seda?", vbYesNo) End if If Ans = vbNo then Application.EnableEvents = False Application. Undo Application.EnableEvents = True End If End Sub

Ülaltoodud koodis, otse rakenduse kohal. Võta tagasi, oleme kasutanud - Application.EnableEvents = False.

Kui lubate väärtuse EnableEvents väärtuseks Väär, ei käivitata ühtegi sündmust (praeguses või avatud töövihikus).

Kui oleme tagasivõtmise lõpule viinud, saame EnableEvents atribuudi tagasi tõeseks muuta.

Pidage meeles, et sündmuste keelamine mõjutab kõiki töövihikuid, mis on praegu avatud (või avatud, kui EnableEvents on seatud väärtusele Väär). Näiteks kui avate uue töövihiku koodi osana, siis töövihiku avamise sündmus ei tööta.

Sündmuste mõju Tühista virn

Esiteks ütlen teile, mis on Undo Stack.

Kui töötate Excelis, jälgib see teie toiminguid. Kui teete vea, saate alati eelmisele sammule naasmiseks kasutada klahve Control + Z (st praeguse toimingu tagasivõtmist).

Kui vajutate kaks korda klahvikombinatsiooni Control + Z, viib see kaks sammu tagasi. Need teie tehtud toimingud salvestatakse korstna tagasivõtmise osana.

Iga sündmus, mis muudab töölehte, hävitab selle tagasivõtmise virna.See tähendab, et kui olen enne sündmuse käivitamist teinud 5 asja, ei saa ma kasutada eelmiste sammude juurde naasmiseks klahve Control + Z. Sündmuse käivitamine on selle virna minu jaoks hävitanud.

Allolevas koodis kasutan VBA -d ajatempli sisestamiseks lahtrisse A1, kui töölehel on muudatusi.

Privaatne alamlehe_muutmine (ByVal-sihtmärk vahemikuna) Application.EnableEvents = vale vahemik ("A1"). Väärtus = vorming (nüüd, "pp-kk-aaaa hh: mm: ss") Application.EnableEvents = Tõeline lõpp

Kuna teen töölehel muudatusi, hävitab see tagasivõtmise virna.

Samuti pidage meeles, et see ei piirdu ainult sündmustega.

Kui teil on kood, mis on salvestatud tavalisse VBA -moodulisse ja teete töölehel muudatusi, hävitab see ka Exceli tagasivõtmise virna.

Näiteks sisestage allolev kood lahtrisse A1 lihtsalt tekst „Tere“, kuid isegi selle käivitamine hävitaks tagasivõtmise virna.

Alamtüüp Tere () vahemik ("A1"). Väärtus = "Tere" lõpp -alam

Samuti võivad teile meeldida järgmised Exceli VBA õpetused:

• Lahtrite ja vahemikega töötamine Excel VBA -s.
• Töölehtedega töötamine Excel VBA -s.
• Töövihikutega töötamine Excel VBA -s.
• Exceli VBA silmused - ülim juhend.
• Kasutades IF Siis muud avaldust Excel VBA -s.
• Järgmise silmuse jaoks Excelis.
• Kasutaja määratud funktsioonide loomine Excel VBA-s.
• Lisandmoodulite loomine ja kasutamine Excelis.
• Makrode loomine ja taaskasutamine isikliku makro töövihikusse salvestamisega.