i den här artikeln ska jag visa dig några av de mest fantastiska VBA Excel-koderna som du kan använda för att optimera ditt arbete. VBA är ett programmeringsspråk som kan användas för att utöka funktionerna i MS Excel och andra MS Office-applikationer. Det är mycket användbart för MS Excel-användare, eftersom det kan användas för att automatisera ditt arbete och avsevärt förbättra din effektivitet. Denna artikel kommer att introducera dig till VBA och visa dig några av de mest användbara, redo att använda VBA koder ute. Du kan använda dessa makroexempel för att skapa egna skript som passar dina egna behov.

du behöver inte programmeringserfarenhet för att dra nytta av informationen i den här artikeln, men du förväntas ha grundläggande kunskaper i Excel. Om du är nybörjare rekommenderar jag att du läser artikeln 20 Excel-formler som du bör börja använda nu för att lära dig mer om Excels kärnfunktioner.

Jag har förberett för dig ett antal redo att använda VBA Excel makro exempel med stor funktionalitet som du kan använda för att optimera ditt arbete. För att kunna använda dem måste du ”installera” dem i din Excel-fil. Nästa stycke handlar om Excel-makroinstallation. Hoppa över den här delen om du redan är bekant med detta.

så här installerar du ett makro

i Excel trycker du på tangentkombinationen alt + F11. Detta tar dig till VBA-redigeraren i MS Excel. Högerklicka sedan på mappen Microsoft Excel Objects till vänster och välj Infoga => modul. Det här är platsen där makron lagras. För att använda makrot måste du spara Excel-dokumentet som makroaktiverat. Från fliken file = > Spara som, välj Spara som makroaktiverad arbetsbok (den .xlsm extension) nu är det dags att skriva ditt första Makro!

1. Kopiera data från en fil till en annan.

mycket användbart makro, eftersom det visar hur man kopierar en rad data inifrån vba och hur man skapar och namnger en ny arbetsbok. Du kan enkelt uppgradera den för att passa dina egna krav:

Sub CopyFiletoAnotherWorkbook() 'kopiera databladen ("exempel 1").Intervall ("B4: C15").Kopiera ' skapa en ny arbetsbok arbetsböcker.Lägg till ' klistra in data ActiveSheet.Klistra in ' Stäng av ansökan varningar ansökan.DisplayAlerts = False ' spara den nya filen. Ändra namnet på katalogen. ActiveWorkbook.SaveAs Filnamn:="C:\Temp\MyNewBook.xlsx "' vänd programvarningar tillbaka på ansökan.DisplayAlerts = TrueEnd Sub

2. Visa dolda rader

ibland innehåller stora Excel-filer dolda rader för bättre tydlighet. Här är ett makro som visar alla rader från ett aktivt kalkylblad:

Sub ShowHiddenRows() kolumner.EntireColumn.Dold = Falska Rader.EntireRow.Dold = FalseEnd Sub

3. Ta bort tomma rader och kolumner

tomma rader i Excel är ett problem med databehandling. Så här blir du av med dem:

Sub DeleteEmptyRowsAndColumns() 'deklarera dina variabler. Dim MyRange som Range Dim iCounter så länge 'definiera målområdet. Ange MyRange = ActiveSheet.UsedRange ' starta omvänd looping genom Radintervallet. För Icounter = MyRange.Rad.Räkna till 1 Steg -1 ' om hela raden är tom, ta bort den. Om Ansökan.CountA(rader (iCounter).EntireRow) = 0 sedan rader(iCounter).Ta bort ' Ta bort kommentar för att se vilka är de tomma raderna 'MsgBox "rad" & iCounter & "är tom" avsluta Om 'öka räknaren ner nästa Icounter 'steg 6: Starta omvänd looping genom kolumnerna. För Icounter = MyRange.Kolumn.Räkna till 1 Steg -1 ' Steg 7: om hela kolumnen är tom, ta bort den. Om Ansökan.CountA(kolumner (Icounter).EntireColumn) = 0 sedan kolumner(Icounter).Ta Bort Avsluta Om ' Steg 8: Öka räknaren ner nästa iCounter End Sub

4. Hitta en tom cell

 Sub FindEmptyCell() ActiveCell.Offset (1, 0).Välj Gör Medan Inte IsEmpty (ActiveCell) ActiveCell.Offset (1, 0).Välj LoopEnd Sub

5. Ersätt tomma celler med ett värde.

som tidigare nämnts stör tomma celler databehandling och skapandet av pivottabeller. Här är en kod som ersätter alla tomma celler med 0. Detta makro har en mycket stor applikation eftersom du kan använda den för att hitta och ersätta N/A-resultat, liksom andra tecken som prickar, kommatecken eller dubbla värden:

Sub FindAndReplace() 'deklarera dina variabler Dim MyRange som intervall Dim MyCell som intervall 'spara arbetsboken innan du byter celler? Välj Case MsgBox ("kan inte ångra den här åtgärden. "& _ " spara arbetsboken först?", Vbyesnocancel) fallet är = vbYes ThisWorkbook.Spara fall är = vbCancel avsluta Sub End Välj ' definiera målområdet. Ställ in MyRange = Selection ' börja looping genom intervallet. För varje MyCell i MyRange 'kontrollera noll längd sedan lägga 0. Om Len (MyCell.Värde) = 0 sedan Mycell = 0 Avsluta om ' Hämta nästa cell i intervallet nästa MyCellEnd Sub

6. Sortera siffror

följande makro sorterar i stigande ordning alla siffror från den aktiva cellens kolumn. Dubbelklicka bara på en cell från en kolumn som du vill sortera.OBS: Du måste sätta koden i ark 1 och inte i en modul för att fungera:

Private Sub Worksheet_BeforeDoubleClick (ByVal Target as Range, Cancel As Boolean) 'deklarera dina variabler Dim LastRow så länge' Hitta sista raden LastRow = celler (rader.Räkna, 1).Slut (xlUp).Rad ' Sortera stigande på dubbelklickade kolumnrader ("6:"& LastRow) .Sortera _ Key1: = Celler (6, ActiveCell.Kolumn), _ Order1: = xlAscendingEnd Sub

7. Ta bort tomma utrymmen

ibland innehåller data i arbetsboken ytterligare mellanslag (mellanslag) som kan störa dataanalysen och korrupta formler. Här är ett makro som tar bort alla mellanslag från ett förvalt cellområde:

Sub TrimTheSpaces() 'deklarera dina variabler Dim MyRange som intervall Dim MyCell som intervall 'spara arbetsboken innan du ändrar celler välj Case MsgBox("kan inte ångra den här åtgärden. "& _ " spara arbetsboken först?", Vbyesnocancel) fallet är = vbYes ThisWorkbook.Spara fall är = vbCancel avsluta Sub End Välj ' definiera målområdet. Ställ in MyRange = Selection ' börja looping genom intervallet. För varje MyCell i MyRange trimma utrymmena. Om inte IsEmpty(MyCell) slutar MyCell = Trim (MyCell) om 'Hämta nästa cell i intervallet nästa MyCellEnd Sub

8. Markera dublicerade värden

ibland finns det dubbla värden i de flera kolumnerna vi vill belysa. Här är ett makro som gör just det:

Sub HighlightDuplicates() 'förklara dina variabler Dim MyRange som Range Dim MyCell som Range 'definiera målområdet. Ställ in MyRange = Selection ' börja looping genom intervallet. För varje MyCell i MyRange 'se till att cellen har textformatering. Om Arbetsbladfunktion.CountIf (MyRange, MyCell.Värde) > 1 Sedan MyCell.Inredning.ColorIndex = 36 Avsluta om ' Hämta nästa cell i intervallet nästa MyCellEnd Sub

9. Markera topp tio värden

denna kod markerar de tio bästa värdena från ett urval av celler:

 Sub TopTen() val.Formatvillkor.AddTop10 Val.Formatvillkor (Urval.Formatvillkor.Räkna).Setfirstprioritet Med Val.Formatvillkor (1).TopBottom = xlTop10Top ' ändra rang här för att markera ett annat antal värden .Rang = 10 .Procent = Falskt Slut Med Med Val.Formatvillkor (1).Teckensnitt .Färg = -16752384 .TintAndShade = 0 Avsluta Med Med Val.Formatvillkor (1).Inredning .PatternColorIndex = xlAutomatic .Färg = 13561798 .TintAndShade = 0 Avsluta Med Val.Formatvillkor (1).StopIfTrue = FalseEnd Sub 

Du kan enkelt justera koden för att markera olika antal värden.

10. Markera större än värden

När du kör den här koden kommer ett fönster att fråga. Det kommer att fråga dig det värde du vill jämföra de celler du har valt.

Sub HighlightGreaterThanValues() Dim I som heltal i = InputBox ("ange större än värde"," Ange värde") val.Formatvillkor.Radera ' ändra operatören till xlower för att markera lägre än val av värden.Formatvillkor.Lägg Typ:=xlCellValue, operatör:=xlGreater, Formula1:=i val.Formatvillkor (Urval.Formatvillkor.Räkna).Setfirstprioritet Med Val.Formatvillkor (1).Teckensnitt.Färg = RGB (0, 0, 0) .Inredning.Color = RGB(31, 218, 154) End WithEnd Sub

Du kan justera den här koden för att markera lägre värden också.

ett enkelt makro som markerar alla celler som innehåller kommentarer:

Sub HighlightCommentCells() val.Specialceller (xlcelltypekommentarer).Välj Val.Style="Obs" avsluta Sub

12. Markera celler med felstavade ord

detta är extremt användbart när du arbetar med funktioner som tar strängar, men någon gick in i strängen med ett misstag och dina formler fungerar inte. Så här åtgärdar du problemet:

 Sub ColorMispelledCells() för varje cl i ActiveSheet.UsedRange Om Inte Ansökan.Kontrollstavning (Ord:= cl.Text) sedan _ cl.Inredning.ColorIndex = 28 nästa clEnd Sub

13. Skapa en pivottabell

Så här skapar du en pivottabell från MS Excel (2007-version). Särskilt användbart när du gör en anpassad rapport varje dag. Du kan optimera skapandet av pivottabellen på följande sätt:

Sub PivotTableForExcel2007() Dim SourceRange As Range Set SourceRange = Sheets ("Sheet1").Intervall ("A3: N86") ActiveWorkbook.PivotCaches.Skapa (_SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Bifoga aktiv arbetsbok i ett e-postmeddelande

min favorit VBA-kod. Det låter dig bifoga och skicka filen du arbetar med med en fördefinierad e-postadress, meddelandetitel och meddelandekropp! Du måste först ställa in referens till Microsoft Outlook (i din VBA editior, klicka på Verktyg => referenser och välj Microsoft Outlook).

Sub SendFIleAsAttachment() 'deklarera dina variabler 'ange referens till Microsoft Outlook Object library Dim OLApp som Outlook.Ansökan Dim OLMail som objekt ' Öppna Outlook starta ett nytt postobjekt Set OLApp = New Outlook.Applikationsuppsättning OLMail = OLApp.CreateItem (0) OLApp.Session.Logga in " bygg ditt e-postobjekt och skicka med OLMail. To = "[email protected]; [email protected]". CC="".BCC = "" .Subject = "Detta är ämnesraden".Kropp = "Hej där" .Bilaga.Lägg ActiveWorkbook.Fullständigt namn .Display ' Ändra till .Skicka för att skicka utan att granska slutet med ' Memory cleanup Set OLMail = Nothing Set OLApp = NothingEnd Sub

15. Skicka alla Excel-diagram till en PowerPoint-presentation

ett mycket praktiskt makro som låter dig lägga till alla dina Excel-diagram i din Powerpoint-presentation bara med ett enda klick:

Sub SendExcelFiguresToPowerPoint() 'ange referens till Microsoft Powerpoint Object Library 'deklarera dina variabler Dim PP som PowerPoint.Ansökan Dim PPPres Som PowerPoint.Presentation Dim PPSlide Som PowerPoint.Slide Dim I som heltal ' kontrollera för diagram; avsluta om inga diagram finns Ark("bilddata").Välj Om ActiveSheet.ChartObjects.Count < 1 Sedan MsgBox" inga diagram befintliga det aktiva arket " avsluta Sub End om 'öppna PowerPoint och skapa ny presentationsuppsättning PP = ny PowerPoint.Ansökan Set PPPres = PP.Presentationer.Lägg till PP.Visible = True ' starta slingan baserat på diagramräkning för i = 1 till ActiveSheet.ChartObjects.Räkna ' kopiera diagrammet som en bild ActiveSheet.ChartObjects (i).Diagram.CopyPicture _ storlek:=xlScreen, Format: = Xlpicture ansökan.Vänta (Nu + TimeValue ("0: 00:1")) ' Räkna bilder och Lägg till ny bild som nästa tillgängliga bildnummer ppSlideCount = PPPres.Skjuta.Räkna Set PPSlide = PPPres.Skjuta.Lägg till (SlideCount + 1, ppLayoutBlank) PPSlide.Välj ' Klistra in bilden och justera dess position; gå till nästa diagram PPSlide.Form.Klistra.Välj PP.Aktivt fönster.Urval.ShapeRange.Justera msoAlignCenters, sann PP.Aktivt fönster.Urval.ShapeRange.Justera msoAlignMiddles, Sant nästa I ' Memory Cleanup Set PPSlide = ingenting Set PPPres = ingenting set PP = NothingEnd Sub

16. Skicka Excel-tabell i MS Word

Excel-tabeller placeras vanligtvis i textdokument. Här är ett automatiserat sätt att exportera Excel-tabellen till MS Word:

 Sub ExcelTableInWord() 'ange referens till Microsoft Word Object library 'deklarera dina variabler Dim MyRange som Excel.Range Dim wd som ord.Ansökan Dim wdDoc som ord.Dokument Dim WdRange Som Ord.Range ' kopiera de definierade intervallark ("Intäktstabell").Intervall ("B4: F10").Cop ' öppna målet Word dokument Set wd = nytt ord.Ansökan Set wdDoc = wd.Dokument.Öppna _ (ThisWorkbook.Sökväg && " PasteTable.docx") wd.Visible = True ' Ställ in fokus på målbokmärket Set WdRange = wdDoc.Bokmärken ("DataTableHere").Range ' ta bort den gamla tabellen och klistra in ny på fel återuppta nästa WdRange.Tabeller (1).Ta Bort WdRange.Klistra in ' klistra in i tabellen 'justera kolumnbredder WdRange.Tabeller (1).Kolumn.SetWidth _ (MyRange.Bredd / MyRange.Kolumn.Räkna), Wdadjustsamewidth ' Sätt tillbaka bokmärket wdDoc.Bokmärken.Lägg till "DataTableHere", Wdrange 'Memory cleanup Set wd = Nothing Set wdDoc = Nothingend Sub

17. Extrahera ett specifikt ord från en cell

Vi kan använda formler om vi vill extrahera ett visst antal symboler. Men vad händer om vi bara vill extrahera det andra ordet från en mening eller ett antal ord i en cell? För att göra detta kan vi skapa en anpassad Excel-funktion med VBA. Detta är en av de mest viktiga VBA-funktionerna, eftersom det låter dig skapa egna funktioner som inte finns i MS Excel. Låt oss fortsätta och skapa två funktioner: findword() och findwordrev(). Här är VBA-koden för detta:

funktion FindWord(källa som sträng, Position som heltal) som sträng på fel återuppta nästa FindWord = Split(Kalkylbladfunktion.Trim (källa),"") (Position - 1) på fel GoTo 0End FunctionFunction FindWordRev(källa som sträng, Position som heltal) som sträng Dim Arr () som sträng Arr = VBA.Split(Arbetsbladfunktion.Trim (källa),"") på fel fortsätt nästa FindWordRev = Arr(UBound(Arr) - Position + 1) på fel GoTo 0end-funktion

mycket trevligt, vi har skapat två Cstom Excel-funktioner. Försök nu använda dem i Excel. Funktionen = FindWordRev (A1,1) tar det sista ordet från cell A1. Funktionen = FindWord (A1,3) tar det tredje ordet från cell A1, etc.

18. Skydda din arbetsbok

Ibland vill vi skydda data i vår fil så att bara vi kan ändra den. Så här gör du med VBA:

Sub ProtectSheets() 'deklarera dina variabler Dim ws som kalkylblad 'börja looping genom alla kalkylblad för varje ws i ActiveWorkbook.Kalkylblad ' Skydda och slinga till nästa kalkylblad ws.Skydda lösenord:= "1234" nästa Wsend Sub

Grattis! Eftersom du fortfarande läser detta är du verkligen angelägen om att lära dig VBA. Som du redan har sett själv är VBA-programmeringsspråket extremt användbart och kan spara oss mycket tid. Jag hoppas att du hittade den här informationen till hjälp och använder den för att bli en mästare i MS Excel, VBA och datorprogram i allmänhet.