Naudodami „Excel“ makrokomandas galite pagreitinti darbą ir sutaupyti daug laiko.
Vienas iš būdų gauti VBA kodą yra įrašyti makrokomandą ir paimti jos sukurtą kodą. Tačiau tas makrokomandos įrašymo kodas dažnai yra pilnas kodo, kurio iš tikrųjų nereikia. Makro įrašymo įrenginys taip pat turi tam tikrų apribojimų.
Taigi verta turėti naudingų VBA makro kodų rinkinį, kurį galite turėti savo galinėje kišenėje ir naudoti, kai reikia.
Nors „Excel VBA“ makro kodo rašymas iš pradžių gali užtrukti, kai jį padarysite, galėsite palikti jį prieinamą kaip nuorodą ir naudoti, kai tik prireiks.
Šiame didžiuliame straipsnyje išvardinsiu keletą naudingų „Excel“ makrokomandų pavyzdžių, kurių man dažnai reikia, ir laikysiu savo asmeniniame saugykloje.
Aš nuolat atnaujinsiu šią mokymo programą, pateikdamas daugiau makro pavyzdžių. Jei manote, kad kažkas turėtų būti sąraše, tiesiog palikite komentarą.
Galite pažymėti šį puslapį, kad galėtumėte naudoti ateityje.
Dabar, kol nesileisiu į makrokomandos pavyzdį ir pateiksiu jums VBA kodą, pirmiausia parodysiu, kaip naudoti šiuos pavyzdinius kodus.
„Excel“ makrokomandų pavyzdžių kodo naudojimas
Štai veiksmai, kuriuos turite atlikti, jei norite naudoti kodą iš bet kurio iš pavyzdžių:
- Atidarykite darbaknygę, kurioje norite naudoti makrokomandą.
- Laikykite nuspaudę ALT klavišą ir paspauskite F11. Tai atveria VB redaktorių.
- Dešiniuoju pelės mygtuku spustelėkite bet kurį projekto tyrinėtojo objektą.
- Eikite į Įterpti -> Modulis.
- Nukopijuokite ir įklijuokite kodą modulio kodo lange.
Jei pavyzdyje sakoma, kad turite įklijuoti kodą darbalapio kodo lange, dukart spustelėkite darbalapio objektą ir nukopijuokite įklijuokite kodą į kodo langą.
Įdėję kodą į darbaknygę, turite jį išsaugoti .XLSM arba .XLS plėtiniu.
Kaip paleisti makrokomandą
Nukopijavę kodą VB redaktoriuje, atlikite makrokomandos veiksmus:
- Eikite į skirtuką Kūrėjas.
- Spustelėkite „Makrokomandos“.
- Dialogo lange makrokomandos pasirinkite makrokomandą, kurią norite paleisti.
- Spustelėkite mygtuką Vykdyti.
Jei juostelėje nerandate skirtuko kūrėjas, perskaitykite šią mokymo programą, kad sužinotumėte, kaip ją gauti.
Susijusi pamoka: įvairūs būdai paleisti makrokomandą „Excel“.
Jei kodas įklijuotas darbalapio kodo lange, jums nereikia jaudintis dėl kodo paleidimo. Jis bus paleistas automatiškai, kai bus atliktas nurodytas veiksmas.
Dabar pažvelkime į naudingus makro pavyzdžius, kurie gali padėti automatizuoti darbą ir sutaupyti laiko.
Pastaba: rasite daug apostrofo („) atvejų, po kurių eina eilutė ar dvi. Tai komentarai, kurie ignoruojami vykdant kodą ir pateikiami kaip pastabos sau/skaitytojui.
Jei straipsnyje ar kode rasite kokių nors klaidų, būkite nuostabūs ir praneškite man.
„Excel“ makrokomandų pavyzdžiai
Šiame straipsnyje pateikiami makro pavyzdžiai:
Rodyti visus darbalapius vienu metu
Jei dirbate darbaknygėje, kurioje yra keli paslėpti lapai, šiuos lapus turite slėpti po vieną. Tai gali užtrukti, jei yra daug paslėptų lapų.
Čia yra kodas, kuris parodys visus darbalapio darbalapius.
„Šis kodas parodys visus darbaknygės lapus Sub UnhideAllWoksheets () Dim ws kaip kiekvieno„ ActiveWorkbook “ws darbalapis. Darbo lapai ws.Visible = xlSheetVisible Next ws End Sub
Aukščiau pateiktas kodas naudoja VBA kilpą (kiekvienam), kad būtų galima peržiūrėti kiekvieną darbo knygos darbalapį. Tada ji pakeičia matomą darbalapio savybę į matomą.
Čia yra išsami pamoka, kaip naudoti įvairius metodus, norint atskleisti „Excel“ lapus.
Slėpti visus darbalapius, išskyrus aktyvų lapą
Jei dirbate prie ataskaitos ar prietaisų skydelio ir norite paslėpti visą darbalapį, išskyrus tą, kuriame yra ataskaita/prietaisų skydelis, galite naudoti šį makrokomandos kodą.
„Ši makrokomanda paslėps visą darbalapį, išskyrus aktyvų lapą Sub HideAllExceptActiveSheet () Dim ws kaip darbalapis kiekvienam„ wW “šioje„ Darbo knygoje “.
Rūšiuoti darbalapius abėcėlės tvarka naudojant VBA
Jei turite darbaknygę su daugybe darbalapių ir norite rūšiuoti juos abėcėlės tvarka, šis makro kodas gali būti tikrai naudingas. Tai gali atsitikti, jei lapų pavadinimus nurodote kaip metus arba darbuotojų vardus ar produktų pavadinimus.
„Šis kodas surūšiuos darbalapius abėcėlės tvarka Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets. Jei skaičiuoklės (j). Pavadinimas <lakštai (i). Pavadinimas, tada skaičiuoklės (j). Perkelkite anksčiau: = lakštai (i) pabaiga, jei kita j kita i programa.ScreenUpdating = Tikroji pabaiga
Apsaugokite visus darbalapius vienu metu
Jei darbaknygėje yra daug darbalapių ir norite apsaugoti visus lapus, galite naudoti šį makro kodą.
Tai leidžia nurodyti kodo slaptažodį. Šio slaptažodžio jums reikės norint panaikinti darbalapio apsaugą.
"Šis kodas vienu metu apsaugos visus lapus Sub ProtectAllSheets () Dim ws kaip darbalapis Dim Dimension As String password =" Test123 "" pakeiskite Test123 norimu slaptažodžiu Kiekvienam ws darbalapiuose ws. Apsaugoti slaptažodį: = slaptažodis Pabaiga Sub
Vienu metu panaikinkite visų darbalapių apsaugą
Jei kai kurie arba visi darbalapiai yra apsaugoti, galite tik šiek tiek pakeisti kodą, naudojamą lapams apsaugoti, kad jį apsaugotumėte.
'Šis kodas vienu metu apsaugos visus lapus Sub ProtectAllSheets () Dim ws kaip darbalapis Dim Dimension as String password = "Test123" "pakeiskite Test123 norimu slaptažodžiu. Pabaiga Sub
Atminkite, kad slaptažodis turi būti tas pats, kuris buvo naudojamas darbo lapams užrakinti. Jei ne, pamatysite klaidą.
Rodyti visas eilutes ir stulpelius
Šis makrokomandos kodas parodys visas paslėptas eilutes ir stulpelius.
Tai gali būti tikrai naudinga, jei gausite failą iš kito asmens ir norite būti tikri, kad nėra paslėptų eilučių/stulpelių.
"Šis kodas parodys visas eilutes ir stulpelius darbalapyje Sub UnhideRowsColumns () Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Panaikinti visus sujungtus langelius
Įprasta sujungti ląsteles, kad jos taptų viena. Nors tai veikia, kai ląstelės bus sujungtos, negalėsite rūšiuoti duomenų.
Jei dirbate su darbalapiu su sujungtomis ląstelėmis, naudokite žemiau esantį kodą, kad vienu metu atlaisvintumėte visas sujungtas ląsteles.
„Šis kodas sujungs visas sujungtas ląsteles Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Atminkite, kad vietoj suliejimo ir centro rekomenduoju naudoti parinktį „Centre Across Selection“.
Išsaugokite darbaknygę pavadinimu „TimeStamp“
Daug laiko gali tekti sukurti savo darbo versijas. Tai labai naudinga ilgiems projektams, kai laikui bėgant dirbate su failu.
Gera praktika yra išsaugoti failą naudojant laiko žymes.
Naudodami laiko žymes galėsite grįžti į tam tikrą failą, kad pamatytumėte, kokie pakeitimai buvo atlikti ar kokie duomenys buvo naudojami.
Čia yra kodas, kuris automatiškai įrašys darbaknygę į nurodytą aplanką ir pridės laiko žymę, kai ji bus išsaugota.
'Šis kodas išsaugos failą su laiko žyma jo pavadinime Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Turite nurodyti aplanko vietą ir failo pavadinimą.
Pirmiau pateiktame kode „C: UsersUsernameDesktop yra mano naudojama aplanko vieta. Turite nurodyti aplanko vietą, kurioje norite išsaugoti failą. Be to, kaip failo pavadinimo priešdėlį naudojau bendrinį pavadinimą „WorkbookName“. Galite nurodyti kažką, susijusio su jūsų projektu ar įmone.
Išsaugokite kiekvieną darbalapį kaip atskirą PDF
Jei dirbate su skirtingų metų, padalinių ar produktų duomenimis, gali tekti išsaugoti skirtingus darbalapius kaip PDF failus.
Nors tai gali būti daug laiko reikalaujantis procesas, jei tai atliekama rankiniu būdu, VBA tikrai gali jį pagreitinti.
Čia yra VBA kodas, kuris kiekvieną darbalapį išsaugos kaip atskirą PDF.
„Šis kodas išsaugos kiekvieną blogo lapą kaip atskirą PDF antraštę SaveWorkshetAsPDF () Dim ws kaip darbalapis kiekvienam ws darbalapiuose ws.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ws.Name &" .pdf "Kitas ws pabaiga
Pirmiau pateiktame kode nurodiau aplanko vietos, kurioje noriu išsaugoti PDF failus, adresą. Be to, kiekvienas PDF bus toks pat kaip darbalapio pavadinimas. Turėsite pakeisti šio aplanko vietą (nebent jūsų vardas taip pat yra „Sumit“ ir išsaugote jį bandomame aplanke darbalaukyje).
Atminkite, kad šis kodas tinka tik darbalapiams (o ne diagramų lapams).
Išsaugokite kiekvieną darbalapį kaip atskirą PDF
Čia yra kodas, kuris išsaugos visą jūsų darbaknygę kaip PDF į nurodytą aplanką.
'Šis kodas išsaugos visą darbaknygę kaip PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Norėdami naudoti šį kodą, turėsite pakeisti aplanko vietą.
Konvertuokite visas formules į vertybes
Naudokite šį kodą, kai turite darbalapį, kuriame yra daug formulių, ir norite šias formules paversti reikšmėmis.
"Šis kodas visas formules pavers reikšmėmis Sub ConvertToValues () su ActiveSheet.UsedRange .Value = .Value End With End Sub
Šis kodas automatiškai nustato naudojamus langelius ir paverčia juos vertėmis.
Apsaugokite/užrakinkite ląsteles formulėmis
Galbūt norėsite užrakinti langelius formulėmis, kai turite daug skaičiavimų ir nenorite jų netyčia ištrinti ar pakeisti.
Čia yra kodas, kuris užrakins visas ląsteles, kuriose yra formulės, o visos kitos ląstelės nėra užrakintos.
"Šis makrokomandos kodas užrakins visas ląsteles formulėmis Sub LockCellsWithFormulas () su ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = Tiesa.
Susijusi pamoka: Kaip užrakinti langelius „Excel“.
Apsaugokite visus darbaknygės darbalapius
Naudokite žemiau esantį kodą, kad vienu metu apsaugotumėte visus darbaknygės darbalapius.
„Šis kodas apsaugo visus darbo knygos„ Sub ProtectAllSheets () “lapus () Dim ws kaip darbalapį kiekvienam ws darbalapyje ws. Protect next ws End Sub
Šis kodas po vieną eis per visus darbalapius ir jį apsaugos.
Jei norite panaikinti visų darbalapių apsaugą, naudokite „ws“. Atsisakyti apsaugos, o ne „ws“. Apsaugokite kodą.
Įterpkite eilutę po kiekvienos kitos pasirinktos eilutės
Naudokite šį kodą, kai norite įterpti tuščią eilutę po kiekvienos pasirinkto diapazono eilutės.
„Šis kodas įterps eilutę po kiekvienos pasirinktos eilutės Sub InsertAlternateRows () Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Įdėkite „ActiveCell.Offset“ (2, 0). Pasirinkite „Next i End Sub“
Panašiai galite modifikuoti šį kodą ir įterpti tuščią stulpelį po kiekvieno pasirinkto diapazono stulpelio.
Automatiškai įterpti datą ir laiko žymę į gretimą langelį
Laiko žymą naudojate norėdami stebėti veiklą.
Pvz., Galbūt norėsite stebėti veiklą, pvz., Kada buvo patirtos tam tikros išlaidos, kada buvo sukurta pardavimo sąskaita faktūra, kada buvo įvestas langelis, kada paskutinį kartą buvo atnaujinta ataskaita ir pan.
Naudokite šį kodą, norėdami įterpti datą ir laiko žymę į gretimą langelį, kai įvedamas įrašas arba redaguojamas esamas turinys.
'Šis kodas įterps laiko žymę į gretimą langelį Privatus antrinis darbalapis_Change („ByVal Target As Range“) „GoTo Handler“ klaidoje, jei Target.Column = 1 And Target.Value "" Tada Application.EnableEvents = False Target.Offset (0, 1) = Formatas (dabar (), "dd-mm-yyyy hh: mm: ss") Application.EnableEvents = Tikroji pabaiga, jei tvarkytojas: pabaiga
Atminkite, kad šį kodą turite įterpti į darbalapio kodo langą (o ne į modulio kodo langą, kaip tai darėme kituose „Excel“ makrokomandų pavyzdžiuose). Norėdami tai padaryti, VB redaktoriuje dukart spustelėkite lapo pavadinimą, kuriame norite šios funkcijos. Tada nukopijuokite ir įklijuokite šį kodą to lapo kodo lange.
Be to, šis kodas veikia, kai duomenys įvedami A stulpelyje (atkreipkite dėmesį, kad kodas turi eilutę Target.Column = 1). Atitinkamai galite tai pakeisti.
Pasirinkime pažymėkite alternatyvias eilutes
Pažymėjus alternatyvias eilutes, jūsų duomenų skaitymas gali labai padidėti. Tai gali būti naudinga, kai reikia išspausdinti ir peržiūrėti duomenis.
Čia yra kodas, kuris akimirksniu paryškins alternatyvias pasirinkimo eilutes.
„Šis kodas paryškintų alternatyvias eilutes pasirinkus Sub HighlightAlternateRows () Dim Myrange As Range Dim Dimrow As Range Set Myrange = Pasirinkimas kiekvienai Myrow Myrange. Eilučių If Myrow.Row Mod 2 = 1 Tada Myrow.Interior.Color = vbCyan End Jei Kitas Myrow End Sub
Atminkite, kad kode nurodžiau spalvą kaip „vbCyan“. Taip pat galite nurodyti kitas spalvas (pvz., „VbRed“, „vbGreen“, „vbBlue“).
Pažymėkite langelius klaidingai parašytais žodžiais
„Excel“ neturi rašybos tikrinimo, kaip ir „Word“ ar „PowerPoint“. Nors rašybos tikrinimą galite atlikti paspausdami klavišą F7, vizualinio užuominos nėra, kai yra rašybos klaida.
Naudokite šį kodą, kad akimirksniu paryškintumėte visas langelius, kuriuose yra rašybos klaida.
„Šis kodas paryškins langelius, kuriuose yra klaidingai parašyti žodžiai Sub HighlightMisspelledCells () Dim cl As Range for each cl in ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text), tada cl.Interior.Color = vbRed End End Next cl Pabaiga Sub
Atminkite, kad pažymėti langeliai yra tie, kuriuose yra tekstas, kurį „Excel“ laiko rašybos klaida. Daugeliu atvejų jis taip pat išryškintų nesuprantamus pavadinimus ar prekės ženklo terminus.
Atnaujinti visas „Pivot“ lenteles darbaknygėje
Jei darbaknygėje yra daugiau nei viena „Pivot“ lentelė, galite naudoti šį kodą, kad iš karto atnaujintumėte visas šias „Pivot“ lenteles.
„Šis kodas atnaujins visą„ Pivot “lentelę darbaknygėje Sub RefreshAllPivotTables () Dim PT kaip„ PivotTable “kiekvienam„ ActiveSheet “PT.„ PivotTables “PT.RefreshTable Kitas PT pabaigos poskyris
Daugiau apie „Pivot“ lentelių atnaujinimą galite perskaityti čia.
Pakeiskite pasirinktų langelių raidžių raidę į didžiąsias raides
Nors „Excel“ turi formules teksto raidžių keitimui, tai leidžia tai padaryti kitame langelių rinkinyje.
Naudokite šį kodą, kad akimirksniu pakeistumėte pasirinkto teksto didžiosios ir mažosios raidės.
"Šis kodas pakeis pasirinkimą į didžiąsias raides Sub ChangeCase () Dim Rng kaip diapazonas kiekvienam pasirinktam Rng. Ląstelės, jei Rng.HasFormula = False then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Atkreipkite dėmesį, kad šiuo atveju aš naudojau UCase, kad teksto raidė būtų viršutinė. „LCase“ galite naudoti mažosioms raidėms.
Pažymėkite visus langelius su komentarais
Naudokite žemiau esantį kodą, kad paryškintumėte visas ląsteles, kuriose yra komentarų.
"Šis kodas paryškins langelius, kuriuose yra komentarų" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Šiuo atveju aš naudoju vbBlue, kad ląstelėms suteikčiau mėlyną spalvą. Jei norite, galite tai pakeisti kitomis spalvomis.
Paryškinkite tuščias langelius naudodami VBA
Nors tuščią langelį galite paryškinti su sąlyginiu formatu arba naudodami dialogo langą Eiti į specialųjį, jei tai turite padaryti gana dažnai, geriau naudoti makrokomandą.
Sukūrę šią makrokomandą galite turėti ją sparčiosios prieigos įrankių juostoje arba išsaugoti asmeninėje makrokomandos darbaknygėje.
Čia yra VBA makrokomandos kodas:
„Šis kodas paryškins visas tuščias duomenų rinkinio langelius Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks). Interior.Color = vbRed End Sub Sub
Šiame kode nurodiau tuščias langelius, kurie turi būti paryškinti raudona spalva. Galite pasirinkti kitas spalvas, tokias kaip mėlyna, geltona, žalsvai mėlyna ir kt.
Kaip rūšiuoti duomenis pagal vieną stulpelį
Norėdami rūšiuoti duomenis pagal nurodytą stulpelį, galite naudoti žemiau esantį kodą.
Sub SortDataHeader () Diapazonas („DataRange“). Rūšiavimo raktas1: = Diapazonas („A1“), Tvarka1: = xlAuncending, Header: = xl
Atminkite, kad aš sukūriau pavadintą diapazoną pavadinimu „DataRange“ ir naudoju jį vietoj ląstelių nuorodų.
Čia taip pat naudojami trys pagrindiniai parametrai:
- 1 raktas - čia norite rūšiuoti duomenų rinkinį. Pirmiau pateiktame kodo pavyzdyje duomenys bus rūšiuojami pagal A stulpelio reikšmes.
- Užsakymas- čia reikia nurodyti, ar norite rūšiuoti duomenis didėjančia, ar mažėjančia tvarka.
- Antraštė - čia turite nurodyti, ar jūsų duomenys turi antraštes, ar ne.
Skaitykite daugiau apie tai, kaip rūšiuoti duomenis „Excel“ naudojant VBA.
Kaip rūšiuoti duomenis pagal kelis stulpelius
Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau:
Žemiau yra kodas, kuris surūšiuos duomenis pagal kelis stulpelius:
Sub SortMultipleColumns () su ActiveSheet.Sort .SortFields.Add raktas: = Diapazonas ("A1"), Tvarka: = xlAugantis. : C13 "). Antraštė = xlTaip. Taikyti pabaigą su pabaiga
Atminkite, kad čia aš nurodžiau pirmiausia rūšiuoti pagal A stulpelį, o paskui pagal B stulpelį.
Išvestis būtų tokia, kaip parodyta žemiau:
Kaip iš „Excel“ eilutės gauti tik skaitinę dalį
Jei norite iš eilutės išgauti tik skaitinę dalį arba tik tekstinę dalį, galite sukurti pasirinktinę funkciją VBA.
Tada galite naudoti šią VBA funkciją darbalapyje (kaip ir įprastos „Excel“ funkcijos) ir ji iš eilutės ištrauks tik skaitinę arba tekstinę dalį.
Kažkas, kaip parodyta žemiau:
Žemiau yra VBA kodas, kuris sukurs funkciją išgauti skaitinę dalį iš eilutės:
„Šis VBA kodas sukurs funkciją, kad gautų skaitinę dalį iš eilutės. ) Tada rezultatas = Rezultatas ir vidurys (CellRef, i, 1) Kitas i GetNumeric = Rezultato pabaigos funkcija
Jums reikia įdėti kodą į modulį, o tada darbalapyje galite naudoti funkciją = GetNumeric.
Ši funkcija turės tik vieną argumentą, tai yra langelio, iš kurio norite gauti skaitinę dalį, langelio nuoroda.
Panašiai žemiau yra funkcija, kuri suteiks jums tik teksto dalį iš „Excel“ eilutės:
„Šis VBA kodas sukurs funkciją, leidžiančią gauti teksto dalį iš eilutės 1))) Tada rezultatas = Rezultatas ir vidurys (CellRef, i, 1) Kitas i GetText = Rezultato pabaigos funkcija
Taigi tai yra keletas naudingų „Excel“ makrokomandų kodų, kuriuos galite naudoti kasdieniniame darbe, kad automatizuotumėte užduotis ir taptumėte daug produktyvesni.
