ebben a cikkben megmutatom neked a legcsodálatosabb VBA Excel kódokat, amelyek segítségével optimalizálhatja munkáját. A VBA egy programozási nyelv, amely az MS Excel és más MS Office alkalmazások képességeinek bővítésére használható. Ez rendkívül hasznos MS Excel felhasználók számára, mert lehet használni, hogy automatizálják a munkát, és jelentősen javítja a hatékonyságot. Ez a cikk bemutatja, hogy VBA, majd mutassa meg néhány, a leghasznosabb, használatra kész VBA kódok odakinn. Használhatja ezeket a makró példákat, hogy saját szkripteket hozzon létre, amelyek megfelelnek a saját igényeinek.

nem kell programozási tapasztalat, hogy kihasználják az információkat ebben a cikkben, de akkor várható, hogy az alapvető ismereteket az Excel. Ha kezdő felhasználó vagy, azt javaslom, hogy olvassa el a cikket 20 Excel képletek, amelyeket most el kell kezdenie használni, hogy többet megtudjon az Excel alapvető funkcióiról.

számos használatra kész VBA Excel makró példát készítettem Önnek, nagyszerű funkcionalitással, amellyel optimalizálhatja munkáját. Használatukhoz “telepíteni” kell őket az Excel fájlba. A következő bekezdés az Excel makró telepítésével foglalkozik. Hagyja ki ezt a részt, ha már ismeri ezt.

makró telepítése

Excelben nyomja meg az alt + F11 billentyűkombinációt. Ez elviszi a VBA szerkesztő MS Excel. Ezután kattintson a jobb gombbal a bal oldali Microsoft Excel objektumok mappára, majd válassza az Insert => modul lehetőséget. Ez az a hely, ahol a makrókat tárolják. A makró használatához az Excel dokumentumot makróként kell mentenie. A lapon file = > Mentés másként, válassza a Mentés makró-kompatibilis munkafüzet (az .xlsm kiterjesztés) most itt az ideje, hogy megírja az első makrót!

1. Adatok másolása egyik fájlból a másikba.

nagyon hasznos makró, mivel megmutatja, hogyan másolhat egy sor adatot a vba belsejéből, valamint hogyan hozhat létre és nevezhet meg egy új munkafüzetet. Könnyedén frissíteni, hogy illeszkedjen a saját igényeinek:

 sub CopyFiletoAnotherWorkbook ()"másolja az adatlapokat ("1.példa").Tartomány ("B4: C15").Másolás " hozzon létre egy új munkafüzet munkafüzeteket.Add " illessze be az adatok ActiveSheet.Paste " kapcsolja ki alkalmazás riasztások alkalmazás.DisplayAlerts = False ' mentse el az új fájlt. Módosítsa a könyvtár nevét. ActiveWorkbook.SaveAs Fájlnév:="C:\Temp\MyNewBook.xlsx "" kapcsolja be az alkalmazás figyelmeztetéseit az alkalmazáson.DisplayAlerts = TrueEnd Sub

2. Rejtett sorok megjelenítése

alkalmanként a nagy Excel fájlok rejtett sorokat tartalmaznak a jobb érthetőség érdekében. Itt van egy makró, amely felfedi az összes sort egy aktív munkalapról:

 Sub ShowHiddenRows() oszlopok.Teljes ősz.Rejtett = Hamis Sorok.EntireRow.Rejtett = FalseEnd Sub

3. Az üres sorok és oszlopok törlése

üres sorok az Excelben problémát jelentenek az adatfeldolgozásban. Itt van, hogyan lehet megszabadulni tőlük:

Sub DeleteEmptyRowsAndColumns() 'állapítsa meg a változókat. Dim MyRange mint tartomány Dim iCounter ameddig " határozza meg a céltartományt. Állítsa Be A MyRange = ActiveSheet Értéket.UsedRange " Start fordított hurok a Sortartományban. ICounter = MyRange.Sorok.Számoljon 1 lépés -1 " Ha a teljes sor üres, akkor törölje azt. Ha Alkalmazás.Gróf(Icounter).EntireRow ) = 0 majd sorok (iCounter).Törlés 'Eltávolítani hozzászólás Látni, amelyek az üres sorok 'MsgBox "sor" & iCounter & "üres" Vége, Ha a számláló le a Következő iCounter '6. Lépés: Indítsa el a fordított hurok át a tartomány Oszlopok. ICounter = MyRange.Oszlopok.Gróf 1 lépés -1 ' 7. lépés: ha a teljes oszlop üres, akkor törölje azt. Ha Alkalmazás.Gróf (oszlopok (iCounter).EntireColumn ) = 0 majd oszlopok (iCounter).Törlés Vége, Ha " 8. Lépés: Növelje a számlálót lefelé következő iCounter End Sub

4. Keressen egy üres cellát

Sub FindEmptyCell() ActiveCell.Eltolás(1, 0).Válassza A Do Lehetőséget, Ha Nem IsEmpty(ActiveCell) ActiveCell.Eltolás(1, 0).Válassza a LoopEnd Sub

5 lehetőséget. Cserélje ki az üres cellákat egy értékkel.

mint korábban említettük, az üres cellák zavarják az adatfeldolgozást és a pivot táblák létrehozását. Itt van egy kód, amely felváltja az összes üres cellát 0. Ez a makró egy nagyon nagy alkalmazás, mert akkor használja, hogy megtalálja és cserélje n / a eredmények, valamint más karakterek, mint a pontok, vesszők vagy ismétlődő értékek:

Sub FindAndReplace () "állapítsa meg a változók Dim MyRange tartomány Dim MyCell tartomány" mentse a munkafüzet megváltoztatása előtt sejtek? Válassza Case MsgBox ("nem lehet visszavonni ezt a műveletet. "& _ "először mentse el a munkafüzetet?", vbYesNoCancel) eset = vbYes ThisWorkbook.Az eset mentése = vbCancel kilépés sub End Select ' határozza meg a céltartományt. Állítsa be a Myrange = kiválasztás ' indítsa el a hurkolást a tartományon keresztül. Minden MyCell a MyRange ' ellenőrizze a nulla hosszúságú, majd adjunk hozzá 0. Ha Len(MyCell.Érték) = 0 akkor MyCell = 0 vége, ha ' kap a következő cellát a következő tartományban MyCellEnd Sub

6. Rendezés számok

a következő makró rendezi növekvő sorrendben az összes számot az aktív cella oszlopában. Csak kattintson duplán bármelyik cellára egy oszlopból, amelyet rendezni szeretne.Megjegyzés: a kódot az 1. lapba kell helyezni, nem pedig egy modulba, hogy működjön:

Private Sub Workheet_beforedoubleclick (ByVal Target as Range, Cancel As Boolean) 'állapítsa meg a változók Dim LastRow as Long 'Find last row LastRow = Cells (Rows.Gróf, 1) .Vége (xlUp) .Sor ' rendezés növekvő dupla kattintással oszlopsorok ("6:" & LastRow) .Rendezés _ Key1: = Sejtek (6, ActiveCell.Oszlop), _ Order1: = xlAscendingEnd Sub 

7. Távolítsa el az üres szóközöket

alkalmanként, a munkafüzetben lévő adatok további szóközöket (szóközöket) tartalmaznak, amelyek zavarhatják az adatok elemzését és a képletek sérülését. Itt van egy makró, amely eltávolítja az összes szóközt egy előre kiválasztott cellatartományból:

sub TrimTheSpaces () 'állapítsa meg a változókat Dim MyRange tartományként Dim MyCell tartományként' mentse el a munkafüzetet a cellák megváltoztatása előtt válassza ki az MsgBox esetet("nem tudja visszavonni ezt a műveletet. "& _ "először mentse el a munkafüzetet?", vbYesNoCancel) eset = vbYes ThisWorkbook.Az eset mentése = vbCancel kilépés sub End Select ' határozza meg a céltartományt. Állítsa be a Myrange = kiválasztás ' indítsa el a hurkolást a tartományon keresztül. Minden Myrange MyCell esetében vágja le a tereket. Ha nem IsEmpty (MyCell), akkor a MyCell = Trim (MyCell) akkor ér véget, ha 'a következő cellát kapja a következő MyCellEnd Sub

8 tartományban. Jelölje ki a duplikált értékeket

néha duplikált értékek vannak a megvilágítani kívánt oszlopokban. Itt van egy makró, amely éppen ezt teszi:

Sub HighlightDuplicates () "deklarálja a változókat Dimmyrange tartományként Dim MyCell tartományként" határozza meg a céltartományt. Állítsa be a Myrange = kiválasztás ' indítsa el a hurkolást a tartományon keresztül. A Myrange 'minden egyes MyCell-jéhez győződjön meg arról, hogy a cella szövegformázással rendelkezik. Ha WorksheetFunction.CountIf(MyRange, MyCell.Érték) > 1 majd MyCell.Belső tér.ColorIndex = 36 vége, ha " kap a következő cellát a következő tartományban MyCellEnd Sub

9. Jelölje ki az első tíz értéket

ez a kód kiemeli az első tíz értéket a cellák közül:

Sub TopTen() kiválasztás.FormatConditions.AddTop10 Kiválasztás.FormatConditions (Kiválasztás.FormatConditions.Gróf).SetFirstPriority A Kiválasztás.FormatConditions(1).TopBottom = xlTop10Top ' változtassa meg a rangot itt, hogy kiemelje a különböző értékek számát .Rang = 10 .Százalék = Hamis Vége A Kiválasztással.FormatConditions(1).Font .Szín = -16752384 .TintAndShade = 0 Vége A Kiválasztással.FormatConditions(1).Belső tér .Mintaszínindex = xlAutomatic .Szín = 13561798 .TintAndShade = 0 Vége A Kiválasztással.FormatConditions(1).StopIfTrue = FalseEnd Sub

könnyedén módosíthatja a kódot, hogy kiemelje a különböző értékek számát.

10. Jelölje ki a

értékeknél nagyobb értéket, amikor futtatja ezt a kódot, egy ablak jelenik meg. Meg fogja kérdezni, hogy milyen értéket szeretne összehasonlítani a kiválasztott cellákkal.

 sub HighlightGreaterThanValues() Dim I egész számként i = InputBox ("Enter nagyobb, mint érték"," Enter érték") kiválasztás.FormatConditions.Törlés " változtassa meg az operátort xlLower-re, hogy kiemelje az értékek alatti kijelölést.FormatConditions.Add Típus: = xlCellValue, operátor:=xlGreater, Formula1: = I kiválasztás.FormatConditions (Kiválasztás.FormatConditions.Gróf).SetFirstPriority A Kiválasztás.FormatConditions(1).Font.Szín = RGB (0, 0, 0) .Belső tér.Color = RGB(31, 218, 154) End WithEnd Sub

akkor csípés ezt a kódot, hogy kiemelje az alacsonyabb értékeket is.

egy egyszerű makró, amely kiemeli az összes megjegyzést tartalmazó cellát:

 sub HighlightCommentCells() kiválasztás.Speciális cellák (xlCellTypeComments).Válassza A Kiválasztás Lehetőséget.Style = "Note" End Sub 

12. Jelölje ki a

elírt szavakkal rendelkező cellákat ez rendkívül hasznos, ha olyan funkciókkal dolgozik, amelyek karakterláncokat vesznek fel, azonban valaki hibával lépett be a karakterláncba, a képletek pedig nem működnek. Itt van, hogyan kell kijavítani ezt a problémát:

 Sub ColorMispelledCells() minden Cl ActiveSheet.UsedRange Ha Nem Alkalmazás.CheckSpelling (Szó:= cl.Szöveg) majd _ cl.Belső tér.ColorIndex = 28 következő clEnd Sub 

13. Hozzon létre egy pivot táblát

így hozhat létre pivot táblát MS Excel-ből (2007 verzió). Különösen hasznos, ha minden nap egyéni jelentést készít. A pivot tábla létrehozását a következő módon optimalizálhatja:

Sub PivotTableForExcel2007 () Dim SourceRange mint Tartománykészlet SourceRange = lapok ("Sheet1").Tartomány ("A3:N86") ActiveWorkbook.PivotCaches.Create (_SourceType: = xlDatabase, _ SourceData:=SourceRange, _ Version: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Csatolja az aktív munkafüzetet egy e-mailben

kedvenc VBA kódom. Ez lehetővé teszi, hogy csatolja, majd küldje el a fájlt dolgozik egy előre meghatározott e-mail címet, üzenet címe, üzenet test! Először be kell állítania a Microsoft Outlook hivatkozást(a VBA editiorjában kattintson az eszközök = > referenciák elemre, majd válassza a Microsoft Outlook lehetőséget).

 Sub SendFIleAsAttachment() 'állapítsa meg a változók 'set reference to Microsoft Outlook Object library Dim OLApp as Outlook.Alkalmazás Dim OLMail objektumként ' Open Outlook új e-mail elem beállítása OLApp = új Outlook.Alkalmazás Készlet OLMail = OLApp.CreateItem (0) OLApp.Ülés.Jelentkezzen be ' készítsd el a mail elemet, majd Küldj OLMail. To = "[email protected]; [email protected]". CC ="".BCC ="".Subject = "This is the Subject line" .Test = "Szia".Mellékletek.Add ActiveWorkbook.Teljes név .Kijelző " változás .Küldés elküldés nélkül felülvizsgálata végén " memória razzia készlet OLMail = semmi készlet OLApp = NothingEnd Sub

15. Küldje el az összes Excel diagramok egy PowerPoint prezentáció

egy nagyon praktikus makró, amely lehetővé teszi, hogy adjunk az összes Excel grafikonok a Powerpoint bemutató csak egyetlen kattintással:

Sub SendExcelFiguresToPowerPoint () "hivatkozás beállítása Microsoft Powerpoint objektum Könyvtár" állapítsa meg a változók Dim PP PowerPoint.Alkalmazás Dim PPPres Mint PowerPoint.Bemutató Dim PPSlide Mint PowerPoint.Csúsztassa a Dim I-T egész számként " check for charts; kilépés, ha nincs diagramlap ("Diaadatok").Válassza Az IF ActiveSheet Lehetőséget.ChartObjects.Count < 1 majd MsgBox" nincs diagramok meglévő aktív lap " Exit Sub End ha 'Open PowerPoint and create new presentation Set PP = új PowerPoint.Alkalmazáskészlet PPPres = PP.Előadások.Add hozzá a PP-t.Látható = True ' indítsa el a hurok alapján diagram száma i = 1 ActiveSheet.ChartObjects.Count ' másolja a táblázatot, mint egy kép ActiveSheet.ChartObjects (i).Táblázat.CopyPicture _ Size: = xlScreen, Format: = xlPicture alkalmazás.Várj (Most + TimeValue("0: 00:1")) " számolja a diákat, majd adjon hozzá új diát a következő rendelkezésre álló slide számként ppSlideCount = PPPres.Csúszdák.Count Set PPSlide = PPPres.Csúszdák.Add (SlideCount + 1, ppLayoutBlank) PPSlide.Válassza a " illessze be a képet, majd állítsa be a helyzetét; ugrás a következő táblázat PPSlide.Formák.Paszta.Válassza a PP lehetőséget.ActiveWindow.Kiválasztás.ShapeRange.Igazítsa msoAlignCenters, igaz PP.ActiveWindow.Kiválasztás.ShapeRange.Igazítsa msoAlignMiddles, True Next I ' Memory Cleanup Set PPSlide = semmi Set PPPres = semmi Set PP = NothingEnd Sub 

16. Küldés Excel táblázat MS Word

Excel táblázatok általában be szöveges dokumentumok. Itt van egy automatizált módon exportáló Excel tábla MS Word:

 Sub ExcelTableInWord () "hivatkozás beállítása Microsoft Word Object library" állapítsa meg a változók Dim Myrange Excel.Tartomány Dim wd mint szó.Alkalmazás Dim wdDoc mint szó.Dokumentum Dim WdRange Mint Szó.Tartomány " másolja a meghatározott tartománylapokat ("bevételi táblázat").Tartomány ("B4: F10").Cop ' nyissa meg a cél Word dokumentumkészletet wd = Új Szó.Alkalmazáskészlet wdDoc = wd.Dokumentumok.Open _ (ThisWorkbook.&& "PasteTable.docx") KR.Látható = True ' állítsa Fókuszban a cél könyvjelző készlet WdRange = wdDoc.Könyvjelzők ("DataTableHere").Tartomány " törölje a régi táblázatot, majd illessze be az új hiba Folytatás következő Wdrange.Táblázat(1).Törölje A WdRange-T.Illessze be a "beillesztés a táblázatba" beállítást oszlopszélesség WdRange.Táblázat(1).Oszlopok.SetWidth _ (MyRange.Szélesség / MyRange.Oszlopok.Gróf), wdAdjustSameWidth " Reinert the bookmark wdDoc.Könyvjelzők.Add hozzá a" DataTableHere", Wdrange 'Memory cleanup Set wd = Nothing Set Wddoc = Nothing Set Wdrange = NothingEnd Sub

17. Kivonat egy adott szót egy cellából

képleteket használhatunk, ha bizonyos számú szimbólumot akarunk kivonni. De mi van, ha csak a második szót akarjuk kivonni egy mondatból vagy egy sor szóból egy cellában? Ehhez létrehozhatunk egy egyéni Excel funkciót a VBA-val. Ez az egyik legfontosabb VBA funkció, mert lehetővé teszi, hogy saját funkciókat hozzon létre, amelyek nem léteznek az MS Excel programban. Folytassuk és hozzunk létre két funkciót: findword() és findwordrev(). Itt van a vba kód erre:

Function FindWord (forrás stringként, pozíció egészként) mint String on Error Folytatás következő FindWord = Split(WorksheetFunction.Trim (forrás),"") (pozíció - 1) A hiba GoTo 0end FunctionFunction FindWordRev (forrás String, pozíció egész) mint String Dim Arr () String Arr = VBA.Split (WorksheetFunction.Trim (forrás),"") a hiba Folytatás következő FindWordRev = Arr (UBound (Arr) - pozíció + 1) A hiba Goto 0end funkció

nagyon szép, hoztunk létre két cstom Excel funkciók. Most próbálja meg használni őket Excel-ben. A függvény = FindWordRev (A1,1) az utolsó szót veszi az A1 cellából. A függvény = FindWord (A1, 3) a harmadik szót az A1 cellából stb.

18. Védje munkafüzetét

néha meg akarjuk védeni a fájlunkban lévő adatokat, hogy csak mi tudjuk megváltoztatni. Itt van, hogyan kell ezt csinálni a VBA:

 Sub ProtectSheets() 'állapítsa meg a változók Dim ws munkalapként 'elkezd hurkolt keresztül minden munkalapok minden ws ActiveWorkbook.Munkalapok védelme és hurok a következő munkalap ws.Jelszó védelme: = "1234" következő Wsend Sub

gratulálok! Mivel még mindig ezt olvasod, nagyon szívesen tanulod a VBA-t. Mint már láttad magadnak, a VBA programozási nyelv rendkívül hasznos, sok időt takaríthat meg nekünk. Remélem, hasznosnak találta ezt az információt, és arra használja, hogy mesterré váljon az MS Excel, a VBA és általában a számítógépes szoftverekben.