Dogodki Excel VBA - enostaven (in popoln) vodnik

Ko ustvarite ali posnamete makro v Excelu, ga morate zagnati, da izvedete korake v kodi.

Nekaj ​​načinov izvajanja makra vključuje uporabo pogovornega okna makra, dodelitev makra gumbu, uporabo bližnjice itd.

Poleg izvajanja makrov, ki jih sprožijo uporabniki, lahko za zagon makra uporabite tudi dogodke VBA.

Dogodki Excel VBA - Uvod

Naj najprej razložim, kaj je dogodek v VBA.

Dogodek je dejanje, ki lahko sproži izvedbo podanega makra.

Ko na primer odprete nov delovni zvezek, je to dogodek. Ko vstavite nov delovni list, je to dogodek. Ko dvakrat kliknete celico, je to dogodek.

V VBA je veliko takih dogodkov in za te dogodke lahko ustvarite kode. To pomeni, da bi se koda takoj zgodila in če ste za ta dogodek podali kodo.

Excel to samodejno stori takoj, ko opazi, da se je dogodek zgodil. Zato morate samo napisati kodo in jo umestiti v pravilno podprogram dogodkov (to je opisano kasneje v tem članku).

Če na primer vstavite nov delovni list in želite, da ima predpono za leto, lahko zanj napišete kodo.

Zdaj, ko kdo vstavi nov delovni list, bi se ta koda samodejno izvedla in imenu delovnega lista dodala predpono leta.

Drug primer je lahko, da želite spremeniti barvo celice, ko nekdo dvakrat klikne nanjo. Za to lahko uporabite dogodek dvojnega klika.

Podobno lahko ustvarite kode VBA za številne takšne dogodke (kot bomo videli kasneje v tem članku).

Spodaj je kratek vizualni prikaz dogodka dvojnega klika v akciji. Takoj, ko dvakrat kliknem na celico A1. Excel takoj odpre polje s sporočilom, ki prikazuje naslov celice.

Dvojni klik je dogodek in prikaz okna s sporočilom je tisto, kar sem navedel v kodi, kadar koli se zgodi dogodek dvojnega klika.

Čeprav je zgornji primer neuporaben dogodek, upam, da vam bo pomagal razumeti, kaj v resnici so dogodki.

Različne vrste dogodkov Excel VBA

V Excelu so različni predmeti - na primer Excel sam (ki ga pogosto imenujemo aplikacija), delovni zvezki, delovni listi, grafikoni itd.

Vsak od teh predmetov ima lahko z njim povezane različne dogodke. Na primer:

• Če ustvarite nov delovni zvezek, je to dogodek na ravni aplikacije.
• Če dodate nov delovni list, je to dogodek na ravni delovnega zvezka.
• Če spremenite vrednost v celici na listu, je to dogodek na ravni delovnega lista.

Spodaj so različne vrste dogodkov, ki obstajajo v Excelu:

1. Dogodki na ravni delovnega lista: To so vrste dogodkov, ki bi se sprožili na podlagi dejanj na delovnem listu. Primeri teh dogodkov vključujejo spreminjanje celice na delovnem listu, spreminjanje izbire, dvoklik celice, desni klik celice itd.
2. Dogodki na ravni delovnega zvezka: Ti dogodki bi se sprožili na podlagi dejanj na ravni delovnega zvezka. Primeri teh dogodkov vključujejo dodajanje novega delovnega lista, shranjevanje delovnega zvezka, odpiranje delovnega zvezka, tiskanje dela ali celotnega delovnega zvezka itd.
3. Dogodki na ravni aplikacije: To so dogodki, ki se pojavijo v aplikaciji Excel. Primer teh bi bil zapiranje katerega koli odprtega delovnega zvezka ali odpiranje novega delovnega zvezka.
4. Dogodki na ravni uporabniškega obrazca: Ti dogodki bi se sprožili na podlagi dejanj v „uporabniškem obrazcu“. Primeri teh vključujejo inicializacijo uporabniškega obrazca ali klik na gumb v uporabniškem obrazcu.
5. Dogodki na grafikonu: To so dogodki, povezani z diagramom. List z grafikonom se razlikuje od delovnega lista (tam se večina od nas uporablja za delo v Excelu). Namen listov grafikona je shranjevanje grafikona. Primeri takšnih dogodkov bi vključevali spreminjanje serije grafikona ali spreminjanje velikosti grafikona.
6. Dogodki OnTime in OnKey: To sta dva dogodka, ki ne spadata v nobeno od zgornjih kategorij. Zato sem jih navedel ločeno. Dogodek „OnTime“ vam omogoča, da kodo izvedete ob določenem času ali po preteku določenega časa. Dogodek 'OnKey' vam omogoča, da izvedete kodo, ko uporabite določen pritisk tipke (ali kombinacijo pritiskov tipk).

Kam vstaviti kodo, povezano z dogodkom

V zgornjem razdelku sem obravnaval različne vrste dogodkov.

Glede na vrsto dogodka morate kodo vnesti v ustrezen predmet.

Če je na primer dogodek, povezan z delovnim listom, bi moral iti v okno kode predmeta delovnega lista. Če je povezan z delovnim zvezkom, bi moral iti v okno kode za predmet delovnega zvezka.

V VBA imajo različni predmeti - na primer delovni listi, delovni zvezki, grafikoni, uporabniški obrazci itd. Svoja okna za kode. Kodo dogodka morate vnesti v okno kode ustreznega predmeta. Na primer - če gre za dogodek na ravni delovnega zvezka, morate v oknu za kodo delovnega zvezka imeti kodo dogodka.

Naslednji razdelki zajemajo mesta, kjer lahko vnesete kodo dogodka:

V oknu kode delovnega lista

Ko odprete urejevalnik VB (z bližnjico na tipkovnici ALT + F11), boste v Raziskovalcu projektov opazili predmet delovnih listov. Za vsak delovni list v delovnem zvezku boste videli en predmet.

Ko dvokliknete predmet delovnega lista, v katerega želite vstaviti kodo, se odpre okno kode za ta delovni list.

Medtem ko lahko začnete pisati kodo iz nič, je veliko bolje izbrati dogodek s seznama možnosti in pustiti, da VBA samodejno vstavi ustrezno kodo za izbrani dogodek.

Če želite to narediti, morate najprej izbrati delovni list s spustnega menija v zgornjem levem kotu okna za kodo.

Ko iz spustnega menija izberete delovni list, dobite seznam vseh dogodkov, povezanih z delovnim listom. Iz spustnega menija v zgornjem desnem kotu okna za kodo lahko izberete tistega, ki ga želite uporabiti.

Takoj ko izberete dogodek, bo samodejno vnesel prvo in zadnjo vrstico kode za izbrani dogodek. Zdaj lahko kodo dodate med dve vrstici.

Opomba: Takoj, ko v spustnem meniju izberete Delovni list, boste v oknu za kodo opazili dve vrstici kode. Ko izberete dogodek, za katerega želite kodo, lahko izbrišete vrstice, ki so se privzeto pojavile.

Upoštevajte, da ima vsak delovni list svoje okno s kodom. Ko vnesete kodo za Sheet1, bo delovala le, če se bo dogodek zgodil v Sheet1.

V oknu ThisWorkbook Code

Tako kot delovne liste, če imate kodo dogodka na ravni delovnega zvezka, jo lahko postavite v okno s kodo tega delovnega zvezka.

Ko dvakrat kliknete ThisWorkbook, se odpre okno za kodo.

Iz spustnega menija v zgornjem levem kotu okna za kodo morate izbrati Delovni zvezek.

Ko na spustnem seznamu izberete Delovni zvezek, boste dobili seznam vseh dogodkov, povezanih z Delovnim zvezkom. Iz spustnega menija v zgornjem desnem kotu okna za kodo lahko izberete tistega, ki ga želite uporabiti.

Takoj ko izberete dogodek, bo samodejno vnesel prvo in zadnjo vrstico kode za izbrani dogodek. Zdaj lahko kodo dodate med dve vrstici.

Opomba: Takoj, ko v spustnem meniju izberete Delovni zvezek, boste v oknu za kodo opazili dve vrstici kode. Ko izberete dogodek, za katerega želite kodo, lahko izbrišete vrstice, ki so se privzeto pojavile.

V oknu Userform Code

Ko ustvarjate uporabniške obrazce v Excelu, lahko uporabite tudi dogodke uporabniškega obrazca za izvajanje kod na podlagi določenih dejanj. Na primer, lahko podate kodo, ki se izvede, ko kliknete gumb.

Medtem ko so predmeti Sheet in ThisWorkbook že na voljo, ko odprete urejevalnik VB, morate najprej ustvariti UserForm.

Če želite ustvariti uporabniški obrazec, z desno miškino tipko kliknite kateri koli predmet, pojdite na Vstavi in ​​kliknite na Uporabniški obrazec.

To bi v delovni zvezek vstavilo objekt UserForm.

Ko dvokliknete uporabniški obrazec (ali kateri koli predmet, ki ga dodate v uporabniški obrazec), se odpre okno kode za uporabniški obrazec.

Tako kot na delovnih listih ali v tej delovni knjigi lahko izberete dogodek, ki bo vstavil prvo in zadnjo vrstico za ta dogodek. Nato lahko kodo dodate sredi nje.

V oknu s kodo grafikona

V Excel lahko vstavite tudi liste grafikonov (ki se razlikujejo od delovnih listov). List z grafikoni naj bi vseboval samo grafikone.

Ko vstavite list grafikona, boste v urejevalniku VB lahko videli objekt lista grafikona.

Kodo dogodka lahko dodate v okno kode lista grafikona, tako kot smo to storili na delovnem listu.

Dvakrat kliknite na objekt lista grafikona v Raziskovalcu projektov. S tem se odpre okno kod za list z grafikoni.

Zdaj morate iz spustnega menija v zgornjem levem kotu okna za kodo izbrati grafikon.

Ko iz spustnega menija izberete grafikon, dobite seznam vseh dogodkov, povezanih s listom grafikona. Iz spustnega menija v zgornjem desnem kotu okna za kodo lahko izberete tistega, ki ga želite uporabiti.

Opomba: Takoj, ko iz spustnega menija izberete grafikon, boste v oknu za kodo opazili dve vrstici kode. Ko izberete dogodek, za katerega želite kodo, lahko izbrišete vrstice, ki so se privzeto pojavile.

V razrednem modulu

Module razreda je treba vstaviti tako kot uporabniške obrazce.

Modul razreda lahko vsebuje kodo, povezano z aplikacijo - to bi bil Excel sam in vgrajene grafikone.

Modul razreda bom v naslednjih tednih obravnaval kot ločeno vadnico.

Upoštevajte, da razen dogodkov OnTime in OnKey nobenega od zgornjih dogodkov ni mogoče shraniti v običajni modul VBA.

Razumevanje zaporedja dogodkov

Ko sprožite dogodek, se to ne zgodi ločeno. Prav tako lahko povzroči zaporedje več sprožilcev.

Ko na primer vstavite nov delovni list, se zgodijo naslednje stvari:

1. Dodan je nov delovni list
2. Prejšnji delovni list se deaktivira
3. Nov delovni list se aktivira

Čeprav vam v večini primerov morda ne bo treba skrbeti za zaporedje, če ustvarjate zapletene kode, ki temeljijo na dogodkih, je bolje poznati zaporedje, da se izognete nepričakovanim rezultatom.

Razumevanje vloge argumentov v dogodkih VBA

Preden preidemo na primere dogodkov in čudovite stvari, ki jih lahko naredite z njim, moram zajeti en pomemben koncept.

V dogodkih VBA obstajata dve vrsti kod:

• Brez argumentov
• Z argumenti

In v tem razdelku želim hitro obravnavati vlogo argumentov.

Spodaj je koda, ki nima argumenta (oklepaji so prazni):

Private Sub Workbook_Open () MsgBox "Ne pozabite izpolniti časovnega lista" End Sub

Z zgornjo kodo, ko odprete delovni zvezek, preprosto prikaže polje s sporočilom - »Ne pozabite izpolniti časovnega lista«.

Zdaj pa poglejmo kodo, ki ima argument.

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

Zgornja koda uporablja argument Sh, ki je opredeljen kot tip objekta. Argument Sh je lahko delovni list ali list grafikona, saj se zgornji dogodek sproži ob dodajanju novega lista.

Z dodelitvijo novega lista, ki je dodan v delovni zvezek, predmetni spremenljivki Sh, nam je VBA omogočila uporabo v kodi. Za sklicevanje na novo ime lista lahko uporabim Sh.Name.

Koncept argumentov bo koristen, ko boste v naslednjih razdelkih pregledali primere dogodkov VBA.

Dogodki na ravni delovnega zvezka (razloženo s primeri)

V nadaljevanju so najpogosteje uporabljeni dogodki v delovnem zvezku.

IME DOGODKA	KAJ TRIGGERS DOGODEK
Aktiviraj	Ko je delovni zvezek aktiviran
AfterSave	Ko je delovni zvezek nameščen kot dodatek
Pred Shrani	Ko je delovni zvezek shranjen
Pred Zapri	Ko je delovni zvezek zaprt
Pred tiskanjem	Ko je natisnjen delovni zvezek
Deaktiviraj	Ko je delovni zvezek deaktiviran
Nov list	Ko je dodan nov list
Odprto	Ko se odpre delovni zvezek
SheetActivate	Ko je aktiviran kateri koli list v delovnem zvezku
SheetBeforeDelete	Ko je kateri koli list izbrisan
SheetBeforeDoubleClick	Ko dvokliknete kateri koli list
SheetBeforeRightClick	Ko z desno tipko miške kliknete kateri koli list
ListCalculate	Ko se kateri koli list izračuna ali preračuna
SheetDeactivate	Ko je delovni zvezek deaktiviran
SheetPivotTableUpdate	Ko je delovni zvezek posodobljen
SheetSelectionChange	Ko se delovni zvezek spremeni
WindowActivate	Ko je delovni zvezek aktiviran
WindowDeactivate	Ko je delovni zvezek deaktiviran

Upoštevajte, da to ni popoln seznam. Celoten seznam najdete tukaj.

Ne pozabite, da je koda za dogodek delovnega zvezka shranjena v oknu s kodo predmetov ThisWorkbook.

Zdaj pa si oglejmo nekaj koristnih dogodkov iz delovnega zvezka in poglejmo, kako jih je mogoče uporabiti pri vsakodnevnem delu.

Odprti dogodek delovnega zvezka

Recimo, da želite uporabniku prikazati prijazen opomnik, da izpolni svoje urnike, ko odpre določen delovni zvezek.

Za to lahko uporabite spodnjo kodo:

Private Sub Workbook_Open () MsgBox "Ne pozabite izpolniti časovnega lista" End Sub

Zdaj, ko odprete delovni zvezek s to kodo, vam bo prikazal polje s sporočilom z navedenim sporočilom.

Pri delu s to kodo (ali kodami dogodkov v delovnem zvezku na splošno) morate vedeti nekaj stvari:

• Če ima delovni zvezek makro in ga želite shraniti, ga morate shraniti v obliki .XLSM. V nasprotnem primeru bi bila koda makra izgubljena.
• V zgornjem primeru bi se koda dogodka izvedla le, če so omogočeni makri. Morda boste videli rumeno vrstico, ki prosi za dovoljenje za omogočanje makrov. Dokler to ni omogočeno, se koda dogodka ne izvede.
• Koda dogodka delovnega zvezka je postavljena v okno kode predmeta ThisWorkbook.

To kodo lahko še izboljšate in sporočilo prikaže samo v petek.

Spodnja koda bi to naredila:

Zasebni pod Delovni zvezek_Open () wkday = Dan v tednu (Datum) Če je wkday = 6 Potem MsgBox "Ne pozabite izpolniti časovnega lista" End Sub

Upoštevajte, da je v funkciji Teden nedelji dodeljena vrednost 1, ponedeljku 2 in tako naprej.

Zato sem za petek uporabil 6.

Dogodek Odprti delovni zvezek je lahko koristen v številnih situacijah, na primer:

• Ko želite osebi, ko odpre delovni zvezek, pokazati sporočilo dobrodošlice.
• Ko želite prikazati opomnik, ko je delovni zvezek odprt.
• Ko želite v delovnem zvezku, ko je odprt, vedno aktivirati en poseben delovni list.
• Ko želite skupaj z delovnim zvezkom odpreti sorodne datoteke.
• Ko želite zajeti datum in čas vsakič, ko odprete delovni zvezek.

Dogodek NewSheet v delovnem zvezku

Dogodek NewSheet se sproži, ko v delovni zvezek vstavite nov list.

Recimo, da želite vnesti datum in čas v celico A1 na novo vstavljenega lista. Za to lahko uporabite spodnjo kodo:

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

Zgornja koda uporablja „On Error Resume Next“ za obravnavo primerov, ko nekdo vstavi list grafikona in ne delovnega lista. Ker list z grafikonom nima celice A1, bi se prikazala napaka, če ne uporabite možnosti »On Error Resume Next«.

Drug primer je lahko, ko želite uporabiti novo osnovno nastavitev ali oblikovanje za nov list takoj, ko je dodan. Na primer, če želite dodati nov list in želite, da samodejno dobi serijsko številko (do 100), lahko uporabite spodnjo kodo.

Private Sub Workbook_NewSheet (ByVal Sh As Object) o napaki Nadaljuj naprej z Sh.Range ("A1") .Value = "S. št." .Interior.Color = vbBlue .Font.Color = vb Beli konec z For i = 1 do 100 Sh.Range ("A1"). Offset (i, 0) .Varue = i Next i Sh.Range ("A1", Range ("A1"). Konec (xlDown)). Borders.LineStyle = xlNeprekinjen konec Sub

Zgornja koda tudi nekoliko formatira. Glavi celica daje modro barvo, pisava pa bela. Velja tudi za vse napolnjene celice.

Zgornja koda je primer, kako vam lahko kratka koda VBA pomaga ukrasti nekaj sekund vsakič, ko vstavite nov delovni list (v primeru, da morate to narediti vsakič).

Delovni zvezek Pred shranjevanjem

Ko shranite delovni zvezek, se sproži dogodek Shrani. Upoštevajte, da se najprej sproži dogodek in nato shrani delovni zvezek.

Pri shranjevanju Excelovega delovnega zvezka sta možna dva scenarija:

1. Prvič ga shranite in prikazal se bo pogovorno okno Shrani kot.
2. Shranili ste ga že prej in preprosto bo shranil in prepisal spremembe v že shranjeni različici.

Zdaj pa si oglejmo nekaj primerov, kjer lahko uporabite dogodek BeforeSave.

Recimo, da imate nov delovni zvezek, ki ga shranite prvič, in želite opomniti uporabnika, naj ga shrani v pogon K, potem lahko uporabite spodnjo kodo:

Private Sub Workbook_BeforeSave (ByVal SaveAsUI kot Boolean, Prekliči kot Boolean) Če SaveAsUI Nato MsgBox "Shrani to datoteko v pogonu K" End Sub

Če zgornja koda ni shranjena, je SaveAsUI True in prikaže pogovorno okno Shrani kot. Zgornja koda bi prikazala sporočilo, preden se prikaže pogovorno okno Shrani kot.

Drug primer je lahko posodobitev datuma in časa, ko je datoteka shranjena v določeni celici.

Spodnja koda bi vsakič, ko se datoteka shrani, vstavila datum in čas v celico A1 lista 1.

Zasebni pod-delovni zvezek_BeforeSave (ByVal SaveAsUI Kot Boolean, Prekliči kot Boolean) Delovni listi ("Sheet1"). Obseg ("A1") = Oblika (Zdaj, "dd-mmm-yyyy hh: mm: ss") End Sub

Upoštevajte, da se ta koda izvrši takoj, ko uporabnik shrani delovni zvezek. Če se delovni zvezek shranjuje prvič, se prikaže pogovorno okno Shrani kot. Toda koda se prikaže, ko se prikaže pogovorno okno Shrani kot. Na tej točki, če se odločite za preklic in shranjevanje delovnega zvezka, bosta datum in čas že vnesena v celico.

Delovni zvezek Pred zapiranjem dogodka

Preden se dogodek Zapri zgodi tik pred zaprtjem delovnega zvezka.

Spodnja koda ščiti vse delovne liste pred zaprtjem delovnega zvezka.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Dim sh As Delovni list za vsak sh v tem delovnem zvezku.

Ne pozabite, da se koda dogodka sproži takoj, ko zaprete delovni zvezek.

Pri tem dogodku je pomembno vedeti, da mu ni vseeno, ali je delovni zvezek dejansko zaprt ali ne.

Če delovni zvezek ni shranjen in se prikaže poziv z vprašanjem, ali naj delovni zvezek shranite ali ne, in pritisnete Prekliči, delovni zvezek ne bo shranjen.Koda dogodka pa bi bila takrat že izvedena.

Delovni zvezek Pred dogodkom Print

Ko podate ukaz za tiskanje (ali ukaz Predogled tiskanja), se sproži dogodek Pred tiskanjem.

Spodnja koda bi preračunala vse delovne liste pred tiskanjem delovnega zvezka.

Private Sub Workbook_BeforePrint (Prekliči kot logično) Za vsak ws na delovnih listih ws.Izračunaj Naslednji ws End Sub

Ko uporabnik natisne delovni zvezek, bi se dogodek sprožil, ne glede na to, ali natisne celoten delovni zvezek ali le njegov del.

Drug spodnji primer je koda, ki bi dodala datum in čas v nogo, ko je natisnjen delovni zvezek.

Private Sub Workbook_BeforePrint (Prekliči kot logično) Zatemni ws kot delovni list za vsako ws v ThisWorkbook.

Dogodki na ravni delovnega lista (razloženo s primeri)

Dogodki na delovnem listu se odvijajo na podlagi sprožilcev na delovnem listu.

V nadaljevanju so najpogosteje uporabljeni dogodki na delovnem listu.

Ime dogodka	Kaj sproži dogodek
Aktiviraj	Ko je delovni list aktiviran
Pred izbrisom	Preden se delovni list izbriše
PredDoubleClick	Pred dvoklikom na delovni list
PredRightClick	Pred desnim klikom na delovni list
Izračunaj	Preden se delovni list izračuna ali preračuna
Spremenite	Ko se celice na delovnem listu spremenijo
Deaktiviraj	Ko je delovni list deaktiviran
PivotTableUpdate	Ko se vrtilna tabela na delovnem listu posodobi
SelectionChange	Ko se izbira na delovnem listu spremeni

Upoštevajte, da to ni popoln seznam. Celoten seznam najdete tukaj.

Ne pozabite, da je koda za dogodek delovnega lista shranjena v oknu kode predmeta delovnega lista (v tistem, v katerem želite, da se dogodek sproži). V delovnem zvezku je lahko več delovnih listov, vaša koda pa bi se sprožila le, ko se dogodek odvija na delovnem listu, v katerem je vstavljen.

Zdaj pa si oglejmo nekaj koristnih dogodkov na delovnem listu in poglejmo, kako jih je mogoče uporabiti pri vsakodnevnem delu.

Delovni list Aktiviraj dogodek

Ta dogodek se sproži, ko aktivirate delovni list.

Spodnja koda odstrani zaščito lista, takoj ko je aktiviran.

Zasebni pod -delovni list_Activate () ActiveSheet. Odstrani zaščito pod -pod

Ta dogodek lahko uporabite tudi za zagotovitev, da je izbrana določena celica ali obseg celic (ali imenovani obseg), takoj ko aktivirate delovni list. Spodnja koda bi izbrala celico D1 takoj, ko aktivirate list.

Private Sub Worksheet_Activate () ActiveSheet.Range ("D1"). Izberite End Sub

Dogodek spremembe delovnega lista

Dogodek spremembe se sproži vsakič, ko spremenite delovni list.

No … ne vedno.

Dogodek sprožijo nekatere spremembe, nekatere pa ne. Tu je seznam nekaterih sprememb, ki ne bodo sprožile dogodka:

• Ko spremenite oblikovanje celice (velikost pisave, barva, obroba itd.).
• Ko združite celice. To je presenetljivo, saj včasih združevanje celic odstrani tudi vsebino iz vseh celic, razen iz zgornje leve.
• Ko dodate, izbrišete ali uredite komentar celice.
• Ko razvrstite obseg celic.
• Ko uporabljate iskanje cilja.

Naslednje spremembe bi sprožile dogodek (čeprav mislite, da ne bi smel):

• Oblikovanje kopiranja in lepljenja bi sprožilo dogodek.
• Izbris oblikovanja bi sprožil dogodek.
• Izvajanje preverjanja črkovanja bi sprožilo dogodek.

Spodaj je koda, ki prikazuje polje s sporočilom z naslovom celice, ki je bila spremenjena.

Private Sub Worksheet_Change (ByVal Target As Range) MsgBox "Pravkar ste spremenili" & Target.Address End Sub

Čeprav je to neuporaben makro, vam pokaže, kako z argumentom Target ugotovite, katere celice so bile spremenjene.

Zdaj pa poglejmo še nekaj uporabnih primerov.

Recimo, da imate nabor celic (recimo A1: D10) in želite prikazati poziv ter vprašati uporabnika, ali si resnično želi spremeniti celico v tem obsegu ali ne, lahko uporabite spodnjo kodo.

Prikaže poziv z dvema gumboma - Da in Ne. Če uporabnik izbere "Da", se sprememba izvede, sicer pa se obrne.

Private Sub Worksheet_Change (ByVal Target As Range) Če je Target.Row <= 10 In Target.Column <= 4 Potem Ans = MsgBox ("Spreminjate celice v A1: D10. Ali ste prepričani, da to želite?", vbYesNo) End If If If Ans = vbNo Potem Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

V zgornji kodi preverimo, ali je ciljna celica v prvih 4 stolpcih in prvih 10 vrsticah. V tem primeru se prikaže okno s sporočilom. Tudi če je uporabnik v polju za sporočilo izbral Ne, se sprememba obrne (z ukazom Application.Undo).

Upoštevajte, da sem pred vrstico Application.Undo uporabil Application.EnableEvents = False. In potem sem ga obrnil z uporabo Application.EnableEvent = True v naslednji vrstici.

To je potrebno, saj ob razveljavitvi sproži tudi dogodek spremembe. Če možnosti EnableEvent ne nastavim na False, bo sprožil sprožitev dogodka spremembe.

Spremembe imenovanega obsega lahko spremljate tudi z dogodkom spremembe. Na primer, če imate poimenovano območje z imenom »DataRange« in želite prikazati poziv, če uporabnik spremeni imenovan obseg, lahko uporabite spodnjo kodo:

Private Sub Worksheet_Change (ByVal Target As Range) Dim DRange As Range Set DRange = Range ("DataRange") If Not Intersect (Target, DRange) is Nothing then MsgBox "Pravkar ste spremenili obseg podatkov" End If End Sub

Zgornja koda preveri, ali ima celica/obseg, v katerem ste naredili spremembe, skupne celice za območje podatkov. Če se prikaže, se prikaže polje s sporočilom.

Izbira delovnega zvezka Spremeni dogodek

Dogodek spremembe izbire se sproži, kadar pride do spremembe izbora na delovnem listu.

Spodnja koda bi preračunala list takoj, ko spremenite izbiro.

Zasebni pod -delovni list_SelectionChange (ByVal Target As Range) Aplikacija.

Drug primer tega dogodka je, ko želite označiti aktivno vrstico in stolpec izbrane celice.

Nekaj, kot je prikazano spodaj:

Naslednja koda lahko to stori:

Zasebni pod -delovni list_SelectionChange (ByVal Target As Range) Celice.Interior.ColorIndex = xlNone With ActiveCell .EntireRow.Interior.Color = RGB (248, 203, 173) .EntireColumn.Interior.Color = RGB (180, 198, 231) Končaj z End Sub

Koda najprej odstrani barvo ozadja iz vseh celic, nato pa tisto, ki je omenjena v kodi, uporabi za aktivno vrstico in stolpec.

In to je problem s to kodo. Da odstranjuje barvo iz vseh celic.

Če želite označiti aktivno vrstico/stolpec in obdržati barvo v drugih celicah nedotaknjeno, uporabite tehniko, prikazano v tej vadnici.

Dogodek DoubleClick v delovnem zvezku

To je eden mojih najljubših dogodkov na delovnem listu in videli boste veliko vadnic, kjer sem to uporabil (na primer ta ali ta).

Ta dogodek se sproži, ko dvokliknete celico.

Naj vam pokažem, kako super je to.

S spodnjo kodo lahko dvokliknete celico in ta bo uporabila barvo ozadja, spremenila barvo pisave in naredila besedilo v celici krepko;

Private Sub Worksheet_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

To je lahko koristno, ko pregledujete seznam celic in želite označiti nekaj izbranih. Čeprav lahko s tipko F4 ponovite zadnji korak, bi lahko uporabil le eno obliko oblikovanja. S tem dogodkom dvojnega klika lahko vse tri uporabite le z dvojnim klikom.

Upoštevajte, da sem v zgornji kodi naredil vrednost Cancel = True.

To se naredi tako, da je privzeto dejanje dvojnega klika onemogočeno - to pomeni, da preidete v način urejanja. Če prekličete = res, Excel ne bo pripeljal v način urejanja, če dvokliknete celico.

Tu je še en primer.

Če imate v Excelu seznam opravil, lahko z dogodkom dvojnega klika uporabite prečrtano obliko, da označite nalogo kot dokončano.

Nekaj, kot je prikazano spodaj:

Tukaj je koda, ki bo to naredila:

Zasebni pod -delovni list_BeforeDoubleClick (Cilj ByVal kot obseg, Prekliči kot logično) Prekliči = True CurrFormat = Target.Font.Strikethrough If CurrFormat Potem Target.Font.Strikethrough = False Else Target.Font.Strikethrough = True End Če konča Sub

Upoštevajte, da sem v tej kodi dvakrat kliknil kot preklopni dogodek. Ko dvokliknete celico, preveri, ali je prečrtana oblika že uporabljena. Če je bil, dvojni klik odstrani prečrtano obliko, če pa še ni, se uporabi prečrtana oblika.

Dogodek OnTime v Excelu VBA

Dogodki, ki smo jih doslej videli v tem članku, so bili povezani z enim od predmetov programa Excel, pa naj bo to delovni zvezek, delovni list, list grafikona ali uporabniški obrazci itd.

Dogodek OnTime se razlikuje od drugih dogodkov, saj ga je mogoče shraniti v običajen modul VBA (ostale pa je treba vstaviti v kodno okno predmetov, kot so Ta delovna knjiga ali delovni listi ali uporabniški obrazci).

Znotraj običajnega modula VBA se uporablja kot metoda aplikacijskega objekta.

Razlog za ta dogodek je, da ga je mogoče sprožiti glede na čas, ki ga določite. Če na primer želim, da se list preračuna vsakih 5 minut, lahko zanj uporabim dogodek OnTime.

Ali pa, če želim prikazati sporočilo/opomnik ob določenem času v dnevu, lahko uporabim dogodek OnTime.

Spodaj je koda, ki bo vsak dan ob 14. uri prikazala sporočilo.

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

Ne pozabite, da morate to kodo postaviti v običajni modul VBA,

Medtem ko bi se dogodek OnTime sprožil ob določenem času, morate makro kadar koli zagnati ročno. Ko zaženete makro, bo počakal do 14. ure in nato poklical makro »ShowMessage«.

Makro ShowMessage bi nato prikazal sporočilo.

Dogodek OnTime ima štiri argumente:

Application.OnTime (Najzgodnejši čas, Postopek, Najnovejši čas, Urnik)

• Najzgodnejši čas: Čas, ko želite zagnati postopek.
• Postopek: Ime postopka, ki ga je treba izvesti.
• LatestTime (izbirno): Če se izvaja druga koda in vaše kode ni mogoče zagnati v določenem času, lahko določite najnovejši čas, na katerega naj počaka. Na primer, lahko je EarliestTime + 45 (kar pomeni, da bo počakal 45 sekund, da se drugi postopek zaključi). Če tudi po 45 sekundah postopek ne more teči, se postopek opusti. Če tega ne določite, bi Excel počakal, da se koda zažene, in jo nato zagnal.
• Urnik (neobvezno): Če je nastavljeno na True, načrtuje nov časovni postopek. Če je False, prekliče predhodno nastavljen postopek. Privzeto je to res.

V zgornjem primeru smo uporabili le prva dva argumenta.

Poglejmo še en primer.

Spodnja koda bi osvežila delovni list vsakih 5 minut.

Zatemni NextRefresh kot datum Sub RefreshSheet () ThisWorkbook.Worksheets ("Sheet1"). Calculate NextRefresh = Now + TimeValue ("00:05:00") Application.OnTime NextRefresh, "RefreshSheet" End Sub Sub StopRefresh () On Napaka Nadaljuj Next Application.OnTime NextRefresh, "RefreshSheet",, False End Sub

Zgornja koda bi osvežila delovni list vsakih 5 minut.

Za določitev trenutnega časa uporablja funkcijo Now in trenutnemu času doda 5 minut.

Dogodek OnTime bi se nadaljeval, dokler ga ne ustavite. Če zaprete delovni zvezek in aplikacija Excel še vedno deluje (drugi delovni zvezki so odprti), bi se delovni zvezek, v katerem se izvaja dogodek OnTime, znova odprl.

To je bolje rešiti s posebno ustavitvijo dogodka OnTime.

V zgornji kodi imam kodo StopRefresh, vendar jo morate izvesti, da ustavite dogodek OnTime. To lahko storite ročno, dodelite gumbu in to storite tako, da pritisnete gumb ali ga pokličete iz dogodka Zapri delovni zvezek.

Private Sub Workbook_BeforeClose (Cancel As Boolean) Call StopRefresh End Sub

Zgornja koda dogodka „BeforeClose“ se nahaja v oknu s kodo ThisWorkbook.

Dogodek OnKey v Excelu VBA

Ko delate z Excelom, nenehno spremlja pritiske tipk, ki jih uporabljate. To nam omogoča, da pritisnemo tipke kot sprožilec dogodka.

Z dogodkom OnKey lahko podate pritisk tipke (ali kombinacijo pritiskov tipk) in kodo, ki jo je treba izvesti, ko se ta pritisk tipke uporabi. Ko pritisnete te tipke, bo za to izvedla kodo.

Tako kot dogodek OnTime morate imeti tudi način, da prekličete dogodek OnKey. Ko dogodek OnKey nastavite za določen pritisk tipke, postane na voljo v vseh odprtih delovnih zvezkih.

Preden vam pokažem primer uporabe dogodka OnKey, naj najprej delim ključne kode, ki so vam na voljo v VBA.

KLJUČ	KODA
Backspace	{BACKSPACE} ali {BS}
Odmor	{BREAK}
Caps Lock	{CAPSLOCK}
Izbriši	{DELETE} ali {DEL}
Puščica dol	{DOL]
Konec	{END}
Vnesite	~
Enter (na numerični tipkovnici)	{ENTER}
Pobeg	{ESCAPE} ali {ESC}
Domov	{HOME}
Ins	{VSTAVI}
Puščica levo	{LEFT}
NumLock	{NUMLOCK}
PageDown	{PGDN}
Stran gor	{PGUP}
Puščica desno	{PRAV}
Scroll Lock	{SCROLLOCK}
Zavihek	{TAB}
Puščica navzgor	{UP}
F1 do F15	{F1} do {F15}

Kadar morate uporabiti kateri koli dogodek na tipkovnici, morate zanj uporabiti kodo.

Zgornja tabela vsebuje kode za posamezne pritiske tipk.

Kombinirate jih lahko tudi z naslednjimi kodami:

• Premik: + (Znak plus)
• Nadzor: ^ (Caret)
• Nadomestni: % (Odstotek)

Na primer, za Alt F4 morate uporabiti kodo: "%{F4}” - kjer je % za tipko ALT in {F4} za tipko F4.

Zdaj pa poglejmo primer (ne pozabite, da je koda za dogodke OnKey postavljena v običajni modul VBA).

Ko pritisnete tipko PageUp ali PageDown, skoči 29 vrstic nad/pod aktivno celico (vsaj to počne na mojem prenosnem računalniku).

Če želite, da preskoči le 5 vrstic hkrati, lahko uporabite spodnjo kodo:

Sub PageUpDOwnKeys () Application.OnKey "{PgUp}", "PageUpMod" Application.OnKey "{PgDn}", "PageDownMod" End Sub Sub Page PageUpMod () On Napaka Nadaljuj Naslednji ActiveCell.Offset (-5, 0). Sub Sub PageDownMod () On Napaka Nadaljuj Naslednji ActiveCell.Offset (5, 0). Aktiviraj End Sub

Ko zaženete prvi del kode, se bodo zagnali dogodki OnKey. Ko se to izvede, bi s tipko PageUp in tipko PageDown kazalec preskočil le 5 vrstic hkrati.

Upoštevajte, da smo uporabili 'On Error Resume Next', da zagotovimo, da se napake ne upoštevajo. Te napake se lahko pojavijo, ko pritisnete tipko PageUp, tudi ko ste na vrhu delovnega lista. Ker ni več vrstic za preskok, bi koda pokazala napako. Ker pa smo uporabili možnost »On Error Resume Next«, se ne upošteva.

Če želite zagotoviti, da so ti dogodki OnKey na voljo, morate zagnati prvi del kode. Če želite, da je ta na voljo takoj, ko odprete delovni zvezek, ga lahko postavite v okno s kodo tega delovnega zvezka.

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

Spodnja koda vrne ključe v njihovo normalno delovanje.

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

Če v metodi OnKey ne navedete drugega argumenta, se bo pritisk na tipko vrnil v običajno delovanje.

Če želite preklicati funkcionalnost pritiska na tipko, tako da Excel pri uporabi te tipke ne stori ničesar, morate kot drugi argument uporabiti prazen niz.

V spodnji kodi Excel ne bi storil ničesar, če uporabljamo tipki PageUp ali PageDown.

Pod Ignore_PageUpDownKeys () Application.OnKey "{PgUp}", "" Application.OnKey "{PgDn}", "" End Sub

Onemogočanje dogodkov v VBA

Včasih boste morali za nemoteno delovanje kode onemogočiti dogodke.

Recimo, da imam obseg (A1: D10) in želim prikazati sporočilo vsakič, ko se celica spremeni v tem območju. Zato pokažem polje s sporočilom in vprašam uporabnika, ali je prepričan, da želi spremeniti. Če je odgovor pritrdilen, se sprememba izvede, če pa je odgovor ne, ga VBA razveljavi.

Uporabite lahko spodnjo kodo:

Private Sub Worksheet_Change (ByVal Target As Range) Če je Target.Row <= 10 In Target.Column <= 4 Potem Ans = MsgBox ("Spreminjate celice v A1: D10. Ali ste prepričani, da to želite?", vbDaNe) End If If If Ans = vbNo Potem Application.Undo End If End Sub

Težava s to kodo je, da ko uporabnik v polju s sporočilom izbere Ne, se dejanje obrne (kot sem uporabil Application.Undo).

Ko se razveljavi in ​​se vrednost spremeni na prvotno, se znova sproži dogodek spremembe VBA in uporabniku se znova prikaže isto polje s sporočilom.

To pomeni, da lahko v polju s sporočilom še naprej kliknete NE in se bo še naprej prikazoval. To se zgodi, ko ste se v tem primeru zataknili v neskončno zanko.

Da bi se izognili takšnim primerom, morate onemogočiti dogodke, da se dogodek spremembe (ali kateri koli drug dogodek) ne sproži.

Naslednja koda bi v tem primeru dobro delovala:

Private Sub Worksheet_Change (ByVal Target As Range) Če je Target.Row <= 10 In Target.Column <= 4 Potem Ans = MsgBox ("Spreminjate celice v A1: D10. Ali ste prepričani, da to želite?", vbYesNo) End If If If Ans = vbNo Potem Application.EnableEvents = False Application.Undo Application.EnableEvents = True End If End Sub

V zgornji kodi, tik nad vrstico Application.Undo, smo uporabili - Application.EnableEvents = False.

Če nastavite EnableEvents na False, ne bi sprožili nobenega dogodka (v trenutnem ali odprtih delovnih zvezkih).

Ko zaključimo operacijo razveljavitve, lahko lastnost EnableEvents preklopimo nazaj na True.

Upoštevajte, da onemogočanje dogodkov vpliva na vse delovne zvezke, ki so trenutno odprti (ali odprti, medtem ko je možnost EnableEvents nastavljena na False). Na primer, kot del kode, če odprete nov delovni zvezek, dogodek Odpiranje delovnega zvezka ne bi deloval.

Vpliv dogodkov Razveljavi sklad

Naj vam najprej povem, kaj je Undo Stack.

Ko delate v Excelu, nenehno spremlja vaša dejanja. Ko naredite napako, lahko vedno uporabite Control + Z za vrnitev na prejšnji korak (tj. Razveljavite trenutno dejanje).

Če dvakrat pritisnete Control + Z, se vrnete dva koraka nazaj. Ti koraki, ki ste jih izvedli, so shranjeni kot del sklada za razveljavitev.

Vsak dogodek, ki spremeni delovni list, uniči ta niz razveljavitve.To pomeni, da če sem naredil 5 stvari, preden sem sprožil dogodek, ne bom mogel uporabiti Control + Z za vrnitev na prejšnje korake. Sprožanje dogodka mi je uničilo ta sklad.

V spodnji kodi uporabljam VBA za vnos časovnega žiga v celico A1 vsakič, ko pride do spremembe na delovnem listu.

Private Sub Worksheet_Change (ByVal Target As Range) Application.EnableEvents = False Range ("A1"). Value = Format (Now, "dd-mmm-yyyy hh: mm: ss") Application.EnableEvents = True End Sub

Ker spreminjam delovni list, bo to uničilo sklad za razveljavitev.

Upoštevajte tudi, da to ni omejeno samo na dogodke.

Če imate kodo, ki je shranjena v običajnem modulu VBA, in naredite spremembo na delovnem listu, bi to uničilo tudi razveljavitveni sklad v Excelu.

Na primer, spodnja koda preprosto vnese besedilo »Pozdravljeni« v celico A1, vendar bi celo izvajanje uničilo niz razveljavitve.

Podvrsta Področje Zdravo () Obseg ("A1"). Vrednost = "Pozdravljeni" Konec podp

Morda vam bodo všeč tudi naslednji vadnici Excel VBA:

• Delo s celicami in obsegi v Excelu VBA.
• Delo z delovnimi listi v Excelu VBA.
• Delo z delovnimi zvezki v Excelu VBA.
• Zanke Excel VBA - najboljši vodnik.
• Uporaba IF Potem Else Statment v Excelu VBA.
• Za naslednjo zanko v Excelu.
• Ustvarjanje uporabniško določenih funkcij v Excelu VBA.
• Kako ustvariti in uporabljati dodatke v Excelu.
• Ustvarite in ponovno uporabite makre tako, da jih shranite v delovni zvezek Personal Macro.