Exceli makro kasutamine võib kiirendada tööd ja säästa palju aega.
Üks viis VBA koodi saamiseks on makro salvestamine ja selle loodud koodi võtmine. See makrosalvesti kood on aga sageli täis koodi, mida pole tegelikult vaja. Samuti on makrosalvesti teatud piirangud.
Seega tasub koguda kasulikke VBA makrokoode, mis võivad olla teie taskus ja seda vajadusel kasutada.
Kuigi Exceli VBA makrokoodi kirjutamine võib esialgu veidi aega võtta, saate pärast selle valmimist selle viitena kättesaadavaks teha ja kasutada seda alati, kui seda vajate.
Selles massilises artiklis loetlen mõned kasulikud Exceli makro näited, mida ma sageli vajan, ja hoian oma privaatses hoidlas peidetud.
Jätkan selle õpetuse värskendamist rohkemate makro -näidetega. Kui arvate, et midagi peaks loendis olema, jätke lihtsalt kommentaar.
Saate selle lehe edaspidiseks kasutamiseks järjehoidjatesse lisada.
Nüüd, enne kui asun makronäite juurde ja annan teile VBA -koodi, näitan kõigepealt, kuidas neid näidiskoode kasutada.
Exceli makro näidete koodi kasutamine
Siin on sammud, mida peate järgima, et kasutada mis tahes näite koodi:
- Avage töövihik, milles soovite makro kasutada.
- Hoidke all klahvi ALT ja vajutage klahvi F11. See avab VB redaktori.
- Paremklõpsake projektiuurija mis tahes objekti.
- Avage Lisa -> Moodul.
- Kopeerige ja kleepige kood mooduli koodi aknasse.
Kui näide ütleb, et peate koodi töölehe koodi aknasse kleepima, topeltklõpsake töölehe objekti ja kopeerige ja kleepige kood koodiaknasse.
Kui olete koodi töövihikusse sisestanud, peate selle salvestama laiendiga .XLSM või .XLS.
Kuidas makro käivitada
Kui olete koodi VB redaktoris kopeerinud, toimige järgmiselt makro käivitamiseks.
- Minge vahekaardile Arendaja.
- Klõpsake nuppu Makrod.
- Valige dialoogiboksis Makro makro, mida soovite käivitada.
- Klõpsake nuppu Käivita.
Kui te ei leia lindilt arendaja vahekaarti, lugege seda õpetust selle saamiseks.
Seotud õpetus: erinevad viisid makro käivitamiseks Excelis.
Kui kood on töölehe koodi aknasse kleebitud, ei pea te koodi käitamise pärast muretsema. See käivitub automaatselt, kui määratud toiming toimub.
Nüüd vaatame kasulikke makronäiteid, mis aitavad teil tööd automatiseerida ja aega säästa.
Märkus. Leiate palju apostroofi (') juhtumeid, millele järgneb rida või kaks. Need on kommentaarid, mida koodi käitamise ajal eiratakse ja mis paigutatakse märkmetena iseendale/lugejale.
Kui leiate artiklist või koodist vigu, olge vinge ja andke mulle sellest teada.
Exceli makro näited
Selles artiklis käsitletakse järgmisi makro näiteid:
Näita korraga kõiki töölehti
Kui töötate töövihikus, millel on mitu peidetud lehte, peate need lehed ükshaaval peitma. See võib võtta aega, kui peidetud lehti on palju.
Siin on kood, mis avab kõik töövihiku töölehed.
"See kood näitab kõik töövihiku lehed alamkataloogis UnhideAllWoksheets () Dim ws töölehena iga ActiveStyri töövihiku töölaua jaoks. Töölehed ws.Visible = xlSheetVisible Next ws End Sub
Ülaltoodud kood kasutab töövihiku iga töölehe läbimiseks VBA silmust (igaühe jaoks). Seejärel muudab see töölehe nähtava atribuudi nähtavaks.
Siin on üksikasjalik õpetus, kuidas kasutada Exceli lehtede peitmiseks erinevaid meetodeid.
Peida kõik töölehed, välja arvatud aktiivne leht
Kui töötate aruande või armatuurlauaga ja soovite peita kogu töölehe, välja arvatud selle, millel on aruanne/armatuurlaud, saate seda makrokoodi kasutada.
See makro peidab kõik töölehed, välja arvatud aktiivne leht Sub HideAllExceptActiveSheet () Dim ws kui tööleht iga töö jaoks käesolevas töövihikus. Töölehed Kui ws.Name ActiveSheet.Name Siis ws.Visible = xlSheetHidden Next ws End Sub
Sorteerige töölehed tähestikuliselt VBA abil
Kui teil on palju töölehti sisaldav töövihik ja soovite neid tähestikulises järjekorras sorteerida, võib see makrokood tõesti kasuks tulla. See võib juhtuda, kui teil on lehtede nimed aastatena või töötajate nimed või tootenimed.
'See kood sorteerib töölehed tähestikulises järjekorras Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 j = i + 1 Kui Arvutustabelid (j). Nimi <Arvutustabelid (i). Nimi Siis Lehed (j). Liigu enne: = Lehed (i) Lõpeta, kui järgmine j Järgmine i Rakendus. ScreenUpdating = Tõeline lõpp Alam
Kaitske kõiki töölehti korraga
Kui teil on töövihikus palju töölehti ja soovite kõiki lehti kaitsta, saate seda makrokoodi kasutada.
See võimaldab määrata koodi parooli. Seda parooli vajate töölehe kaitse tühistamiseks.
'See kood kaitseb kõiki lehti korraga. End Sub
Kõigi töölehtede kaitse eemaldamine korraga
Kui teil on mõni või kõik töölehed kaitstud, saate lehtede kaitsmiseks kasutatavat koodi veidi muuta, et seda kaitsta.
'See kood kaitseb kõiki lehti korraga. End Sub
Pange tähele, et parool peab olema sama, mida kasutati töölehtede lukustamiseks. Kui see pole nii, näete viga.
Kuva kõik read ja veerud
See makrokood avab kõik peidetud read ja veerud.
See võib olla tõesti kasulik, kui saate faili kelleltki teiselt ja soovite olla kindel, et seal pole peidetud ridu/veerge.
'See kood avab kõik töölehe alammärgid UnhideRowsColumns () veerud .EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Tühista kõik ühendatud lahtrid
Lahtrite ühendamine selle loomiseks on tavaline tava. Kuigi see töötab, ei saa lahtrite ühendamisel andmeid sortida.
Kui töötate ühendatud lahtritega töölehega, kasutage alltoodud koodi abil kõik ühendatud lahtrid korraga.
"See kood ühendab kõik ühendatud lahtrid Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Pange tähele, et ühendamise ja tsentreerimise asemel soovitan kasutada valikut Kesk keskel.
Salvestage töövihik oma ajatempliga
Palju aega võib tekkida vajadus luua oma tööst versioonid. Need on üsna kasulikud pikkade projektide puhul, kus töötate aja jooksul failiga.
Hea tava on faili salvestamine ajatemplitega.
Ajatemplite kasutamine võimaldab teil naasta teatud faili, et näha, milliseid muudatusi tehti või milliseid andmeid kasutati.
Siin on kood, mis salvestab töövihiku automaatselt määratud kausta ja lisab selle salvestamisel alati ajatempli.
'See kood salvestab faili nimega ajatempliga Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp End Sub
Peate määrama kausta asukoha ja faili nime.
Ülaltoodud koodis on „C: UsersUsernameDesktop minu kasutatud kausta asukoht. Peate määrama kausta asukoha, kuhu soovite faili salvestada. Samuti olen failinime eesliitena kasutanud üldnime „WorkbookName”. Saate määrata midagi, mis on seotud teie projekti või ettevõttega.
Salvestage iga tööleht eraldi PDF -failina
Kui töötate erinevate aastate või osakondade või toodete andmetega, võib tekkida vajadus salvestada erinevad töölehed PDF -failidena.
Kuigi see võib olla aeganõudev protsess, kui seda teha käsitsi, võib VBA seda tõesti kiirendada.
Siin on VBA -kood, mis salvestab iga töölehe eraldi PDF -failina.
"See kood salvestab iga halvenduslehe eraldi PDF -alammenüüks SaveWorkshetAsPDF () Dim ws töölehena iga ws töölehel ws.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ws.Name &" .pdf "Järgmine ws End Sub
Ülaltoodud koodis olen määranud selle kausta asukoha aadressi, kuhu soovin PDF -failid salvestada. Samuti saab iga PDF sama nime, mis töölehel. Peate seda kausta asukohta muutma (kui teie nimi pole samuti Sumit ja salvestate selle töölaua testkausta).
Pange tähele, et see kood töötab ainult töölehtede (mitte diagrammilehtede) puhul.
Salvestage iga tööleht eraldi PDF -failina
Siin on kood, mis salvestab kogu teie töövihiku PDF -failina määratud kausta.
"See kood salvestab kogu töövihiku PDF -alammenüüna SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF," C: UsersSumitDesktopTest "& ThisWorkbook.Name &" .pdf "End Sub
Selle koodi kasutamiseks peate muutma kausta asukohta.
Teisendage kõik valemid väärtusteks
Kasutage seda koodi, kui teil on tööleht, mis sisaldab palju valemeid ja soovite need valemid väärtusteks teisendada.
"See kood teisendab kõik valemid väärtusteks Sub ConvertToValues () ActiveSheet.UsedRange .Value = .Value End End End
See kood tuvastab automaatselt kasutatavad lahtrid ja teisendab need väärtusteks.
Kaitske/lukustage lahtreid valemitega
Võimalik, et soovite lahtrid valemitega lukustada, kui teil on palju arvutusi ja te ei soovi seda kogemata kustutada ega muuta.
Siin on kood, mis lukustab kõik lahtrid, millel on valemid, samas kui kõik ülejäänud lahtrid pole lukustatud.
See makrokood lukustab kõik lahtrid valemitega Sub LockCellsWithFormulas () ActiveSheetiga .Kaitse kaitset .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True .Protect AllowDeletedRows: = Tõeline lõpp lõpuga
Seotud õpetus: Kuidas lahtreid Excelis lukustada.
Kaitske kõiki töövihiku töölehti
Kasutage allolevat koodi, et kaitsta kõiki töövihiku töölehti korraga.
"See kood kaitseb kõiki lehti töövihikus Sub ProtectAllSheets () Dim ws töölehena iga töölehe ws jaoks. Kaitse Järgmine ws End Sub
See kood läbib kõik töölehed ükshaaval ja kaitseb seda.
Kui soovite kõigi töölehtede kaitse tühistada, kasutage ws. Kaitske ws asemel. Kaitske koodis.
Sisestage rida valiku iga teise rea järel
Kasutage seda koodi, kui soovite tühja rea sisestada valitud vahemiku iga rea järel.
'See kood lisab rea valiku iga rea järel Sub InsertAlternateRows () Dim rng vahemikuna Dim CountRow täisarvuna Dim i täisarvuna Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Sisestage ActiveCell.Offset (2, 0). Valige Next i End Sub
Samamoodi saate seda koodi muuta, et sisestada tühi veerg pärast valitud vahemiku iga veergu.
Sisestage kuupäev ja ajatempel automaatselt külgnevasse lahtrisse
Ajatempel on midagi, mida kasutate tegevuste jälgimiseks.
Näiteks võiksite jälgida selliseid tegevusi nagu millal tekkis konkreetne kulu, mis kell müügiarve koostati, millal sisestati lahtrisse, millal viimati aruannet värskendati jne.
Selle koodi abil saate sisestada või olemasolevat sisu redigeerida külgnevasse lahtrisse kuupäeva ja kellaaja templi.
'See kood lisab ajatempli kõrvalolevasse lahtrisse Privaatne alamleht Töömuutmine_muutmine (ByVal -sihtmärk vahemikuna) veal GoTo Handler If Target.Column = 1 And Target.Value "" Siis Application.EnableEvents = False Target.Offset (0, 1) = Vorming (nüüd (), "pp-kk-aaaa hh: mm: ss") Application.EnableEvents = Tõeline lõpp, kui käitleja: lõppalam
Pange tähele, et peate sisestama selle koodi töölehe koodi aknasse (mitte mooduli koodi aknasse, nagu oleme teinud teistes Exceli makro näidetes seni). Selleks topeltklõpsake VB redaktoris lehe nime, millel soovite seda funktsiooni kasutada. Seejärel kopeerige ja kleepige see kood selle lehe koodiaknasse.
See kood töötab ka siis, kui veerus A on andmete sisestamine (pange tähele, et koodil on rida Target.Column = 1). Saate seda vastavalt muuta.
Tõstke valikust esile alternatiivsed read
Alternatiivsete ridade esiletõstmine võib teie andmete loetavust tohutult suurendada. See võib olla kasulik, kui peate välja printima ja andmed läbi vaatama.
Siin on kood, mis tõstab koheselt esile valiku alternatiivsed read.
'See kood tõstaks esile alternatiivsed read valikul Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Valik iga Myrow jaoks Myrange'is. Rivid If Myrow.Row Mod 2 = 1 Siis Myrow.Interior.Color = vbCyan End Kui järgmine Myrow End Sub
Pange tähele, et olen koodi määranud vbCyan värviks. Saate määrata ka teisi värve (nt vbRed, vbGreen, vbBlue).
Tõstke lahtrid esile valesti kirjutatud sõnadega
Excelil pole õigekirjakontrolli nagu Wordis või PowerPointis. Kuigi saate õigekirjakontrolli käivitada, vajutades klahvi F7, pole õigekirjavea korral visuaalset vihjet.
Kasutage seda koodi, et koheselt esile tõsta kõik lahtrid, milles on õigekirjaviga.
'See kood tõstab esile lahtrid, millel on valesti kirjutatud sõnad Sub HighlightMisspelledCells () Dim cl As vahemik iga kl ActiveSheet.UsedRange If Not Application.CheckSpelling (word: = cl.Text) Seejärel cl.Interior.Color = vbPunane lõpp, kui järgmine cl End Sub
Pange tähele, et esiletõstetud lahtrid on tekstid, mida Excel peab õigekirjaveaks. Paljudel juhtudel tõstaks see esile ka nimesid või bränditermineid, millest ta aru ei saa.
Värskendage kõiki töövihiku liigendtabeleid
Kui töövihikus on mitu liigendtabelit, saate selle koodi abil värskendada kõiki neid liigendtabeleid korraga.
"See kood värskendab kogu Pivot -tabelit töövihiku alamvärskenduses RefreshAllPivotTables () Dim PT kui PivotTable -liigendtabelit ActiveSheet iga PT jaoks.
Pivot -tabelite värskendamise kohta saate lugeda siit.
Muutke valitud lahtrite tähekiri suurtähtedeks
Kuigi Excelil on valemid teksti tähestiku muutmiseks, sunnib see seda tegema mõnes teises lahtrikomplektis.
Kasutage seda koodi valitud teksti tekstitähtede kohest muutmiseks.
"See kood muudab valiku suurustäheks Sub ChangeCase () Dim Rng kui vahemik iga valiku Rng jaoks. Lahtrid Kui Rng.HasFormula = False Then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Pange tähele, et sel juhul olen kasutanud UCase'i, et muuta tekstitarbed ülemiseks. Väikeste tähtede jaoks saate kasutada LCase'i.
Tõstke kommentaaridega esile kõik lahtrid
Kasutage allolevat koodi, et tõsta esile kõik lahtrid, millel on kommentaare.
"See kood tõstab esile lahtrid, millel on kommentaare" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Sel juhul olen kasutanud vbBlue'i, et anda rakkudele sinine värv. Soovi korral saate selle teise värvi muuta.
Tõstke VBA abil esile tühjad lahtrid
Kuigi tühja lahtri saate esile tõsta tingimusliku vormindusega või dialoogiboksi Mine spetsiaalsesse juurde, on parem kasutada makro, kui peate seda tegema üsna sageli.
Kui olete selle makro loonud, saate selle kasutada kiire juurdepääsu tööriistaribal või salvestada oma isiklikku makro töövihikusse.
Siin on VBA makrokood:
See kood tõstab esile kõik andmestiku tühjad lahtrid Sub HighlightBlankCells () Dim Dataset kui Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks). Interior.Color = vbRed End Sub
Selles koodis olen määranud tühjad lahtrid, mis tuleb punase värviga esile tõsta. Saate valida muid värve, näiteks sinine, kollane, tsüaan jne.
Kuidas sortida andmeid ühe veeru järgi
Andmete sortimiseks määratud veeru järgi saate kasutada allolevat koodi.
Alam SortDataHeader () Vahemik ("DataRange"). Sortimisvõti1: = Vahemik ("A1"), Järjekord1: = xlTõusev, Päis: = xl
Pange tähele, et olen loonud nimega vahemiku nimega „DataRange” ja kasutanud seda lahtriviidete asemel.
Siin kasutatakse ka kolme põhiparameetrit:
- Key1 - see on see, mille alusel soovite andmekogumit sortida. Ülaltoodud näidiskoodis sorteeritakse andmed veeru A väärtuste alusel.
- Järjekord- Siin peate määrama, kas soovite sortida andmeid kasvavas või kahanevas järjekorras.
- Päis - Siin peate määrama, kas teie andmetel on päised või mitte.
Lugege lisateavet selle kohta, kuidas andmeid Excelis VBA abil sortida.
Andmete sortimine mitme veeru järgi
Oletame, et teil on allpool näidatud andmekogum:
Allpool on kood, mis sorteerib andmed mitme veeru alusel:
Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Vahemik ("A1"), Järjestus: = xlTõusuv .SortFields.Add Key: = Vahemik ("B1"), Järjestus: = xlTõusev .SetRange vahemik ("A1 : C13 "). Pealkiri = xlJah. Rakenda lõpp lõpuga Alam
Pange tähele, et siin olen määranud esmalt sortida veeru A ja seejärel veeru B alusel.
Väljund oleks midagi sellist, nagu allpool näidatud:
Kuidas saada Exceli stringist ainult numbriline osa
Kui soovite stringist välja võtta ainult numbrilise osa või ainult tekstiosa, saate VBA -s luua kohandatud funktsiooni.
Seejärel saate seda VBA funktsiooni töölehel kasutada (nagu tavalisi Exceli funktsioone) ja see eraldab stringist ainult numbrilise või tekstiosa.
Midagi, nagu allpool näidatud:
Allpool on VBA -kood, mis loob funktsiooni numbrilise osa eraldamiseks stringist:
See VBA -kood loob funktsiooni numbrilise osa saamiseks stringist Funktsioon GetNumeric (CellRef As String) Dim String ) Siis Tulemus = Tulemus ja keskmine (CellRef, i, 1) Järgmine i GetNumeric = Tulemuse lõppfunktsioon
Peate moodulis koodi sisestama ja seejärel saate töölehel kasutada funktsiooni = GetNumeric.
See funktsioon võtab ainult ühe argumendi, mis on selle lahtri viide, kust soovite numbrilise osa saada.
Samamoodi on allpool funktsioon, mis annab teile Exceli stringist ainult tekstiosa:
See VBA -kood loob funktsiooni tekstiosa saamiseks stringist Funktsioon GetText (CellRef As String) Dim StringLength As Integer StringLength = Len (CellRef) I = 1 kuni StringLength Kui ei 1))) Siis Tulemus = Tulemus ja keskmine (CellRef, i, 1) Järgmine i GetText = Tulemuse lõppfunktsioon
Nii et need on mõned kasulikud Exceli makrokoodid, mida saate oma igapäevases töös kasutada ülesannete automatiseerimiseks ja palju produktiivsemaks muutmiseks.
