VBA listi - končni vodnik

To je najboljši vodnik za delo z listi / delovnimi listi v Excelu.

Na dnu tega vodnika smo ustvarili goljufanje pogostih ukazov za delo z listi.

Listi vs. Delovni listi

Obstajata dva načina za sklicevanje na liste z uporabo VBA. Prvi je s predmetom Sheets:

1	Listi ("List1"). Aktivirajte

Druga je z objektom Delovni listi:

1	Delovni listi ("List1"). Aktivirajte

V 99% primerov sta ta dva predmeta enaka. Pravzaprav, če ste na spletu iskali primere kode VBA, ste verjetno videli oba uporabljena predmeta. Tu je razlika:

Zbirka listov vsebuje delovne liste in liste z grafikoni.

Uporabite torej liste, če želite vključiti redne delovne liste in liste z grafikoni. Uporabite delovne liste, če želite izključiti liste grafikonov. V preostalem delu tega priročnika bomo liste in delovne liste uporabljali zamenljivo.

Sklicevanje na liste

Sklicevanje na liste je več različnih načinov:

• ActiveSheet
• Ime zavihka lista
• Indeksna številka lista
• Koda imena lista

ActiveSheet

ActiveSheet je list, ki je trenutno aktiven. Z drugimi besedami, če ste zaustavili kodo in pogledali Excel, je viden list. Spodnji primer kode bo prikazal polje za sporočila z imenom ActiveSheet.

1	MsgBox ActiveSheet.Name

Ime lista

Verjetno ste najbolj seznanjeni s sklicevanjem na liste po imenu zavihka:

1	Listi ("TabName"). Aktivirajte

Indeksna številka lista

Številka indeksa listov je položaj lista v delovnem zvezku. 1 je prvi list. 2 je drugi list itd .:

1	Listi (1). Aktivirajte

Indeksna številka lista - zadnji list v delovnem zvezku

Če se želite sklicevati na zadnji list v delovnem zvezku, uporabite Sheets.Count, da dobite zadnjo številko indeksa:

1	Listi (Sheets.Count). Aktivirajte

List "Kodirano ime"

Koda lista je ime predmeta v VBA:

1	CodeName.Aktiviraj

Sklicevanje na liste v drugih delovnih zvezkih

Preglednice so tudi enostavne za sklicevanje v drugih delovnih zvezkih. Če želite to narediti, morate uporabiti predmet Delovni zvezki:

1	Delovni zvezki ("VBA_Examples.xlsm"). Delovni listi ("List1"). Aktivirajte

Pomembno: Delovni zvezek mora biti odprt, preden se lahko sklicujete na njegove liste.

Aktiviraj proti Izberite List

V drugem članku obravnavamo vse o aktiviranju in izbiri listov. Kratka različica je naslednja:

Ko aktivirate list, postane ActiveSheet. To je list, ki bi ga videli, če bi pogledali svoj program Excel. Naenkrat je lahko aktiviran samo en list.

Aktivirajte list

1	Listi ("List1"). Aktivirajte

Ko izberete list, postane tudi list ActiveSheet. Lahko pa izberete več listov hkrati. Ko je hkrati izbranih več listov, je "zgornji" list ActiveSheet. Lahko pa preklopite list ActiveSheet znotraj izbranih listov.

Izberite list

1	Listi ("List1"). Izberite

Izberite Več listov

Z matriko izberite več listov hkrati:

1	Delovni listi (Niz ("List2", "List3")). Izberite

Spremenljivka delovnega lista

Če spremenljivki dodelite delovni list, se lahko sklicujete na delovni list po imenu spremenljivke. To lahko prihrani veliko tipkanja in olajša branje kode. Obstaja tudi veliko drugih razlogov, zakaj bi morda želeli uporabiti spremenljivke.

Če želite razglasiti spremenljivko delovnega lista:

1	Zatemni ws kot delovni list

Spremenljivki dodelite delovni list:

1	Set ws = Sheets ("List1")

Zdaj se lahko v svoji kodi sklicujete na spremenljivko delovnega lista:

1	ws.Aktiviraj

Prelistajte vse liste v delovnem zvezku

Spremenljivke delovnega lista so bistvene, če želite prelistati vse delovne liste v delovnem zvezku. Najlažji način za to je:

12345

Zatemni kot delovni listZa vsak ws v delovnih listihMsgBox ws.nameNaslednji ws

Ta koda se bo pomikala po vseh delovnih listih v delovnem zvezku in prikazala ime vsakega delovnega lista v polju za sporočila. Prelistavanje vseh listov v delovnem zvezku je zelo uporabno pri zaklepanju / odklepanju ali skrivanju / razkrivanju več delovnih listov hkrati.

Zaščita delovnega lista

Zaščita delovnega zvezka

Zaščita delovnega zvezka zaklene delovni zvezek pred strukturnimi spremembami, kot je dodajanje, brisanje, premikanje ali skrivanje delovnih listov.

Zaščito delovnega zvezka lahko vklopite z uporabo VBA:

1	ActiveWorkbook.Protect Password: = "Geslo"

ali onemogočite zaščito delovnega zvezka:

1	ActiveWorkbook.UnProtect Password: = "Geslo"

Opomba: Brez gesla lahko zaščitite / odstranite zaščito tudi tako, da izpustite argument Geslo:

1	ActiveWorkbook.Protect

Zaščita delovnega lista

Zaščita na ravni delovnega lista preprečuje spremembe posameznih delovnih listov.

Zaščitite delovni list

1	Delovni listi ("List1"). Zaščitite "Geslo"

Odstrani zaščito delovnega lista

1	Delovni listi ("List1"). Odstrani zaščito "Geslo"

Pri zaščiti delovnih listov obstajajo različne možnosti (dovolite spremembe oblikovanja, dovolite, da uporabnik vstavi vrstice itd.) Za snemanje želenih nastavitev priporočamo uporabo snemalnika makrov.

Tukaj podrobneje obravnavamo zaščito delovnega lista.

Vidna lastnost delovnega lista

Morda že veste, da je delovne liste mogoče skriti:

Dejansko obstajajo tri nastavitve vidnosti delovnega lista: Vidna, Skrita in Zelo skrito.Skrite liste lahko odkrije vsak običajni uporabnik Excela - z desnim klikom na področju zavihka delovnega lista (prikazano zgoraj). Zelo skrite liste lahko skrijete samo s kodo VBA ali iz urejevalnika VBA. Za skrivanje / razkrivanje delovnih listov uporabite naslednje primere kode:

Razkrij delovni list

1	Delovni listi ("List1"). Visible = xlSheetVisible

Skrij delovni list

1	Delovni listi ("List1"). Visible = xlSheetHidden

Zelo skrije delovni list

1	Delovni listi ("List1"). Vidno = xlSheetVeryHidden

Dogodki na ravni delovnega lista

Dogodki so sprožilci, ki lahko povzročijo izvajanje »postopkov dogodkov«. Na primer, lahko povzroči, da se koda zažene vsakič, ko se spremeni katera koli celica na delovnem listu ali ko je delovni list aktiviran.

Postopke dogodkov na delovnem listu je treba vnesti v modul delovnega lista:

Obstaja veliko dogodkov na delovnem listu. Če si želite ogledati celoten seznam, pojdite na modul delovnega lista, v prvem spustnem meniju izberite »Delovni list«. Nato iz drugega spustnega menija izberite postopek dogodka, da ga vstavite v modul.

Delovni list Aktiviraj dogodek

Dogodki za aktiviranje delovnega lista se izvajajo vsakič, ko se delovni list odpre.

123	Zasebni pod -delovni list_Activate ()Razpon ("A1"). IzberiteEnd Sub

Ta koda bo izbrala celico A1 (ponastavitev območja pogleda v zgornji levi del delovnega lista) vsakič, ko se delovni list odpre.

Dogodek spremembe delovnega lista

Dogodki za spremembo delovnega lista se izvajajo vsakič, ko se spremeni vrednost celice na delovnem listu. Za več informacij preberite naš vodič o dogodkih spremembe delovnega lista.

Goljufski list delovnega lista

Spodaj boste našli listo, ki vsebuje pogoste primere kod za delo z listi v VBA

Upravljalni list delovnih listov VBA

Delovni listi VBA Cheatsheet

Opis	Primer kode
Sklicevanje in aktiviranje listov
Ime zavihka	Listi ("Vnos"). Aktivirajte
Koda VBA	List 1. Aktivirajte
Indeksni položaj	Listi (1). Aktivirajte
Izberite List
Izberite List	Listi ("Vnos"). Izberite
Nastavite na spremenljivo	Zatemni kot delovni list Nastavite ws = ActiveSheet
Ime / Preimenuj	ActiveSheet.Name = "Novo ime"
Naslednji list	ActiveSheet.Next.Activate
Prelistajte vse liste	Zatemni kot delovni list Za vsak ws v delovnih listih Msgbox ws.name Naslednji ws
Prelistajte izbrane liste	Zatemni kot delovni list Za vsako ws v ActiveWindow.SelectedSheets MsgBox ws.Name Naslednji ws
Pridobite ActiveSheet	MsgBox ActiveSheet.Name
Dodaj list	Listi.Dodaj
Dodajte list in ime	Sheets.Add.Name = "Nov list"
Dodajte list z imenom iz celice	Sheets.Add.Name = vrednost ("a3"). Vrednost
Dodaj list za drugim	Sheets.Add After: = Sheets ("Input")
Dodajte list po in ime	Sheets.Add (After: = Sheets ("Input")). Name = "NewSheet"
Dodajte list Pred in ime	Sheets.Add (Before: = Sheets ("Input")). Name = "NewSheet"
Dodaj list na konec delovnega zvezka	Sheets.Add After: = Sheets (Sheets.Count)
Dodaj list na začetek delovnega zvezka	Sheets.Add (Before: = Sheets (1)). Name = "FirstSheet"
Dodaj list spremenljivki	Zatemni kot delovni list Set ws = Sheets.Add
Kopiraj delovne liste
Premakni list na konec delovnega zvezka	Listi ("List1"). Premakni se po: = Listi (Sheets.Count)
Do novega delovnega zvezka	Listi ("List1"). Kopiraj
Izbrani listi za nov delovni zvezek	ActiveWindow.SelectedSheets.Copy
Pred drugim listom	Listi ("List1"). Kopiraj prej: = Listi ("List2")
Pred prvim listom	Listi ("List1"). Kopiraj prej: = Listi (1)
Po zadnjem listu	List ("List1"). Kopiraj po: = List (Sheets.Count)
Kopija in ime	List ("List1"). Kopiraj po: = List (Sheets.Count) ActiveSheet.Name = "Zadnji list"
Kopiraj in poimenuj iz vrednosti celice	List ("List1"). Kopiraj po: = List (Sheets.Count) ActiveSheet.Name = Obseg ("A1"). Vrednost
V drug delovni zvezek	Listi ("List1"). Kopiraj pred: = Delovni zvezki ("Primer.xlsm"). Listi (1)
Skrij / razkrij liste
Skrij list	Listi ("List1"). Visible = False ali Listi ("List1"). Visible = xlSheetHidden
Razkrij list	Listi ("List1"). Vidno = Res ali Listi ("List1"). Vidni = xlSheetVisible
Zelo skrije list	Listi (»List1«). Vidno = xlSheetVeryHidden
Izbriši ali počisti liste
Izbriši list	Listi ("List1"). Izbriši
Izbriši list (ravnanje z napakami)	On Napaka Nadaljuj Naprej Listi ("List1"). Izbriši Pri napaki Pojdi na 0
Izbriši list (brez poziva)	Application.DisplayAlerts = Napačno Listi ("List1"). Izbriši Application.DisplayAlerts = Res
Čist list	Listi ("List1"). Celice.Čisti
Samo vsebina čistega lista	Listi ("List1"). Celice.ClearContents
Clear Sheet UsedRange	Listi ("List1"). UsedRange.Clear
Zaščitite ali odstranite zaščito listov
Odstrani zaščito (brez gesla)	Listi ("List1"). Odstrani zaščito
Odstrani zaščito (geslo)	Listi (»List1«). Odstrani zaščito »Geslo«
Zaščita (brez gesla)	Listi ("List1"). Zaščitite
Zaščita (geslo)	Listi ("List1"). Zaščitite "Geslo"
Zaščitite, vendar dovolite dostop VBA	Listi ("List1"). Zaščitite UserInterfaceOnly: = True
Odstranite zaščito vseh listov	Zatemni kot delovni list Za vsak ws na delovnih listih ws.Odstrani "geslo" Naslednji ws