i denne artikel vil jeg vise dig nogle af de mest fantastiske VBA-koder, som du kan bruge til at optimere dit arbejde. VBA er et programmeringssprog, som kan bruges til at udvide funktionerne i MS Office og andre MS Office-applikationer. Det er yderst nyttigt for MS-brugere, fordi det kan bruges til at automatisere dit arbejde og forbedre din effektivitet betydeligt. Denne artikel vil introducere dig til VBA og vise dig nogle af de mest nyttige, klar til at bruge VBA koder derude. Du kan bruge disse makroeksempler til at oprette dine egne scripts, der passer til dine egne behov.

Du behøver ikke programmeringserfaring for at drage fordel af oplysningerne i denne artikel, men du forventes at have grundlæggende viden om udmærke sig. Hvis du er nybegynder, vil jeg anbefale dig at læse artiklen 20 formler, du skal begynde at bruge nu for at lære mere om de grundlæggende funktioner.

Jeg har forberedt dig en række klar til brug VBA udmærke Makro eksempler med stor funktionalitet, som du kan bruge til at optimere dit arbejde. For at bruge dem skal du “installere” dem i din fil. Det næste afsnit omhandler Makro installation. Spring denne del over, hvis du allerede er bekendt med dette.

Sådan installeres en makro

tryk på tastekombinationen alt + F11. Dette fører dig til VBA-editoren i MS. Højreklik derefter på mappen Microsoft-objekter til venstre, og vælg Indsæt => modul. Dette er stedet, hvor makroerne er gemt. Hvis du vil bruge makroen, skal du gemme dokumentet som makroaktiveret. Fra fanen file => Gem som, vælg Gem som makroaktiveret projektmappe (den .nu er det tid til at skrive din første Makro!

1. Kopier data fra en fil til en anden.

meget nyttig Makro, da det viser, hvordan man kopierer en række data indefra vba og hvordan man opretter og navngiver en ny projektmappe. Du kan nemt opgradere den, så den passer til dine egne krav:

 Sub Copyfiletoanotherarbejdsbog() 'Kopier databladene ("eksempel 1").Rækkevidde ("B4:C15").Kopier ' Opret en ny projektmappe projektmapper.Tilføj ' Indsæt dataaktivarket.Indsæt ' sluk ansøgning advarsler ansøgning.DisplayAlerts = False ' gem den nyligt fil. Skift navnet på mappen. Aktivarbejdsbog.SaveAs Filnavn:="C:\Temp\MyNewBook.""slå applikationsadvarsler tilbage på applikationen.DisplayAlerts = TrueEnd Sub

2. Vis skjulte rækker

lejlighedsvis indeholder store filer skjulte linjer for bedre klarhed. Her er en makro, der viser alle rækker fra et aktivt regneark:

Undervisningskolonner ().Entiecolumn.Skjulte = Falske Rækker.Hele vejen.Skjult = FalseEnd Sub

3. Slet tomme rækker og kolonner

tomme rækker er et problem med databehandling. Sådan slipper du af med dem:

Sub Deleteemptyrogcolumns() 'erklære dine variabler. Dim MyRange som rækkevidde Dim iCounter så længe ' definere målområdet. Sæt MyRange = ActiveSheet.UsedRange ' Start omvendt looping gennem rækken af rækker. For Icounter = MyRange.Række.Tæl til 1 trin -1 ' hvis hele rækken er tom, skal du slette den. Hvis Ansøgning.CountA(rækker (iCounter).Entirerou) = 0 derefter rækker (iCounter).Slet 'Fjern kommentar for at se, hvilke der er de tomme rækker 'Msgboks" række " & icounter &" er tom " slut, hvis 'Forøg tælleren ned næste iCounter 'Trin 6: Start omvendt looping gennem kolonneområdet. For Icounter = MyRange.Kolonner.Tæl til 1 trin -1 'Trin 7: hvis hele kolonnen er tom, skal du slette den. Hvis Ansøgning.CountA(kolonner (Icounter).Entiecolumn) = 0 derefter kolonner (iCounter).Slet End Hvis ' Trin 8: Forøg tælleren ned næste iCounter End Sub

4. Find en tom celle

Sub FindEmptyCell() ActiveCell.Forskydning (1, 0).Vælg Gør Mens Ikke IsEmpty (ActiveCell) ActiveCell.Forskydning (1, 0).Vælg LoopEnd Sub

5. Udskift tomme celler med en værdi.

som tidligere nævnt forstyrrer tomme celler databehandling og oprettelse af pivottabeller. Her er en kode, der erstatter alle tomme celler med 0. Denne makro har en meget stor applikation, fordi du kan bruge den til at finde og erstatte N/A-resultater såvel som andre tegn som prikker, kommaer eller duplikatværdier:

Sub FindAndReplace() 'erklære dine variabler Dim MyRange som rækkevidde Dim MyCell som rækkevidde 'Gem projektmappen, før du skifter celler? Vælg sag Msgboks ("kan ikke fortryde denne handling. "& _ " Gem projektmappe først?", vbYesNoCancel) sagen er = vbYes denne arbejdsbog.Gem sag er = vbCancel Afslut Sub End Vælg ' Definer målområdet. Indstil MyRange = Selection ' Start looping gennem området. For hver MyCell i MyRange 'Check for nul længde derefter tilføje 0. Hvis Len (MyCell.Værdi) = 0 derefter Mycell = 0 End hvis ' få den næste celle i området næste MyCellEnd Sub

6. Sorter tal

følgende makro sorterer i stigende rækkefølge alle tal fra den aktive celles kolonne. Dobbeltklik bare på en celle fra en kolonne, du gerne vil sortere.NB: Du skal sætte koden i ark 1 og ikke i et modul for at arbejde:

 privat Sub-Regneark_foredoubleclick (ByVal-mål som rækkevidde, Annuller som boolsk) 'Deklarer dine variabler Dim Lastrove så længe 'Find sidste række Lastrove = celler (rækker.Tælle, 1).Slut (slut).Række ' sorter stigende på dobbeltklikkede kolonnerækker ("6:" & Sidstrække) .Sorter _ Key1: = Celler (6, ActiveCell.Kolonne), _ Ordre1: = udsending Sub 

7. Fjern tomme mellemrum

lejlighedsvis indeholder data i projektmappen yderligere mellemrum (mellemrum), der kan forstyrre dataanalyse og ødelægge formler. Her er en makro, der fjerner alle mellemrum fra et forudvalgt celleområde:

Sub TrimTheSpaces() 'Angiv dine variabler Dim MyRange som Range Dim MyCell som Range 'Gem projektmappen, før du ændrer celler vælg sag Msgboks("kan ikke fortryde denne handling. "& _ " Gem projektmappe først?", vbYesNoCancel) sagen er = vbYes denne arbejdsbog.Gem sag er = vbCancel Afslut Sub End Vælg ' Definer målområdet. Indstil MyRange = Selection ' Start looping gennem området. For hver MyCell i MyRange 'Trim mellemrummene. Hvis ikke IsEmpty(MyCell), slutter MyCell = Trim (MyCell), Hvis 'få den næste celle i området næste MyCellEnd Sub

8. Fremhæv dublicerede værdier

Nogle gange er der duplikatværdier i de flere kolonner, vi gerne vil belyse. Her er en makro, der gør netop det:

Sub HighlightDuplicates() 'Angiv dine variabler Dim MyRange som rækkevidde Dim MyCell som rækkevidde 'Definer målområdet. Indstil MyRange = Selection ' Start looping gennem området. For hver MyCell i MyRange 'sikre cellen har tekstformatering. Hvis Regnearkfunktion.CountIf (MyRange, MyCell.Værdi) > 1 Derefter MyCell.Interiør.Farveindeks = 36 End hvis ' få den næste celle i området næste MyCellEnd Sub

9. Fremhæv top ti værdier

denne kode fremhæver de ti bedste værdier fra et udvalg af celler:

Sub TopTen() valg.Formatbetingelser.AddTop10 Valg.Formatbetingelser (Valg.Formatbetingelser.Tælle).SetFirstPriority Med Udvælgelse.Formatbetingelser (1).Topbottom = hltop10top 'Skift rang her for at fremhæve et andet antal værdier .Rang = 10 .Procent = Falsk Ende Med Med Valg.Formatbetingelser (1).Skrifttype .Farve = -16752384 .TintAndShade = 0 Slut Med Med Valg.Formatbetingelser (1).Interiør .Mønsterfarveindeks = automatisk .Farve = 13561798 .TintAndShade = 0 Slut Med Valg.Formatbetingelser (1).StopIfTrue = FalseEnd Sub 

Du kan nemt justere koden for at fremhæve forskellige antal værdier.

10. Fremhæv større end værdier

når du kører denne kode, vil et vindue spørge. Det vil bede dig om den værdi, du vil sammenligne de celler, du har valgt.

Sub HighlightGreaterThanValues() Dim I som heltal i = Inputboks("indtast større end værdi", "indtast værdi") valg.Formatbetingelser.Slet ' Skift operatoren til lavere for at markere lavere end værdier.Formatbetingelser.Tilføj Type: = lcellvalue, Operator:=llgreater, Formula1:=i valg.Formatbetingelser (Valg.Formatbetingelser.Tælle).SetFirstPriority Med Udvælgelse.Formatbetingelser (1).Skrifttype.Farve = RGB (0, 0, 0) .Interiør.Farve = RGB (31, 218, 154) slut medend Sub

Du kan justere denne kode for også at fremhæve lavere værdier.

en simpel makro, der fremhæver alle de celler, der indeholder kommentarer:

Sub HighlightCommentCells() valg.SpecialCells (kommentarer).Vælg Valg.Style= "Note" End Sub

12. Fremhæv celler med forkert stavede ord

dette er yderst nyttigt, når du arbejder med funktioner, der tager strenge, men nogen indtastede strengen med en fejl, og dine formler fungerer ikke. Sådan løses dette problem:

 Sub ColorMispelledCells() for hver cl i ActiveSheet.UsedRange Hvis Ikke Ansøgning.CheckSpelling (Ord:= cl .Tekst) derefter _ cl.Interiør.Colorindeks = 28 næste clEnd Sub

13. Opret en pivottabel

Sådan oprettes en pivottabel fra MS (2007-version). Især nyttigt, når du laver en brugerdefineret rapport hver dag. Du kan optimere pivottabeloprettelsen på følgende måde:

Sub Pivottableforecel2007() Dim SourceRange as Range Set SourceRange = Sheets("Sheet1").Rækkevidde ("A3:N86") Aktivarbejdsbog.PivotCaches.Opret (_SourceType:=sldatabase, _ SourceData:=SourceRange, _ Version:=slpivottableversion12).CreatePivotTable _ TableDestination:="", _ TableName:="", _ DefaultVersion:=lpivottableversion12end Sub

14. Vedhæft aktiv projektmappe i en e-mail

min favorit VBA-kode. Det giver dig mulighed for at vedhæfte og sende den fil, du arbejder på, med en foruddefineret e-mail-adresse, meddelelsestitel og meddelelseskrop! Du skal først indstille reference til Microsoft Outlook (i din VBA editior skal du klikke på Værktøjer => referencer og vælge Microsoft Outlook).

 Sub SendFIleAsAttachment() 'Angiv dine variabler 'Indstil reference til Microsoft Outlook Object library Dim OLApp som Outlook.Ansøgning Dim OLMail som objekt ' Åbn Outlook start et nyt postelement sæt OLApp = ny Outlook.Ansøgning Sæt OLMail = OLApp.CreateItem (0) OLApp.Session.Logon ' Byg din post og send med OLMail. to = "[email protected]; [email protected]". CC = "" .BCC = "" .Emne = "Dette er emnelinjen".Krop = "Hej der".Vedhæftet.Tilføj Aktivarbejdsbog.FullName .Vis ' Skift til .Send for at sende uden at gennemgå ende med ' Memory cleanup Set OLMail = Nothingend Olapp = NothingEnd Sub 

15.

en meget praktisk Makro, der giver dig mulighed for at tilføje alle dine diagrammer i din præsentation med et enkelt klik:

Sub Sendekselfunktionspunkt() 'Angiv reference til Microsoft Objektbibliotek 'Angiv dine variabler Dim PP som Effektpunkt.Ansøgning Dim PPPres Som Magtpunkt.Præsentation Dim PPSlide Som Magtpunkt.Slide Dim I som heltal ' Check for diagrammer; Afslut, hvis der ikke findes diagrammer ark("Diasdata").Vælg Hvis ActiveSheet.ChartObjects.Tæl < 1 Derefter Msgboks "ingen diagrammer eksisterende det aktive ark" Afslut Sub End hvis 'Åbn Effektpunkt og Opret nyt præsentationssæt PP = nyt Effektpunkt.Ansøgning sæt PPPres = PP.Præsentationer.Tilføj PP.Synlig = sand ' Start sløjfen baseret på diagramtælling for i = 1 til ActiveSheet.ChartObjects.Tæl ' Kopier diagrammet som et billede Aktivark.ChartObjects (i).Diagram.CopyPicture _ størrelse: = skærm, Format: = applikation.Vent (Nu + TimeValue ("0:00:1")) ' Tæl dias og tilføj nyt dias som næste tilgængelige diasnummer ppSlideCount = PPPres.Slide.Count Sæt PPSlide = PPPres.Slide.Tilføje (SlideCount + 1, ppLayoutBlank) PPSlide.Vælg ' Indsæt billedet og juster dets position; gå til næste diagram PPSlide.Figurer.Indsætte.Vælg PP.Aktivvindue.Udvalg.ShapeRange.Juster msoaligncentre, ægte PP.Aktivvindue.Udvalg.ShapeRange.Juster msoAlignMiddles, sand næste Jeg ' hukommelse oprydning sæt PPSlide = intet sæt PPPres = intet sæt PP = Intetend Sub 

16.

tabeller placeres normalt i tekstdokumenter.

 Sub fremragende ord() 'Angiv reference til Microsoft-Ordobjektbibliotek 'Angiv dine variabler Dim MyRange som udmærke.Range Dim VD som ord.Ansøgning Dim vddoc som ord.Dokument Dim Vdrange Som Ord.Range ' Kopier de definerede områdeark ("Indtjeningstabel").Rækkevidde ("B4:F10").Cop ' Åbn målorddokumentsættet VD = nyt ord.Ansøgning sæt vddoc = VD.Dokument.Åben _ (Dettearbejdsbog.Sti && " PasteTable.dok") VD.Visible = True ' sæt fokus på målet bogmærke sæt Vdrange = vddoc.Bogmærker("Datatableher").Range ' Slet den gamle tabel og Indsæt Ny På fejl Genoptag næste område.Tabeller(1).Slet Vdrange.Indsæt 'Indsæt i tabellen' Juster kolonnebredder med rækkevidde.Tabeller(1).Kolonner.Setbredde _ (MyRange.Bredde / MyRange.Kolonner.Sæt bogmærket i igen.Bogmærke.Tilføj "DataTableHere", "Memory cleanup Set" = intet sæt "Nothing" = intet sæt "Nothingend" = intet sæt "NothingEnd" Sub

17. Uddrag et bestemt ord fra en celle

Vi kan bruge formler, hvis vi vil udtrække et bestemt antal symboler. Men hvad nu hvis vi kun vil udtrække det andet ord fra en sætning eller en række ord i en celle? For at gøre dette kan vi oprette en brugerdefineret funktion med VBA. Dette er en af de mest vigtige VBA-funktionaliteter, fordi det giver dig mulighed for at oprette dine egne funktioner, der ikke findes i MS. Lad os fortsætte med at oprette to funktioner: findord() og findordrev(). Her er VBA-koden til dette:

 funktion Findord(kilde som streng, Position som heltal) som streng på fejl Genoptag næste Findord = Split(Regnearkfunktion.Trim (kilde),"") (Position - 1) Ved fejl GoTo 0end FunctionFunction Findordrev(kilde som streng, Position som heltal) som streng Dim Arr () som streng Arr = VBA.Dele(Regnearkfunktion.Trim (kilde),"") ved fejl Genoptag næste Findordrev = Arr(UBound(Arr) - Position + 1) Ved fejl GoTo 0end-funktion

meget flot, vi har oprettet to cstom-funktioner. Prøv nu at bruge dem i . Funktionen = Findordrev (A1,1) tager det sidste ord fra celle A1. Funktionen = Findord (A1,3) tager det tredje ord fra celle A1 osv.

18. Beskyt din projektmappe

Nogle gange vil vi beskytte dataene i Vores fil, så kun vi kan ændre dem. Sådan gør du med VBA:

 Sub ProtectSheets() 'Angiv dine variabler Dim vs som regneark 'Start looping gennem alle regneark for hver vs I Aktivarbejdsbog.Regneark ' Beskyt og loop til næste regneark med.Beskyt adgangskode:= "1234" næste send Sub

Tillykke! Da du stadig læser dette, er du virkelig opsat på at lære VBA. Som du allerede har set for dig selv, er VBA-programmeringssproget ekstremt nyttigt og kan spare os meget tid. Jeg håber, at du fandt disse oplysninger nyttige og bruger dem til at blive en master i MS, VBA og computerprogram generelt.