24 hasznos Excel makrópélda VBA kezdőknek (használatra kész)

Az Excel makrók használatával felgyorsítható a munka, és sok időt takaríthat meg.

A VBA-kód beszerzésének egyik módja egy makró rögzítése és az általa generált kód lekérése.A makrórögzítő kódja azonban gyakran tele van olyan kóddal, amelyre nincs is igazán szükség.A makrórögzítőnek is vannak korlátai.

Érdemes tehát rendelkezni egy hasznos VBA makrókészlettel, amit a hátsó zsebedben tarthatsz és használhatsz, amikor szükséged van rá.

Bár kezdetben eltarthat egy ideig egy Excel VBA-makró kódolása, de miután végzett, használhatja referenciaként, és használhatja, amikor legközelebb szüksége lesz rá.

Ebben a hatalmas cikkben felsorolok néhány hasznos Excel-makrópéldát, amelyekre gyakran szükségem van, és amelyeket a privát tárolómban tartok.

Továbbra is frissítem ezt az oktatóanyagot további makrópéldákkal.Kérjük, írjon megjegyzést, ha úgy gondolja, hogy valaminek szerepelnie kell a listán.

Ezt az oldalt könyvjelzők közé helyezheti későbbi hivatkozás céljából.

Most, mielőtt belevágnék a makrópéldákba és megadnám a VBA-kódot, hadd mutassam meg először a példakód használatát.

Az Excel makró példájából származó kód használata

A példák bármelyikének kódjának használatához kövesse az alábbi lépéseket:

• Nyissa meg azt a munkafüzetet, amelyben használni kívánja a makrót.
• Tartsa lenyomva az ALT billentyűt, és nyomja meg az F11 billentyűt.Ez megnyílikVB szerkesztő.
• Kattintson a jobb gombbal bármelyik objektumra a Project Explorerben.
• Lépjen a Beszúrás -> Modulok menüpontra.
• Másolja ki és illessze be a kódot a modul kód ablakába.

Ha a példa szerint be kell illesztenie a kódot a munkalap kódablakába, kattintson duplán a munkalap objektumra, és másolja be a kódot a kódablakba.

Miután beszúrta a kódot a munkafüzetbe, el kell mentenie azt .XLSM vagy .XLS kiterjesztéssel.

Makró futtatása

A kód VB-szerkesztőben való másolása után a makró futtatásának lépései a következők:

• Lépjen a fejlesztői lapra.
• Kattintson a Makró elemre.

• A Makrók párbeszédpanelen válassza ki a futtatni kívánt makrót.
• Kattintson a Futtatás gombra.

Ha nem találja a fejlesztői lapot a szalagon, olvassa el ezt az oktatóanyagot, hogy megtudja, hogyan szerezheti meg.

Ha a kód be van illesztve a munkalap kódablakába, akkor nem kell aggódnia a kód futtatása miatt.A megadott művelet bekövetkezésekor automatikusan lefut.

Most pedig lássunk olyan hasznos makrópéldákat, amelyek segítségével automatizálhatja munkáját és időt takaríthat meg.

Megjegyzés: Sok aposztrófot (') talál, amelyet egy-két sor követ.Ezek olyan megjegyzések, amelyeket a program figyelmen kívül hagy a kód futtatásakor, és saját/olvasó megjegyzésként helyezi el.

Ha bármilyen hibát talál a cikkben vagy a kódban, kérjük, jelezze.

Excel makró példa

Ez a cikk a következő makrópéldákat ismerteti:

Az összes munkalap elrejtésének megjelenítése egyszerre

Ha több rejtett lapot tartalmazó munkafüzeten dolgozik, egyenként kell feloldania ezeket a lapokat.Ez eltarthat egy ideig, ha sok rejtett lap van.

Itt található a kód a munkafüzet összes lapjának felfedéséhez.

"Ez a kód felfedi az összes munkalapot a munkafüzetben Sub UnhideAllWoksheets() Dim ws Munkalapként minden ws-hez az ActiveWorkbookban. Munkalapok ws. Visible = xlSheetVissible Következő ws End Sub

A fenti kód egy VBA hurkot használ (Mindegyikhez) a munkafüzet egyes munkalapjainak végigjátszásához.Ezután a lap látható tulajdonságát láthatóvá változtatja.

Itt található egy részletes oktatóanyag a munkalapok elrejtésének felfedéséhez az Excelben különféle módszerekkel.

Az összes munkalap elrejtése az aktív munkalap kivételével

Ezt a makrókódot akkor használhatja, ha jelentéssel vagy irányítópulttal dolgozik, és el szeretné rejteni az összes lapot, kivéve a jelentést/irányítópultot.

'Ez a makró elrejti az összes munkalapot, kivéve az aktív lapot. Sub HideAllExceptActiveSheet() Dim ws Munkalapként minden ws-hez ebben a munkafüzetben.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

A munkalap rendezése ábécé sorrendben VBA használatával

Ez a makrókód akkor lehet hasznos, ha sok lapot tartalmazó munkafüzetünk van, és ezeket a lapokat betűrendbe szeretné rendezni.Ez akkor fordulhat elő, ha a lap neve évszámként vagy alkalmazott neveként vagy terméknévként szerepel.

'Ez a kód ábécé sorrendbe rendezi a munkalapokat Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To Sh Count If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Védje az összes munkalapot egyszerre

Ha sok lapja van a munkafüzetben, és az összes lapot védeni szeretné, használhatja ezt a makrókódot.

Lehetővé teszi a jelszó megadását kódban.Erre a jelszóra lesz szüksége a lap védelmének megszüntetéséhez.

'Ez a kód egyszerre védi az összes lapot Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'cserélje ki a Test123-at a kívánt jelszóra Minden ws In Worksheets esetén ws.Protect password:=password Next ws Vége Sub

Egyszerre szüntesse meg az összes munkalap védelmét

Ha a munkalapok egy részét vagy mindegyikét levédette, akkor a védelem csak a munkalapok védelmére használt kód kismértékű módosításával szüntethető meg.

'Ez a kód egyszerre védi az összes lapot Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'cserélje ki a Test123-at a kívánt jelszóra Minden ws In Worksheets esetén ws.Unprotect password:=password Next ws Vége Sub

Vegye figyelembe, hogy a jelszónak meg kell egyeznie a lap zárolásához használt jelszóval.Ha nem, akkor hibaüzenetet fog látni.

Az összes sor és oszlop elrejtésének felfedése

Ez a makrókód felfedi az összes rejtett sort és oszlopot.

Ez akkor lehet hasznos, ha valaki mástól veszi át a fájlt, és meg akar győződni arról, hogy nincsenek rejtett sorok/oszlopok.

'Ez a kód felfedi az összes sort és oszlopot a munkalap aljában. UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Az összes egyesített cella egyesítésének megszüntetése

A sejtek egyesítése általános gyakorlat.Amíg működik, a cellák egyesítésekor nem tudja rendezni az adatokat.

Ha egyesített cellákat tartalmazó munkalapot használ, használja az alábbi kódot az összes egyesített cella egyidejű feloldásához.

'Ez a kód feloldja az összes egyesített cellát Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Ne feledje, hogy azt javaslom, hogy a „Kijelölés középen” opciót használja az „Egyesítés és középre” opció helyett.

Mentse el a munkafüzetet időbélyeggel a névben

Sokszor előfordulhat, hogy működő verziót kell létrehoznia.Ezek nagyszerűek olyan hosszú távú projektekhez, amelyek idővel foglalkoznak a fájlokkal.

Jó gyakorlat az, ha a fájlt időbélyeggel mentjük.

Az időbélyeg használata lehetővé teszi, hogy visszatérjen egy fájlhoz, és megtekinthesse, milyen változtatásokat hajtottak végre, vagy milyen adatokat használtak fel.

Itt található az a kód, amely automatikusan elmenti a munkafüzetet a megadott mappába, és mentéskor időbélyeget ad hozzá.

'Ez a kód elmenti a fájlt időbélyeggel a nevében Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Dátum, "nn-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & időbélyeg End Sub

Meg kell adnia a mappa helyét és a fájl nevét.

A fenti kódban a „C:UsersUsernameDesktop az általam használt mappa helye.Meg kell adnia a mappa helyét, ahová a fájlt menteni szeretné.Ezenkívül a "WorkbookName" általános nevet használtam a fájlnév előtagjaként.Megadhat projektjéhez vagy cégéhez kapcsolódó tartalmat.

Mentse el az egyes munkalapokat külön PDF-ként

Ha különböző évekből, részlegekből vagy termékekből származó adatokat használ, előfordulhat, hogy különböző munkalapokat kell mentenie PDF-fájlként.

Noha ez manuálisan időigényes folyamat lehet, a VBA felgyorsítja a dolgokat.

Itt van egy VBA-kód, amely minden munkalapot külön PDF-ként ment.

'Ez a kód minden worsheet külön PDF-ként menti el. Sub SaveWorkshetAsPDF() Dim ws As Worksheet minden ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & "Sub End" Következő ws

A fenti kódban megadtam annak a mappának a címét, ahová a PDF-et menteni szeretném.Ezenkívül minden PDF-fájl ugyanazt a nevet kapja, mint a lap.Módosítania kell ezt a mappa helyét (kivéve, ha az Ön neve is Sumit, és elmentette egy tesztmappába az asztalon).

Vegye figyelembe, hogy ez a kód csak munkalapokhoz működik (diagramlapokhoz nem).

Mentse el az egyes munkalapokat külön PDF-ként

Itt található a kód, amellyel a teljes munkafüzetet PDF-ként mentheti a megadott mappába.

"Ez a kód a teljes munkafüzetet PDF formátumban menti Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

A kód használatához módosítania kell a mappa helyét.

Konvertálja az összes képletet értékké

Használja ezt a kódot, ha sok képletet tartalmazó munkalapja van, és ezeket a képleteket értékekké szeretné konvertálni.

'Ez a kód az összes képletet Sub ConvertToValues() értékké konvertálja az ActiveSheet.UsedRange .Value = .Value End With End Sub értékkel

Ez a kód automatikusan felismeri a használt cellát, és értékké alakítja.

Védje/zárolja a cellákat képletekkel

Ha sok számítása van, és nem szeretné véletlenül törölni vagy módosítani, érdemes lehet zárolni a cellákat képletekkel.

Ez a kód zárolja az összes cellát a képletekkel, és az összes többi cellát zárolva hagyja.

'Ez a makrókód zárolja az összes cellát Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows With End:=True

Kapcsolódó oktatóanyag: A cellák zárolása az Excelben.

Védje az összes lapot a munkafüzetben

Használja a következő kódot a munkafüzet összes lapjának egyidejű védelméhez.

"Ez a kód védi a munkafüzet összes lapot Sub ProtectAllSheets() Dim ws Munkalapként minden ws In Worksheets ws. Protect Next ws End Sub

Ez a kód egyenként végigmegy az összes lapon, és megvédi azokat.

Ha az összes lap védelmét szeretné feloldani, használja a ws.Unprotect parancsot a ws.Protect helyett a kódban.

Szúrjon be egy sort a kijelölés minden sora után

Használja ezt a kódot, ha a kiválasztott tartomány minden sora után egy üres sort szeretne beszúrni.

'Ez a kód beszúr egy sort a Sub InsertAlternateRows() kijelölés minden sora után 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. Insert ActiveCell.Offset(2, 0). Válassza a Következő i End Sub lehetőséget

Ismét módosíthatja ezt a kódot úgy, hogy a kiválasztott tartomány minden oszlopa után egy üres oszlopot szúrjon be.

Dátum és időbélyeg automatikus beszúrása a szomszédos cellákba

Az időbélyegek azok, amelyeket a tevékenység nyomon követéséhez használ.

Előfordulhat például, hogy nyomon szeretné követni az olyan tevékenységeket, mint egy adott költség felmerülésének időpontja, értékesítési számla létrehozása, adatbevitel cellába, jelentés utolsó frissítése stb.

Ezzel a kóddal dátum- és időbélyegzőt szúrhat be a szomszédos cellákba meglévő tartalom beírásakor vagy szerkesztésekor.

'Ez a kód beszúr egy időbélyeget a szomszédos cellába Private Sub Worksheet_Change(ByVal Target As Range) Hiba esetén GoTo Handler Ha Target.Column = 1 És Target.Value <> "" Akkor Application.EnableEvents = False Target.Offset(0, 1) = Formátum(Most(), "nn-hh-éééé óó:pp:pp") Application.EnableEvents = True End If Handler: End Sub

Ne feledje, hogy ezt a kódot a munkalap kódablakába kell beillesztenie (nem a modulkód ablakába, ahogyan eddig más Excel makrópéldákban tettük).Ehhez a VB-szerkesztőben kattintson duplán arra a lap nevére, amelyhez ezt a funkciót kívánja használni.Ezután másolja ki ezt a kódot, és illessze be az adott lap kódablakába.

Ez a kód akkor is működik, ha adatbevitel történik az A oszlopban (vegye figyelembe, hogy a kódnak Target.Column = 1 sora van).Ennek megfelelően módosíthatja.

Jelölje ki az alternatív sorokat a kijelölésben

A váltakozó sorok kiemelése nagymértékben javíthatja az adatok olvashatóságát.Ez akkor lehet hasznos, ha ki kell nyomtatnia, és böngésznie kell az adatok között.

Itt van egy kód, amely azonnal kiemeli a kijelölés alternatív sorait.

'Ez a kód alternatív sorokat emel ki a kijelölésben Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Every Myrow In Myrange.Rows Ha Myrow.Row Mod 2 = 1 then Myrow.Interior.Color = vbCyan End Ha Next Myrow End Sub

Ne feledje, hogy a színt vbCyan-ként adtam meg a kódban.Más színeket is megadhat (pl. vbRed, vbGreen, vbBlue).

Jelölje ki a hibásan írt cellákat

Az Excelben nincs helyesírás-ellenőrzés Wordben vagy PowerPointban.Bár a helyesírás-ellenőrzést az F7 billentyű lenyomásával is futtathatja, a helyesírási hibákra nincs vizuális jelzés.

Ezzel a kóddal azonnal kiemelheti az összes elgépelt cellát.

'Ez a kód kiemeli azokat a cellákat, amelyek hibásan írt szavakat tartalmaznak Sub HighlightMisspelledCells() Dim cl As Range For Every cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Akkor cl.Interior.Color = vbRed End If Next cl End Sub

Vegye figyelembe, hogy a kiemelt cellák azok a cellák, amelyek szövegét az Excel szerint hibásan írták el.Sok esetben olyan neveket vagy márkakifejezéseket is kiemel, amelyeket nem ért.

Frissítse az összes pivot táblát a munkafüzetben

Ha több pivot táblája van a munkafüzetben, akkor ezzel a kóddal frissítheti az összeset egyszerre.

'Ez a kód frissíti az összes kimutatást a munkafüzet aljában. RefreshAllPivotTables() Dim PT mint PivotTable minden egyes PT-hez az ActiveSheet.PivotTables PT.RefreshTable Következő PT End Sub

A pivot táblázatok frissítéséről itt olvashat bővebben.

Módosítsa a kijelölt cellák kis- és nagybetűit nagybetűre

Míg az Excel rendelkezik képletekkel a szöveges betűk kis- és nagybetűinek megváltoztatására, lehetővé teszi ezt egy másik cellacsoportban.

Ezzel a kóddal azonnal megváltoztathatja a szöveg kis- és nagybetűit a kiválasztott szövegben.

'Ez a kód megváltoztatja a kijelölést nagybetűs Sub ChangeCase() Dim Rng As Range-ra minden egyes Rng-hez Selection.Cells If Rng.HasFormula = False then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Vegye figyelembe, hogy ebben az esetben az UCase-t használom a szöveg nagybetűsre állításához.Használhatja az LCase-t kisbetűkre.

Jelölje ki az összes megjegyzést tartalmazó cellát

A következő kóddal jelölje ki az összes megjegyzést tartalmazó cellát.

"Ez a kód kiemeli a megjegyzésekkel rendelkező cellákat" Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

Ebben az esetben a vbBlue-t használom, hogy kék színt kapjon a cella.Szükség esetén más színre cserélheti.

Jelölje ki az üres cellákat VBA-val

Bár használhat feltételes formázást, vagy használhatja az Ugrás a speciálishoz párbeszédpanelt az üres cellák kiemelésére, ha ezt gyakran kell megtennie, a legjobb makrót használni.

Létrehozása után ezt a makrót megtalálhatja a Gyorselérési eszköztárban, vagy elmentheti a személyes makró-munkafüzetébe.

Íme a VBA makrókód:

'Ez a kód kiemeli az összes üres cellát az adatkészletben Sub HighlightBlankCells() Dim Dataset, mint Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

Ebben a kódban üres cellákat adtam meg pirossal kiemelve.Választhat más színeket is, például kék, sárga, cián stb.

Az adatok egyetlen oszlop szerinti rendezése

Az alábbi kóddal egy megadott oszlop szerint rendezheti az adatokat.

Sub SortDataHeader() 
Tartomány("Adattartomány").Sort Key1:=Tartomány("A1"), Sorrend1:=xlNövekvő, Fejléc:=xlIgen 
End Sub

Vegye figyelembe, hogy létrehoztam egy "DataRange" nevű tartományt, és ezt használtam a cellahivatkozás helyett.

Három kulcsparaméter is használatos itt:

• Key1 – Ez az a kulcs, amely alapján rendezni szeretné az adatkészletet.A fenti mintakódban az adatok az A oszlopban szereplő értékek alapján lesznek rendezve.
• Sorrend1 – Itt kell megadni, hogy növekvő vagy csökkenő sorrendbe kívánja-e rendezni az adatokat.
• Fejléc – Itt meg kell adni, hogy az adatoknak van-e fejléce vagy sem.

További információ arról, hogyan rendezheti az adatokat Excelben VBA használatával.

Az adatok rendezése több oszlop szerint

Tegyük fel, hogy van egy adatkészlete, amely így néz ki:

Íme a kód az adatok több oszlop alapján történő rendezéséhez:

Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Tartomány("A1"), Sorrend:=xlNövekvő .SortFields.Add Key:=Tartomány("B1"), Sorrend:=xlNövekvő .SetA1 Tartomány("SetA13" :CXNUMX") .Header = xlYes .Alkalmaz End With End Sub

Vegye figyelembe, hogy itt azt határoztam meg, hogy először az A oszlopba, majd a B oszlopba kell rendezni.

A kimenet így fog kinézni:

Hogyan lehet csak a numerikus részt lekérni egy karakterláncból az Excelben

Ha csak a numerikus részt vagy a szöveges részt szeretné kivonni a karakterláncból, létrehozhat egy egyéni függvényt a VBA-ban.

Ezután használhatja ezt a VBA-függvényt a munkalapon (mint egy normál Excel-függvény), és csak a számot vagy a szövegrészt fogja kivonni a karakterláncból.

Az alábbiak szerint:

Itt van a VBA-kód, amely egy függvényt hoz létre, amely kivonja a numerikus részt egy karakterláncból:

'Ez a VBA-kód egy függvényt hoz létre, amely a numerikus részt lekéri egy karakterláncból. Funkció GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Ezután Eredmény = Eredmény és közép (CellRef, i, 1) Következő i GetNumeric = Eredmény vége függvény

A kódot egy modulba kell helyezni, majd használhatja a =GetNumeric függvényt a munkalapon.

Ez a függvény csak egy paramétert vesz igénybe, annak a cellának a cellahivatkozását, amelyből a numerikus részt kapja.

Hasonlóképpen, az alábbi függvény csak a szövegrészt kapja meg egy karakterláncból az Excelben:

'Ez a VBA-kód egy függvényt hoz létre a szövegrész lekéréséhez egy karakterláncból Függvény GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength Ha nem (IsNumeric(Mid(CellRef, i, 1))) Akkor Eredmény = Eredmény & Mid(CellRef, i, 1) Következő i GetText = Eredmény vége függvény

Tehát ezek néhány hasznos Excel makrókód, amelyeket napi munkája során használhat a feladatok automatizálására és a termelékenység növelésére.