Az Excel makrók használata felgyorsíthatja a munkát, és sok időt takaríthat meg.
A VBA kód megszerzésének egyik módja a makró rögzítése és az általa létrehozott kód felvétele. A makró rögzítő által készített kód azonban gyakran tele van olyan kódokkal, amelyekre nincs igazán szükség. A makró rögzítőnek is vannak korlátai.
Érdemes tehát olyan hasznos VBA makrókódokat gyűjteni, amelyeket a hátsó zsebében tarthat, és szükség esetén használhatja.
Bár az Excel VBA makrókód írása kezdetben eltarthat egy ideig, de miután elkészült, referenciaként elérhetővé teheti, és bármikor használhatja.
Ebben a hatalmas cikkben néhány hasznos Excel makró példát fogok felsorolni, amelyekre gyakran szükségem van, és elrejtve a privát tárolómban.
Folyamatosan frissítem ezt az oktatóanyagot további makró példákkal. Ha úgy gondolja, hogy valami szerepelnie kell a listán, írjon megjegyzést.
Ezt az oldalt könyvjelzővel láthatja el későbbi használatra.
Mielőtt belekezdenék a makró példába és megadnám a VBA kódot, hadd mutassam meg először, hogyan kell használni ezeket a példakódokat.
Az Excel makró példákból származó kód használata
Íme a lépések, amelyeket követnie kell a példák bármelyikének használatához:
- Nyissa meg azt a munkafüzetet, amelyben használni szeretné a makrót.
- Tartsa lenyomva az ALT billentyűt, és nyomja meg az F11 billentyűt. Ez megnyitja a VB szerkesztőt.
- Kattintson a jobb gombbal a Project Explorer bármelyik objektumára.
- Lépjen a Beszúrás -> Modul menüpontra.
- Másolja és illessze be a kódot a modulkód ablakba.
Ha a példa azt mondja, hogy be kell illesztenie a kódot a munkalap kódablakába, kattintson duplán a munkalap objektumára, és másolja be a kódot a kódablakba.
Miután beillesztette a kódot a munkafüzetbe, el kell mentenie .XLSM vagy .XLS kiterjesztéssel.
A makró futtatása
Miután lemásolta a kódot a VB szerkesztőben, a makró futtatásához tegye a következőket:
- Lépjen a Fejlesztő fülre.
- Kattintson a Makrók elemre.
- A Makró 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.
Kapcsolódó oktatóanyag: A makró futtatásának különböző módjai Excelben.
Ha a kódot beilleszti a munkalap kódablakába, nem kell aggódnia a kód futtatása miatt. A megadott művelet végrehajtásakor automatikusan elindul.
Most nézzük meg azokat a hasznos makrópéldákat, amelyek segíthetnek a munka automatizálásában és az időmegtakarításban.
Megjegyzés: Sok példát talál az aposztrófra (’), amelyet egy -két sor követ. Ezek olyan megjegyzések, amelyeket figyelmen kívül hagynak a kód futtatása során, és megjegyzésekként helyezik el az ön/olvasó számára.
Ha bármilyen hibát talál a cikkben vagy a kódban, kérjük, legyen félelmetes, és tudassa velem.
Példák Excel makrókra
Az alábbi makrópéldákat ebben a cikkben tárgyaljuk:
Az összes munkalap megjelenítése egyszerre
Ha olyan munkafüzetben dolgozik, amely több rejtett lapot tartalmaz, ezeket a lapokat egyenként fel kell tárnia. Ez eltarthat egy ideig, ha sok rejtett lap van.
Itt található a kód, amely megjeleníti a munkafüzet összes munkalapját.
'Ez a kód feloldja a munkafüzet összes lapját.
A fenti kód VBA -hurkot (Mindegyikhez) használ a munkafüzet minden munkalapjának áttekintéséhez. Ezután a munkalap látható tulajdonságát láthatóvá változtatja.
Itt található egy részletes oktatóanyag arról, hogyan lehet különböző módszereket használni a lapok megjelenítéséhez az Excelben.
Az összes munkalap elrejtése, kivéve az aktív lapot
Ha jelentésen vagy irányítópulton dolgozik, és el szeretné rejteni az összes munkalapot, kivéve azt, amelyik tartalmazza a jelentést/irányítópultot, akkor használja ezt a makrókódot.
'Ez a makró elrejti az összes munkalapot, kivéve az aktív lapot.
Munkalapok rendezése betűrendben a VBA segítségével
Ha sok munkalappal rendelkező munkafüzete van, és ezeket betűrendben szeretné rendezni, akkor ez a makrókód nagyon jól jöhet. Ez akkor fordulhat elő, ha a munkalapok nevei évszámként vagy munkavállalói névként vagy terméknévként szerepelnek.
'Ez a kód betűrendbe rendezi a munkalapokat Sub SortSheetsTabName () Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets. Ha Táblázatok (j). Név <Táblázatok (i). Név Akkor Táblázatok (j). Mozgás előtt: = Táblázatok (i) Vége Ha Következő j Következő i Alkalmazás. ScreenUpdating = Igaz Vége al
Védje meg az összes munkalapot egyszerre
Ha sok munkalap van egy munkafüzetben, és meg szeretné védeni az összes lapot, akkor használja ezt a makrókódot.
Lehetővé teszi a jelszó megadását a kódon belül. Erre a jelszóra lesz szüksége a munkalap védelmének feloldásához.
'Ez a kód egyszerre védi az összes lapot. End Sub
Egyszerre távolítsa el az összes munkalap védelmét
Ha a munkalapok egy része vagy mindegyike védett, akkor a lapok védelmére használt kód enyhe módosításával feloldhatja azt.
'Ez a kód egyszerre védi az összes lapot. End Sub
Ne feledje, hogy a jelszónak ugyanaznak kell lennie, mint a munkalapok zárolásához. Ha nem, akkor hibát fog látni.
Az összes sor és oszlop megjelenítése
Ez a makrókód megjeleníti az összes rejtett sort és oszlopot.
Ez nagyon hasznos lehet, ha valaki mástól kap egy fájlt, és biztos akar lenni abban, hogy nincsenek rejtett sorok/oszlopok.
'Ez a kód feloldja a munkalap összes sorát és oszlopát.
Minden egyesített cella egyesítése
Általános gyakorlat, hogy egyesítik a cellákat, hogy eggyé váljanak. Bár ez működik, a cellák összevonásakor nem tudja rendezni az adatokat.
Ha egyesített cellákkal ellátott munkalappal dolgozik, használja az alábbi kódot az összesített cellák egyesítéséhez.
'Ez a kód feloldja az összes egyesített cellát Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Ne feledje, hogy az Összevonás és a Középpont helyett azt javaslom, hogy használja a Középső kiválasztás lehetőséget.
Mentse a munkafüzetet Időbélyeggel a nevében
Sok idő alatt előfordulhat, hogy létre kell hoznia a munkájának verzióit. Ezek nagyon hasznosak azokban a hosszú projektekben, ahol idővel fájlokkal dolgoznak.
Jó gyakorlat, ha a fájlt időbélyegzővel menti.
Az időbélyegek használatával visszatérhet egy bizonyos fájlhoz, és megtekintheti, hogy milyen változtatásokat hajtottak végre, vagy milyen adatokat használtak fel.
Itt található a kód, amely automatikusan menti a munkafüzetet a megadott mappába, és időbélyeget ad hozzá minden mentéskor.
'Ez a kód menti a fájlt időbélyeggel a nevében ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp 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á menteni kívánja a fájlt. Ezenkívül a „WorkbookName” általános nevet használtam a fájlnév előtagjaként. Megadhat valamit a projektjéhez vagy a vállalatához kapcsolódóan.
Mentsen el minden munkalapot külön PDF -ként
Ha különböző évekre, divíziókra vagy termékekre vonatkozó adatokkal dolgozik, előfordulhat, hogy a különböző munkalapokat PDF fájlként kell mentenie.
Noha időigényes folyamat lehet, ha manuálisan végzik, a VBA valóban felgyorsíthatja.
Itt van egy VBA -kód, amely minden munkalapot külön PDF -ként ment el.
'Ez a kód minden egyes rosszindulatú lapot külön PDF -ként ment el.
A fenti kódban megadtam annak a mappának a címét, ahová el akarom menteni a PDF -eket. Továbbá minden PDF ugyanazt a nevet kapja, mint a munkalap. Módosítania kell ezt a mappa helyét (kivéve, ha a neve is Sumit, és az asztali tesztmappába menti).
Ne feledje, hogy ez a kód csak a munkalapokra vonatkozik (és nem a diagramlapokra).
Mentsen el minden munkalapot külön PDF -ként
Itt található a kód, amely a teljes munkafüzetet PDF -fájlként menti a megadott mappába.
'Ez a kód a teljes munkafüzetet PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub formátumban menti el
A kód használatához meg kell változtatnia a mappa helyét.
Az összes képlet konvertálása értékekké
Ezt a kódot akkor használja, ha van egy munkalapja, amely sok képletet tartalmaz, és ezeket a képleteket értékekre szeretné konvertálni.
'Ez a kód az összes képletet értékekké alakítja Sub ConvertToValues () ActiveSheet.UsedRange .Value = .Value End With End Sub
Ez a kód automatikusan azonosítja a használt cellákat, és értékekké alakítja át.
A cellák védelme/zárolása képletekkel
Érdemes zárolni a cellákat képletekkel, ha sok számítása van, és nem akarja véletlenül törölni vagy módosítani.
Itt található a kód, amely zárolja az összes cellát, amely képlettel rendelkezik, míg az összes többi cella nincs zárolva.
'Ez a makrókód zárolja az összes cellát a következő képletekkel: Sub LockCellsWithFormulas () ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells (xlCellTypeFormulas) .Locked = True.
Kapcsolódó oktatóanyag: A cellák zárolása az Excelben.
Védje a munkafüzet összes munkalapját
Használja az alábbi kódot a munkafüzet összes munkalapjának védelmére egyszerre.
'Ez a kód megvédi a munkafüzet összes lapját Sub ProtectAllSheets () Dim ws munkalapként minden ws munkalapban ws.
Ez a kód egyenként végigmegy az összes munkalapon, és megvédi.
Abban az esetben, ha fel szeretné oldani az összes munkalap védelmét, használja a ws.Unprotect parancsot a ws helyett. Védje a kódot.
Egy sort illesszen be a kijelölés minden sorába
Használja ezt a kódot, ha üres sort szeretne beszúrni a kiválasztott tartomány minden sora után.
'Ez a kód egy sort illeszt be a kiválasztás minden sorába. Helyezze be az ActiveCell.Offset (2, 0) elemet. Válassza a Next i End Sub lehetőséget
Hasonlóképpen módosíthatja ezt a kódot, hogy üres oszlopot illesszen a kiválasztott tartomány minden oszlopa után.
Dátum és időbélyegző automatikus beszúrása a szomszédos cellába
Az időbélyeget használhatja a tevékenységek nyomon követésére.
Érdemes például nyomon követni a tevékenységeket, például azt, hogy mikor merült fel egy adott költség, hány órakor készült az értékesítési számla, mikor történt az adatbevitel egy cellában, mikor frissítették legutóbb a jelentést stb.
Ezzel a kóddal dátumot és időbélyeget illeszthet be a szomszédos cellába, amikor bejegyzés történik, vagy a meglévő tartalmat szerkesztik.
'Ez a kód időbélyeget illeszt be a szomszédos cellába Private Sub Worksheet_Change (ByVal Target as Range) On GoTo Handler if Target.Column = 1 And Target.Value "" Then Application.EnableEvents = False Target.Offset (0, 1) = Formátum (Most (), "dd-mm-yyyy hh: mm: ss") Application.EnableEvents = True End, ha kezelő: End Sub
Ne feledje, hogy ezt a kódot be kell illesztenie a munkalap kódablakába (és nem a modul kódablakába, mint más Excel makrópéldákban eddig). Ehhez a VB szerkesztőben kattintson duplán arra a lap nevére, amelyen szeretné használni ezt a funkciót. Ezután másolja ki és illessze be ezt a kódot a lap kódablakába.
Ez a kód akkor is működik, ha az adatbevitel az A oszlopban történik (vegye figyelembe, hogy a kód Target.Column = 1 sora). Ezt ennek megfelelően módosíthatja.
Jelölje ki az alternatív sorokat a kijelölésben
Az alternatív sorok kiemelése óriási mértékben növelheti az adatok olvashatóságát. Ez akkor lehet hasznos, ha ki kell nyomtatnia és át kell néznie az adatokat.
Itt van egy kód, amely azonnal kiemeli az alternatív sorokat a kijelölésben.
'Ez a kód kiemeli az alternatív sorokat a Sub HighlightAlternateRows () Dim Myrange mint tartomány Dim Myrow mint tartomány beállítása Myrange = Kiválasztás minden Myrow My Myrange.Rows If Myrow.Row Mod 2 = 1 Majd Myrow.Interior.Color = vbCyan End Ha Következő Myrow End Sub
Ne feledje, hogy a színt vbCyan -ként adtam meg a kódban. Megadhat más színeket is (például vbRed, vbGreen, vbBlue).
A cellák kiemelése rosszul írt szavakkal
Az Excel nem rendelkezik helyesírás -ellenőrzéssel, mint a Wordben vagy a PowerPointban. Bár a helyesírás -ellenőrzést az F7 billentyű megnyomásával futtathatja, nincs vizuális jelzés helyesírási hiba esetén.
Ezzel a kóddal azonnal kiemelheti az összes cellát, amelyben helyesírási hiba található.
'Ez a kód kiemeli azokat a cellákat, amelyekben rosszul írt szavak vannak cl End Sub
Ne feledje, hogy a kiemelt cellák azok, amelyek szövegét az Excel helyesírási hibának tekinti. Sok esetben olyan neveket vagy márkaneveket is kiemelne, amelyeket nem ért.
Frissítse a munkafüzet összes kimutatástáblát
Ha egynél több kimutatástábla van a munkafüzetben, akkor ezzel a kóddal frissítheti ezeket a Pivot táblákat egyszerre.
'Ez a kód frissíti a Pivot táblázatot a munkafüzetben. Ref.
A Pivot táblázatok frissítéséről itt olvashat bővebben.
Változtassa meg a kijelölt cellák kis- és nagybetűit nagybetűsre
Míg az Excel rendelkezik képletekkel a szöveg betű- és kisbetűinek megváltoztatásához, ezt egy másik cellában teszi meg.
Ezzel a kóddal azonnal megváltoztathatja a kiválasztott szövegben szereplő szöveg betű- és kisbetűit.
'Ez a kód megváltoztatja a kijelölést nagybetűs Sub ChangeCase () Dim Rng tartományként minden kiválasztott Rng tartományban. 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áltam a szöveg kis- és nagybetűinek elkészítéséhez. Használhatja az LCase -t kisbetűkhöz.
Jelölje ki az összes cellát megjegyzésekkel
Az alábbi kód segítségével jelölje ki az összes cellát, amely megjegyzéseket tartalmaz.
"Ez a kód kiemeli a megjegyzéseket tartalmazó cellákat" Sub HighlightCellsWithComments () ActiveSheet.Cells.SpecialCells (xlCellTypeComments) .Interior.Color = vbBlue End Sub
Ebben az esetben a vbBlue segítségével kék színt adtam a sejteknek. Ezt más színekre is módosíthatja, ha szeretné.
Az üres cellák kiemelése VBA -val
Míg feltételes formázással vagy az Ugrás a speciális párbeszédpanellel kiemelheti az üres cellát, ha gyakran kell ezt tennie, akkor jobb, ha makrót használ.
Létrehozása után a makrót megtalálhatja a gyorselérési eszköztáron, vagy mentheti a személyes makró munkafüzetébe.
Itt a VBA makrókód:
'Ez a kód kiemeli az adathalmaz összes üres celláját Sub HighlightBlankCells () Dim Dataset Range Set Dataset = Selection Dataset. SpecialCells (xlCellTypeBlanks).
Ebben a kódban megadtam az üres cellákat, amelyeket piros színnel kell kiemelni. Választhat más színeket is, például kék, sárga, ciánkék stb.
Az adatok rendezése egyetlen oszlop szerint
Az alábbi kód segítségével rendezheti az adatokat a megadott oszlop szerint.
Sub SortDataHeader () tartomány ("DataRange"). Rendezési kulcs1: = Tartomány ("A1"), Sorrend1: = xlNövekvő, Fejléc: = xlIgen End Sub
Vegye figyelembe, hogy létrehoztam egy elnevezett tartományt „DataRange” névvel, és azt használtam a cellahivatkozások helyett.
Ezenkívül itt három kulcsparamétert használnak:
- 1. kulcs - Ez az, amelyen rendezni szeretné az adathalmazt. A fenti példakódban az adatokat az A oszlop értékei alapján rendezik.
- Sorrend- Itt kell megadni, hogy növekvő vagy csökkenő sorrendbe szeretné rendezni az adatokat.
- Fejléc - Itt meg kell adnia, hogy az adatai fejléceket tartalmaznak -e vagy sem.
További információ az adatok rendezéséről Excelben a VBA használatával.
Az adatok rendezése több oszlop szerint
Tegyük fel, hogy rendelkezik az alább látható adatkészlettel:
Az alábbiakban az a kód található, amely több oszlop alapján rendezi az adatokat:
Sub SortMultipleColumns () ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 "). Fejléc = xlIgen. Alkalmazza a végét a végponttal
Vegye figyelembe, hogy itt az A oszlop, majd a B oszlop alapján rendeltem először.
A kimenet az alábbiak szerint lesz:
Hogyan lehet csak a numerikus részt lekérni az Excel karakterláncából
Ha egy karakterláncból csak a numerikus részt vagy csak a szöveges részt szeretné kibontani, akkor létrehozhat egy egyéni függvényt a VBA -ban.
Ezt a VBA függvényt használhatja a munkalapon (csakúgy, mint a hagyományos Excel függvényeket), és csak a numerikus vagy szöveges részt vonja ki a karakterláncból.
Valami az alábbiak szerint:
Az alábbiakban látható a VBA kód, amely létrehoz egy függvényt a numerikus rész kivonására egy karakterláncból:
'Ez a VBA -kód létrehoz egy függvényt a numerikus rész lekéréséhez egy karakterláncból ) Majd Eredmény = Eredmény és közepes (CellRef, i, 1) Következő i GetNumeric = Eredményvégfüggvény
Szüksége van egy kódban elhelyezésre egy modulban, majd használhatja a = GetNumeric függvényt a munkalapon.
Ez a függvény csak egy argumentumot tartalmaz, amely annak a cellának a cellahivatkozása, amelyből a numerikus részt le szeretné kérni.
Hasonlóképpen, az alábbiakban az a funkció található, amely csak az Excel karakterlánc szöveges részét kapja meg:
'Ez a VBA -kód létrehoz egy függvényt a szövegrész lekéréséhez egy karakterláncból 1))) Majd Eredmény = Eredmény és közepes (CellRef, i, 1) Következő i GetText = Eredményvégfüggvény
Tehát ezek azok a hasznos Excel makrókódok, amelyeket a mindennapi munkában használhat a feladatok automatizálására és sokkal produktívabbá tétele érdekében.
