i denne artikkelen skal jeg vise deg noen AV DE mest fantastiske Vba Excel-kodene du kan bruke til å optimalisere arbeidet ditt. VBA ER et programmeringsspråk, som kan brukes til å utvide mulighetene TIL MS Excel og ANDRE MS Office-programmer. DET er svært nyttig FOR MS Excel-brukere, fordi den kan brukes til å automatisere arbeidet og forbedre effektiviteten. Denne artikkelen vil introdusere DEG TIL VBA og vise deg noen av de mest nyttige, klar til BRUK VBA koder der ute. Du kan bruke disse makroeksemplene til å lage dine egne skript som passer dine egne behov.

du trenger ikke programmeringserfaring for å dra nytte av informasjonen i denne artikkelen, men du forventes å ha grunnleggende Kunnskaper I Excel. Hvis du er nybegynner, vil jeg anbefale deg å lese artikkelen 20 Excel-Formler Du Bør Begynne Å Bruke Nå For å lære Mer Om Excel ‘ s kjernefunksjoner.

jeg har forberedt for deg en rekke klar TIL bruk VBA Excel Makro eksempler med stor funksjonalitet som du kan bruke til å optimalisere arbeidet. For å kunne bruke dem må du» installere » Dem i Excel-filen. Neste avsnitt omhandler Excel makroinstallasjon. Hopp over denne delen hvis du allerede er kjent med dette.

slik installerer du en makro

I Excel trykker du på tastekombinasjonen alt + F11. Dette tar DEG til vba-editoren I MS Excel. Deretter høyreklikker Du på Microsoft Excel Objects-mappen til venstre og velger Insert = > Modul. Dette er stedet der makroene er lagret. For å gjøre bruk av makroen, må du lagre Excel-dokumentet som makroaktivert. Fra fanen fil => lagre som, velg lagre som makroaktivert arbeidsbok (den.xlsm extension) nå er det på tide å skrive din første makro!

1. Kopier data fra en fil til en annen.

Veldig nyttig makro, da det viser hvordan du kopierer en rekke data fra innsiden av vba og hvordan du oppretter og navngir en ny arbeidsbok. Du kan enkelt oppgradere den til å passe dine egne behov:

Under CopyFiletoAnotherWorkbook() 'Kopier Databladene("Eksempel 1").Rekkevidde ("B4: C15").Kopier ' Opprett en ny arbeidsbok Arbeidsbøker.Legg Til ' Lim inn Dataaktivarket.Lim Inn ' Slå av programvarsler Søknad.DisplayAlerts = False ' Lagre den nye filen . Endre navnet på katalogen. ActiveWorkbook.SaveAs Filnavn:="C:\Temp\MyNewBook.xlsx "' Slå søknad varsler tilbake På Programmet.DisplayAlerts = Trueend Sub

2. Vis skjulte rader

noen Ganger inneholder Store Excel-filer skjulte linjer for bedre klarhet. Her er en makro som vil vise alle rader fra et aktivt regneark:

Sub ShowHiddenRows() Kolonner.EntireColumn.Skjult = Falske Rader.EntireRow.Skjult = FalseEnd Sub

3. Slett tomme rader og kolonner

Tomme rader I Excel er et problem med databehandling. Slik blir du kvitt dem:

Sub DeleteEmptyRowsAndColumns() 'Deklarere variablene dine. Dim MyRange Som Rekkevidde Dim iCounter Så Lenge ' Definere Målområdet. Sett MyRange = ActiveSheet.UsedRange ' Start omvendt looping gjennom rekken Av Rader. For Icounter = MyRange.Rad.Tell Til 1 Trinn -1 ' hvis hele raden er tom, slett den. Hvis Søknad.Tella (Rader (iCounter).EntireRow) = 0 Deretter Rader (iCounter).MsgBox "row" & iCounter & "Er tom" Slutt Hvis 'Øk telleren Ned Neste iCounter' Trinn 6: Start omvendt looping gjennom kolonnene. For Icounter = MyRange.Kolonne.Telle Til 1 Trinn -1 ' Trinn 7: hvis hele kolonnen er tom, slett den. Hvis Søknad.CountA (Kolonner (iCounter).EntireColumn) = 0 Deretter Kolonner (iCounter).Slett Slutt Hvis ' Trinn 8: Øk telleren Ned Neste iCounter End Sub

4. Finn en tom celle

 Sub FindEmptyCell() ActiveCell.Offset (1, 0).Velg Gjør Mens Ikke IsEmpty(ActiveCell) ActiveCell.Offset (1, 0).Velg LoopEnd Sub

5. Erstatt tomme celler med en verdi.

som tidligere nevnt forstyrrer tomme celler databehandling og opprettelse av pivottabeller. Her er en kode som erstatter alle tomme celler med 0. Denne makroen har et veldig stort program fordi du kan bruke den til å finne Og erstatte N / a-resultater, samt andre tegn som prikker, kommaer eller dupliserte verdier:

Sub FindAndReplace() 'Deklarere variablene Dim MyRange Som Rekkevidde Dim MyCell Som Rekkevidde 'Lagre Arbeidsboken Før du endrer celler? Velg Sak MsgBox ("Kan Ikke Angre denne handlingen. "& _ "Lagre Arbeidsboken Først?", vbYesNoCancel) Saken er = Vbyes ThisWorkbook.Lagre Saken er = Vbcancel Exit Sub End Velg ' Definer Målområdet . Set MyRange = Selection ' Start looping gjennom området . For Hver MyCell I MyRange 'Sjekk for null lengde deretter legge 0. Hvis Len (MyCell.Verdi) = 0 Deretter MyCell = 0 End Hvis ' Få den neste cellen I området Neste MyCellEnd Sub

6. Sorter tall

følgende makro sorterer i stigende rekkefølge alle tall fra den aktive cellens kolonne. Bare dobbeltklikk på en celle fra en kolonne du vil sortere.NB: du må sette koden i Ark 1 og ikke i en modul for å jobbe:

Private Sub Worksheet_BeforeDoubleClick (ByVal Mål Som Rekkevidde, Avbryt Som Boolsk) 'Erklære Variablene Dim LastRow Så Lenge' Finn siste rad LastRow = Celler (Rader.Antall, 1).Slutt (xlUp).Row ' Sorter stigende på dobbeltklikkede kolonnerader ("6:" & LastRow).Sorter _ Nøkkel1: = Celler (6, ActiveCell.Kolonne), _ Order1: = xlAscendingEnd Sub

7. Fjern tomme mellomrom

noen ganger inneholder data i arbeidsboken flere mellomrom (mellomrom) som kan forstyrre dataanalyse og ødelegge formler. Her er en makro som vil fjerne alle mellomrom fra et forhåndsvalgt celleområde:

Sub TrimTheSpaces() 'Deklarere variablene Dim MyRange Som Range Dim MyCell As Range 'Lagre Arbeidsboken Før du endrer celler Velg Sak MsgBox ("Kan ikke Angre denne handlingen. "& _ "Lagre Arbeidsboken Først?", vbYesNoCancel) Saken er = Vbyes ThisWorkbook.Lagre Saken er = Vbcancel Exit Sub End Velg ' Definer Målområdet . Set MyRange = Selection ' Start looping gjennom området . For Hver MyCell I MyRange 'Trim Mellomrom. Hvis Ikke IsEmpty (MyCell) Så MyCell = Trim (MyCell) Slutt Hvis ' Få neste celle I området Neste MyCellEnd Sub 

8. Fremhev dublerte verdier

Noen ganger er det dupliserte verdier i flere kolonner vi ønsker å belyse. Her er en makro som gjør nettopp det:

Sub HighlightDuplicates() 'Deklarere variablene Dim MyRange Som Rekkevidde Dim MyCell Som Rekkevidde 'Definer Målområdet. Set MyRange = Selection ' Start looping gjennom området . For Hver MyCell I MyRange 'Sikre cellen Har Tekstformatering. Hvis WorksheetFunction.MyRange, MyCell.Verdi) > 1 Deretter MyCell.Interiør.ColorIndex = 36 End Hvis ' Få neste celle I området Neste MyCellEnd Sub 

9. Fremhev topp ti verdier

denne koden vil markere topp ti verdier fra et utvalg av celler:

Sub TopTen() Utvalg.Formatbetingelser.AddTop10 Utvalg.FormatConditions (Utvalg.Formatbetingelser.Telle).SetFirstPriority Med Utvalg.Formatbetingelser (1).TopBottom = xlTop10Top ' Endre rangeringen her for å markere et annet antall verdier .Rang = 10 .Prosent = False End Med Med Utvalg.Formatbetingelser (1).Skrift .Farge = -16752384 .TintAndShade = 0 Slutt Med Med Utvalg.Formatbetingelser (1).Interiør .PatternColorIndex = xlAutomatic .Farge = 13561798 .TintAndShade = 0 Slutt Med Valg.Formatbetingelser (1).StopIfTrue = FalseEnd Sub 

du kan enkelt justere koden for å markere forskjellig antall verdier.

10. Merk større enn verdier

når du kjører denne koden, vil et vindu spørre. Det vil spørre deg verdien du vil sammenligne cellene du har valgt.

Sub HighlightGreaterThanValues () Dim Jeg Som Heltall i = InputBox ("Skriv Inn Større Enn Verdi"," Skriv Inn Verdi") Utvalg.Formatbetingelser.Slett ' Endre Operatoren til xlLower å markere Lavere enn verdier Utvalg.Formatbetingelser.Legg Til Type:=xlcellvalue, Operatør: = xlGreater, Formula1:= I Utvalg.FormatConditions (Utvalg.Formatbetingelser.Telle).SetFirstPriority Med Utvalg.Formatbetingelser (1).Skrift.Farge = RGB (0, 0, 0).Interiør.Farge = RGB(31, 218, 154) Slutt WithEnd Sub

du kan justere denne koden for å markere lavere verdier også.

en enkel makro som fremhever alle cellene som inneholder kommentarer:

Sub HighlightCommentCells() Utvalg.SpecialCells (xlCellTypeComments).Velg Utvalg.Style="Merk" Slutt Sub

12. Fremhev Celler med Feilstavede ord

Dette er ekstremt nyttig når du jobber med funksjoner som tar strenger, men noen kom inn i strengen med en feil, og formlene dine virker ikke. Her er hvordan du løser dette problemet:

 Sub ColorMispelledCells () For hver cl I ActiveSheet.UsedRange Hvis Ikke Programmet.CheckSpelling (Word:= cl.Tekst) deretter _ cl.Interiør.ColorIndex = 28 neste clEnd Sub

13. Opprett en pivottabell

her er hvordan du oppretter en pivottabell FRA MS Excel (2007 versjon). Spesielt nyttig, når du lager en tilpasset rapport hver dag. Du kan optimalisere pivottabellopprettelsen på følgende måte:

Sub Pivottableforexcel2007 () Dim SourceRange Som Range Set SourceRange = Sheets ("Sheet1").Område ("A3: N86") ActiveWorkbook.Pivotcacher.Opprett (_SourceType: = xlDatabase, _ SourceData: = SourceRange, _ Versjon: = xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=xlPivotTableVersion12End Sub

14. Legg ved aktiv arbeidsbok i En E-Post

min favoritt VBA-kode. Den lar deg legge ved og sende filen du jobber med med en forhåndsdefinert e-postadresse, meldingstittel, og meldingstekst! Du må først angi referanse Til Microsoft Outlook (i VBA editior, klikk på verktøy = > referanser og velg Microsoft Outlook).

Sub SendFIleAsAttachment () 'Deklarere variablene dine' Angi referanse Til Microsoft Outlook-Objektbibliotek Dim OLApp Som Outlook.Søknad Dim OLMail Som Objekt ' Åpne Outlook start en ny e-post element Satt OLApp = Ny Outlook.Søknad Satt OLMail = OLApp.CreateItem (0) OLApp.Økt.Logon ' Bygg e-posten din og send Med OLMail. To = "[email protected]; [email protected]". CC="".BCC="".Subject = "Dette Er Emnelinjen".Kropp = "Hei der".Vedlegg.Legg Til ActiveWorkbook.Fullt navn .Display ' Endre til .Send for å sende Uten å gjennomgå Slutt Med ' Memory cleanup Set OLMail = Ingenting Sett OLApp = NothingEnd Sub 

15. Send Alle Excel-diagrammer til En PowerPoint-presentasjon

en veldig praktisk makro som lar deg legge til Alle Excel-diagrammer i Powerpoint-presentasjonen bare med et enkelt klikk:

Sub SendExcelFiguresToPowerPoint () 'Angi referanse Til Microsoft Powerpoint Object Library' Erklære variablene Dim PP Som PowerPoint.Søknad Dim PPPres Som PowerPoint.Presentasjon Dim PPSlide Som PowerPoint.Skyv Dim jeg Som Heltall ' Sjekk for diagrammer; avslutt Hvis Ingen diagrammer finnes Ark ("Lysbildedata").Velg Om ActiveSheet.Kartobjekter.Count< 1 Deretter MsgBox "Ingen diagrammer eksisterende det aktive arket" Avslutt Sub End Hvis ' Åpne PowerPoint og opprett ny presentasjon Sett PP = Ny PowerPoint.Søknad Sett PPPres = PP.Presentasjon.Legg TIL PP.Visible = True ' Start sløyfen basert på diagramtelling for i = 1 Til ActiveSheet.Kartobjekter.Count ' Kopier diagrammet Som et Bilde ActiveSheet.ChartObjects (i).Diagram.CopyPicture _ Størrelse: = xlScreen, Format:=xlPicture Søknad.Vent ( Nå + TimeValue ("0: 00:1")) ' Telle lysbilder og legg til nytt lysbilde som neste tilgjengelige lysbildenummer ppslidecount = PPPres.Skyve.Antall Sett PPSlide = PPPres.Skyve.Legg til (SlideCount + 1, ppLayoutBlank) PPSlide.Velg ' Lim inn bildet og juster posisjonen; Gå til neste diagram PPSlide.Form.Lime.Velg PP.ActiveWindow.Utvalg.ShapeRange.Juster msoAlignCenters, Ekte PP.ActiveWindow.Utvalg.ShapeRange.Juster msoAlignMiddles, Sant Neste Jeg ' Minne Opprydding Satt PPSlide = Ingenting Satt PPPRES = Ingenting Satt PP = NothingEnd Sub 

16. Send Excel-tabell I MS Word

Excel-tabeller legges vanligvis inn i tekstdokumenter. Her er en automatisert måte å eksportere Excel-tabellen TIL MS Word:

 Sub ExcelTableInWord() 'Angi referanse Til Microsoft Word Object library 'Erklære variablene Dim MyRange Som Excel.Range Dim wd Som Ord.Søknad Dim wdDoc Som Word.Dokument Dim WdRange Som Word.Område ' Kopier de definerte områdearkene ("Inntektstabell").Rekkevidde ("B4: F10").Cop ' Åpne Målet Word-dokument Satt wd = Nytt Ord.Søknad Sett wdDoc = wd.Dokument.Åpne _ (ThisWorkbook.Sti && "PasteTable.docx") wd.Synlig = Sann ' Sett fokus På målbokmerket Sett WdRange = wdDoc.Bokmerker ("DataTableHere").Range ' Slett den gamle tabellen og lim inn ny På Feil Gjenoppta Neste WdRange.Tabeller (1).Slett WdRange.Lim inn 'lim inn i tabellen' Juster kolonnebredder WdRange.Tabeller (1).Kolonne.Settbredde _ (MyRange.Bredde / MyRange.Kolonne.Antall), wdAdjustSameWidth ' Sett inn bokmerket wdDoc.Bokmerke.Legg til "DataTableHere", WdRange 'Minneopprydding Sett wd = Ingenting Sett wdDoc = Ingenting Sett WdRange = NothingEnd Sub

17. Trekk ut et bestemt ord fra en celle

vi kan bruke formler hvis vi vil trekke ut et visst antall symboler. Men hva om vi bare vil trekke ut det andre ordet fra en setning eller en rekke ord i en celle? For å gjøre dette kan vi lage en tilpasset Excel-funksjon MED VBA. Dette er en AV DE mest viktige vba-funksjonalitetene, fordi den lar deg lage dine egne funksjoner som ikke eksisterer I MS Excel. La oss fortsette og lage to funksjoner: findword () og findwordrev (). Her er vba-koden for dette:

Funksjon FindWord (Kilde Som Streng, Posisjon Som Heltall) Som Streng På Feil Gjenoppta Neste FindWord = Split (WorksheetFunction.Trim(Kilde),"") (Posisjon - 1) På Feil GoTo 0end FunctionFunction FindWordRev (Kilde Som Streng, Posisjon Som Heltall) Som Streng Dim Arr () Som Streng Arr = VBA.Split (WorksheetFunction.Findwordrev = Arr ( UBound (Arr) - Posisjon + 1) På Feil GoTo 0end Funksjon 

Veldig fint, vi har laget to Cstom Excel-funksjoner. Prøv nå å bruke Dem i Excel. Funksjonen = FindWordRev (A1,1) tar det siste ordet Fra celle A1. Funksjonen = FindWord (A1,3) tar det tredje ordet Fra celle A1, etc.

18. Beskytt Arbeidsboken din

noen ganger vil vi beskytte dataene i filen vår slik at vi bare kan endre den. Slik gjør du DETTE MED VBA:

Sub ProtectSheets () 'Deklarere variablene Dim ws Som Regneark' Start looping gjennom alle regneark for Hver ws I ActiveWorkbook.Regneark ' Beskytt og sløyfe til neste regneark ws.Beskytt Passord:= "1234" Neste Wsend Sub 

Gratulerer! Siden du fortsatt leser dette, er du veldig opptatt AV å lære VBA. SOM du allerede har sett for deg selv, er VBA-programmeringsspråket ekstremt nyttig og kan spare oss mye tid. Jeg håper du fant denne informasjonen nyttig og bruke den til å bli en master I MS Excel, VBA og programvare generelt.