Effektivt arbejde i MS Office. VBA Excel: eksempler på programmer
VBA betragtes som standard scriptsprog for Microsoft-applikationer og er nu inkluderet i alle Office-applikationer og endda ansøgninger fra andre virksomheder. Derfor, når du mestrer VBA til Excel, kan du straks gå videre til at oprette makroer til anden software. Microsoft produkter. Desuden vil du være i stand til at skabe fuldgyldige software produkter, samtidig ved at bruge funktionerne i en række forskellige applikationer.
Sådan aktiveres makroer i Excel
Som standard er fanen, der er ansvarlig for at administrere og navigere makroer i Excel, skjult. For at aktivere denne mulighed gå til fanen Fil til gruppen Muligheder. I den dialogboks, der vises MulighederExcel gå til fanen Tilpasning af bånd, i den højre kombinationsboks skal du placere en markør modsat fanen Udvikler. Disse handlinger er relevante for Excel-versioner 2010 og ældre.
Vises på båndet nyt indskud Udvikler med Excel automatiseringsstyringer.
Skrivning af makroer i Excel
I fanen Udvikler i gruppe Kode, klik på knappen Optag en makro. En dialogboks vises Optag en makro, som anmoder om nogle oplysninger om den fremtidige kode, der skrives. Hvis det er første gang, du opretter en makro, kan du blot klikke på knappen OKAY. MED i dette øjeblik Excel vil registrere hver brugerhandling i et VBA-modul, hvad enten det er dataindtastning, formatering eller oprettelse af diagrammer. Klik på knappen for at stoppe optagelsen af en makro Stop optagelsen som er i samme gruppe Kode.
Du kan også drage fordel af en alternativ mulighed for optagelse af makroer ved at bruge knappen Optag en makro, som er i nederste venstre hjørne arbejdsbog Excel (til højre for status Parat).
Nu kan du se en liste over alle oprettede makroer ved at klikke på knappen Makro, placeret i gruppen Kode. I dialogboksen, der vises, kan du give mere beskrivende navne til dine koder eller indstille tastaturgenveje, der ville køre en bestemt makro. Alternativ mulighed for at starte dette vindue er at trykke på Alt-tasterne+ F8.
Redigering af makroer
Tillykke! Du har skrevet din første makro. Det ville være logisk at kontrollere nu, hvilken kode Excel genererede til os. Den genererede kode er skrevet i VBA ( Visual Basic til applikationer). Du skal åbne den for at se den. RedaktørVB(VBE), som startes ved at trykke på Alt + F11 eller knappen VisuelGrundlæggende på fanen Udvikler.
For at undgå forvirring i editoren kan du kun arbejde med én fane i en projektmappe, et ark eller et modul. Sådan ser redaktøren ud i det virkelige liv.
jeg byder på på dette tidspunkt studere mere detaljeret forskellige vinduer og VBA editor menu. Dette vil hjælpe dig med at spare en masse tid i fremtiden.
For at se koden, klik på tråden Moduler i projektvinduet og dobbeltklik på den gren, der vises modul1 . Editoren åbner et vindue med koden, som vist på billedet.
Her kan du redigere den genererede kode, der blev skrevet, mens du arbejdede i Excel. For eksempel skal du udfylde en bestemt kolonne med værdier fra 1 til 10. Du har allerede de første tre trin, som indtaster værdierne 1, 2 og 3 i de første tre celler i kolonne A. Vi skal fuldfør de resterende syv trin.
Hvis du ser på koden ovenfor, vil du se, at makroen er struktureret på en bestemt måde. Programmet flytter først markøren til cellen ved hjælp af Range("A1").Select-kommandoen, og redigerer derefter dens indhold ved hjælp af ActiveCell.FormulaR1C1 = "1". Så for de resterende trin kan vi gentage disse trin, ændre celleadressen og den værdi, du vil skrive til den celle. For at indstille celle A4 til 4, ville du for eksempel skrive:
Range("A4"). Vælg |
Og gentag lignende trin for de resterende værdier.
Når du er færdig med at redigere, skal du gemme din bog. Du kan køre makroen ved at trykke på F5-knappen eller ved at vende tilbage til arbejdet Excel projektmappe, gå til fanen Udvikler til gruppen Kode -> Makroer og vælg fra listen den makro, du er interesseret i.
Brug et par minutter på omhyggeligt at studere koden, som Excel genererede. Hvis du er nybegynder, vil investere et par minutter i at lære koden give fantastiske resultater i at lære VBA-objekter at kende senere. Bemærk venligst, at det eksempel, vi diskuterede, kun er en illustration. Der er hurtigere og effektive måder opnå lignende resultater, som vi vil diskutere senere.
Forøg hastigheden af Excel-makroudførelse
Så langt så godt. Lad os se på et par tricks, der vil hjælpe med at fremskynde makroudførelsen. Lad os tage kodestykket ovenfor som et eksempel. Moderne computere vil arbejde på den pågældende kode så hurtigt, at du ikke engang bemærker det. Men hvad nu hvis du skal udføre operationen 50.000 gange. Dette vil tage noget tid. Hvis den makro, du skriver, er hundredvis af linjer lang, kan du fremskynde kodeudførelsen ved at trimme den del af processerne, der ikke bruges under makroudførelsen.
Brug af kommandoen Application.ScreenUpdating
Det første trick er at undgå at opdatere skærmen, mens makroen kører. Dette vil tillade Excel at gemme computerkraft computer og opdater først skærmen med de seneste værdier, efter at al koden er blevet udført. For at gøre dette skal du tilføje en kommando for at deaktivere skærmopdatering i begyndelsen af makroen og en kommando for at aktivere skærmopdatering i slutningen af makroen.
1 | Undermakro1() |
Application.ScreenUpdating-kommandoen fortæller Excel om at stoppe med at vise de genberegnet data på skærmen og returnere de færdige værdier ved slutningen af kodeudførelsen.
Brug af programkommandoen. Beregning
Det andet trick er at deaktivere automatiske beregninger. Lad mig forklare. Hver gang en bruger eller proces opdaterer en celle, forsøger Excel at genberegne alle de celler, der afhænger af den. Så lad os sige, at hvis cellen, som makroen forsøger at opdatere, påvirker 10.000 andre celler, vil Excel forsøge at genberegne dem alle, før koden afsluttes med at blive eksekveret. Følgelig, hvis der er hele linjen påvirker celler, kan genberegning betydeligt bremse kodeudførelsen. For at forhindre dette i at ske, kan du installere programkommandoen. Beregning i begyndelsen af koden, som vil skifte genberegning af formler til manuel tilstand og returner derefter den automatiske beregning i slutningen af makroen.
1 | Undermakro1() |
Vær forsigtig, glem ikke at skifte denne mulighed igen til automatisk tilstand i slutningen af makroen. Ellers skal du gøre dette i selve Excel ved at klikke på fanen Formler til gruppen Beregning og vælg Beregningsmuligheder –> Automatisk.
Undgå at vælge celler og områder
I tilstanden automatisk optagelse makroer, bemærker du måske, at Excel meget ofte bruger cellevalgskommandoen, for eksempel Range("A1"). Vælg. I vores eksempel brugte vi denne kommando gentagne gange for at vælge en celle og ændre dens værdi. Du kan undgå dette ved blot at angive celleadressen og give den den påkrævede værdi (Makroen registrerede markørens bevægelse fra en celle til en anden, og derfor indsatte disse trin. De er dog ikke nødvendige). Så en mere effektiv kode ville se sådan ud.
1 | Undermakro1() |
I I dette tilfælde vi refererede simpelthen til cellen og gav den den nødvendige værdi uden overhovedet at vælge den. Denne metode er hurtigere end den forrige.
Eksempler på Excel-makroer
Nedenfor er nogle eksempler på VBA-koder, der hjælper dig med at automatisere de mest almindelige opgaver.
Arbejde med bøger
Makro 1: Oprettelse af en ny projektmappe fra bunden
Makro 2. Gemmer en projektmappe, når en bestemt celle/område ændres
Makro 3. Gem projektmappen før lukning
Makro 4. Beskyttelse af et regneark i en projektmappe før lukning
Makro 5. Ophævelse af beskyttelsen af et ark, når du åbner en Excel-fil
Makro 6. Åbn en projektmappe på det ønskede ark
Makro 7. Åbning af en specifik brugerdefineret projektmappe
Makro 8. Afgør, om bogen er åben
Makro 9. Bestem, om en bog findes i en mappe
Makro 10. Opdater alle forbindelser i åbne projektmapper
Makro 11. Luk alle bøger på én gang
Makro 12. Åbn alle projektmapper i en mappe
Makro 13. Udskriv alle bøger i en mappe
Makro 14. Lad ikke bogen lukkes, før cellen er fyldt
Makro 15. Opret sikkerhedskopi nuværende bog med dags dato
Arbejde med ark
Makro 16. Tilføj et nyt regneark og tildel et navn
Makro 17. Slet alle ark undtagen det aktive
Makro 18. Skjul alt undtagen det aktive regneark
Makro 19. Vis alle ark i projektmappen
Makro 20. Flytteark
Makro 21. Sortering af ark efter navn
Makro 22. Gruppér ark efter etiketfarve
Makro 23. Kopier et ark til en ny projektmappe
Makro 24. Opret en ny projektmappe for hvert ark
Makro 25. Udskrivning af ark
Makro 26. Beskyt alle ark
Makro 27. Fjern beskyttelsen af alle ark
Makro 28. Oprettelse af en indholdsfortegnelse
Makro 29: Dobbeltklik for at zoome ind på et regneark
Makro 30. Vælg aktiv rækkekolonne
Valg og ændring af intervaller
Makro 31. Valg og formatering af et område
Makro 32. Oprettelse og valg af navngivne områder
Makro 33. Optælling ved hjælp af et antal celler
Makro 34. Valg og formatering af områder
Makro 35. Indsæt tomme linjer i rækken
Makro 36. Vis alle skjulte linjer og kolonner
Makro 37. Fjernelse af tomme linjer
Makro 38. Fjernelse af tomme kolonner
Makro 39. Valg og formatering af alle formler i projektmappen
Makro 40: Find og vælg den første tomme række eller kolonne
Makro 41. Anvend ekstra farve fylder
Makro 42. Sorter områder ved at dobbeltklikke
Makro 43. Begrænsning af rulleområdet i et bestemt område
Makro 44. Indstil automatisk udskriftsområdet for arket
Arbejde med data
Makro 45. Kopier og indsæt et område
Makro 46: Konverter alle formler i et interval til værdier
Makro 47. Transform tekstværdier til numerisk
Makro 48. Konvertering af en tankestreg til et minus
Makro 49. Slet ekstra pladser fra alle celler i området
Makro 50. Klip 5 tegn af til venstre i hver celle i området
Makro 51. Tilføj manglende nuller til cellen
Makro 52. Udskift tom celle nul
Makro 53. Tilføjelse af tekst til begyndelsen eller slutningen af en celle
Makro 54. Oprettelse af en datakonverteringsmakro
Makro 55. Ryd data (tegn, der ikke udskrives)
Makro 56. Vælg dubletter i dataområdet
Makro 57. Skjuler gentagne linjer
Makro 58. Skjul selektivt autofilterpilene
Makro 59. Kopier de filtrerede rækker til en ny projektmappe
Makro 60. Oprettelse af et nyt ark for hvert element i AutoFilter
Makro 61. Vis filtrerede kolonner i statuslinjen
Arbejde med pivottabeller
Makro 62: Skabelse bagudkompatibilitet drejetabel
Makro 63. Opdatering af alle pivottabeller i en bog
Makro 64. Oprettelse af en "opgørelse" af alle bogens pivottabeller
Makro 65. Opret alle pivottabeller ved hjælp af den samme datacache
Makro 66. Skjuler alt subtotaler i pivottabellen
Makro 67. Skift datanavnene på alle oversigtsfelter
Makro 68. Tvunget summering for alle oversigtsdata
Makro 69: Anvend talformat på alle dataelementer
Makro 70. Sortering af oversigtsfelter i alfabetisk rækkefølge
Makro 71. Anvend tilpasset sortering på dataelementer
Makro 72: Sætter beskyttelse på drejebordet
Makro 73: Anvend Pivot Field Constraints
Makro 74. Automatisk fjernelse ark med oversigtsdetaljer
Makro 75: Udskriv en pivottabel for hvert filterelement
Makro 76. Opret en ny fil for hvert filterelement
Makro 77. Forberedelse af et dataområde til en pivottabel
Arbejde med diagrammer og grafer
Makro 78. Ændring af størrelse på diagrammer på et regneark
Makro 79. Sammenkædning af diagrammet til et specifikt område
Makro 80: Oprettelse af et sæt usammenhængende diagrammer
Makro 81: Udskriv alle diagrammer på et regneark
Makro 82. At fejre det bedste og værste værdi på
Makro 83. Samme farver for værdier på forskellige diagrammer
Makro 84. Tilpasning af farven på diagrammer til farven på områder
Sende e-mails fra Excel
Makro 85. Sender en aktiv bog med posten (vedhæftet fil)
Makro 86: Sender en række værdier som en vedhæftet fil
Makro 87. Sender ét ark som vedhæftet fil
Makro 88. Send en e-mail med et link til vores filer
Makro 89: Sender e-mails, der tilføjer adresser til vores kontaktliste
Makro 90. Gem alle vedhæftede filer i en separat mappe
Makro 91. Gemmer visse investeringer til en mappe
Interoperabilitet med andre Office-applikationer
Makro 92. Kørsel af en adgangsanmodning fra Excel
Makro 96. Basiskomprimering Få adgang til data fra Excel
Makro 97. Sender Excel data til et Word-dokument
Makro 98. Lav en sammenlægning med et Word-dokument
Makro 99: Sender Excel-data til en PowerPoint-præsentation
Makro 100. Sender Excel-diagrammer i PowerPoint-præsentationer
Makro 101: Konvertering af en projektmappe til en PowerPoint-præsentation
Interoperabilitet med andre Office-applikationer
lære at arbejde med Word, Access og PowerPoint
Makro 92. Kørsel af en adgangsanmodning fra Excel
Makro 93. Kørsel af en Access-makro fra Excel
Makro 94. Åbning af en Access-rapport fra Excel
Makro 95. Åbning af en adgangsformular fra Excel
Makro til at fremhæve celle A1 på hvert ark i den aktive projektmappe. Dette får også skærmen til at bevæge sig.
Sub A1SelectionEachSheet() Dim i As Integer Application.ScreenUpdating = False For i = 1 To Sheets.Count Sheets(i).Vælg ActiveWindow.ScrollColumn = 1 ActiveWindow.ScrollRow = 1 Range("a1").Vælg Næste Sheets(1) .Vælg Application.ScreenUpdating = True End Sub
Makro til kopiering af det aktuelle ark et bestemt antal gange. Nyttigt til at teste nogle makroer - lavet redigeringer, kontrolleret dem på en kopi af dataene. Vi løb tør for kopier - kør makroen igen
Sub SimpleCopy() Dim i As Integer, j As Integer i = Application.InputBox("Indtast antallet af kopier af det aktuelle ark") Application.ScreenUpdating = Falsk For j = 1 Til i ActiveSheet.Copy after:=Sheets(Sheets .Count) ActiveSheet .Name = "Kopi" & j Næste j Application.ScreenUpdating = True End Sub
Opret ark med titler fra et bestemt område på et ark
Sub CreateFromList() Dim celle som område for hver celle i udvælgelsesark. Tilføj efter:=Ark(Ark.Antal) ActiveSheet.Name = cell.Value Næste celle End Sub
Markros for at sende et brev med forsinkelse. Modificeret makro fra John Walkenbachs bog Professional VBA Programming
Sub SendLetter() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon On Error GoTo cleanup Set OutMail = OutApp.CreateItem(0) On Error Resume Next With OutMail .To = " [e-mail beskyttet]" .Subject = "Salgsrapport" .Vedhæftede filer. Tilføj "C:\Test.txt" .Body = "E-mail-tekst" .DeferredDeliveryTime = Erstat(Dato, ".", "/") & " 11:00:00 " .send ".Display for at generere et brev og åbne det End With On Error GoTo 0 Set OutMail = Intet oprydning: Set OutApp = Intet End Sub
Lidt ændret indholdsfortegnelse makro fra Nikolai Pavlov.
Hvis en "Indholdsfortegnelse"-ark allerede findes i bogen, beder makroen dig om at slette den. Hvis ikke, opretter du et "Indholdsfortegnelse"-ark og indsætter links med arknavne
Sub TableOfContent() Dim ark Som Arbejdsark Dim celle Som Range Dim Answer As Integer Application.ScreenUpdating = Falsk Med ActiveWorkbook For hvert regneark i ActiveWorkbook.Worksheets Hvis Worksheet.Name = "Indholdsfortegnelse" Så Answer = MsgBox("Projektmappen har en ark med navnet Indholdsfortegnelse. Slet det?", vbYesNo) If Answer = vbNo Then Exit Sub If Answer = vbYes Then Application.DisplayAlerts = False Worksheet.Delete Application.DisplayAlerts = True End If End If Next End With Sheets(Array (1)).Vælg ark.Tilføj ark(1).Navn = "Indholdsfortegnelse" Med ActiveWorkbook For hvert ark i ActiveWorkbook.Worksheets If sheet.Name<>"Indholdsfortegnelse" Indstil derefter celle = Arbejdsark(1).Cells(ark.Index, 1) .Arbejdsark(1).Hyperlinks.Tilføj anker:=celle, Adresse:="", Underadresse:=""" & ark .Navn & """ & "!A1" celle.Formel = ark.Navn Slut hvis næste ark slutter med rækker("1:1").Slet Application.ScreenUpdating = True End Sub
Sortering af ark fra VBA Wizards. Makroen sorterer også skjulte ark. Virker ikke, hvis bogen har en struktur beskyttet
Sub SORT_ALL_SHEETS() Application.ScreenUpdating = Falsk: Application.EnableEvents = Falsk Dim iSht As Worksheet, oDict As Object, i%, j% Indstil oDict = CreateObject("Scripting.Dictionary") " husk synlighedstilstanden for hvert ark og gør det alt synligt for hver iSht i ActiveWorkbook.Sheets oDict.Item(iSht.Name) = iSht.Visible: iSht.Visible = True Next With ActiveWorkbook " sortering af synlige ark For i = 1 Til .Sheets.Count - 1 For j = i + 1 Til .Sheets.Count If UCase(.Sheets(i).Name) > UCase(.Sheets(j).Name) Then .Sheets(j).Move Before:=.Sheets(i) Next j Next i End With "gendan den oprindelige tilstand synligheden af hvert ark For hver iSht i ActiveWorkbook.Sheets iSht.Visible = oDict.Item(iSht.Name) Næste Application.EnableEvents = True: Application.ScreenUpdating = True End Sub
Importer kolonnerne "Felt1" og "Felt2" fra arket "Ark1" Excel-fil"C:\Manager.xls" via ADODB-forbindelse og indsættelse af indhold fra celle A1 i det aktuelle ark
Selvom du ikke ved noget om VBA og makroer, vil du blive inspireret til at lære mere efter at have læst nogle af tricks og tricks i denne del. Hvis du lige er begyndt at arbejde med makroer, kan du finde nyttig information her.
. .
I tidligere artikler så du, hvordan Excel kan administrere andre Microsoft applikationer såsom Word og Outlook. Men samtidig kan andre applikationer også bruges til at administrere Excel. For eksempel skrev du en makro til Word eller Access, der opretter en Excel-tabel, fylder den med data og derefter gemmer den. Excel behøver ikke nødvendigvis at [...]
. .
Du kan bruge denne teknologi til Microsoft ledelse Outlook og send e-mails direkte fra dit regneark eller kopier dine notesbogsposter. Selvfølgelig, Excel funktioner er sådan, at men e-mail tabellen vil blive sendt, men denne metode giver dig kun mulighed for at sende en del af tabellen. For at denne kode kan fungere, skal du have […]
. .
Denne metode kan være meget nyttig, hvis du for eksempel har standard dokument med tabeller fyldt med datamakroer fra Excel tabeller. Du kan køre makroen, og dataene vil blive overført til tabellerne i Word dokument. For nylig havde jeg til opgave at skrive et program til at udfylde en rapport om SLA (Service Level Agreement). Rapporter […]
. .
Makroer bruges ofte til at automatisere driften af applikationer. Enhver makro er en sekvens af handlinger optaget under et bestemt navn. Hvis når man arbejder med Microsoft Excel der er behov for at udføre den samme sekvens af operationer flere gange (f.eks. kompleks formatering af den aktuelle celle eller tilføjelse ny linje ved at fylde nogle af sine celler med formler), så kan du skrive disse handlinger ned, og [...]
Objekter såsom makroer i Excel hjælper dig med at organisere arbejdsprocessen i Excel korrekt.
Lad os se nærmere på alle funktionerne ved at arbejde med disse objekter i MS Office-softwarepakken.
Takket være brugen af makroer kan hver celle i dit dokument automatiseres. Dette sker ved at give brugeren mulighed for at registrere alle handlinger, efterhånden som de oprettes.
Hvad er makroer, og hvorfor er de nødvendige?
Du kan arbejde med makroer i alle MS Office-programmerne. Først og fremmest er de nødvendige for at kunne organisere brugerens arbejde i programmet kompetent.
De er nødvendige for ikke at udføre den samme type opgaver og handlinger flere dusin gange.
Deres oprettelse og brug vil bidrage til betydeligt at spare tid og maksimere automatisering af robotten i programmet.
Vigtig! Takket være den oprettede makro kan du simpelthen få arbejdet gjort med det samme, mens det ville tage meget tid at gøre det manuelt.
Dens krop består i det væsentlige af instruktioner, der fortæller programmet, hvad det skal gøre, hvis brugerne vælger det ene eller det andet.
Du kan også støde på begrebet makro i Word, men i Excel har det flere fordele:
- For det første kombinerer den alle instruktioner i ét komplet eksekveringsscript, som giver dig mulighed for at optimere belastningen på programmet og begynde at arbejde hurtigere;
- Du kan kalde det ved at bruge en tast på værktøjslinjen eller ved at bruge en speciel tastekombination. Dette vil tillade brugeren ikke at slå op fra tastaturet, mens han arbejder;
Vi noterer os yderligere to artikler, der evtdu får brug for:
Oprettelse af dine egne makroer i Excel 2010, 2007, 2013
Lad os se nærmere på eksempler på oprettelse i Excel 2007, 2013:
- Åbn det dokument, du arbejder med, og som du vil oprette en makro til. Forresten skal hver celle, som en handling udføres på, udarbejdes;
- Vis udviklerfanen på båndet. For at gøre dette skal du åbne menupunktet "Filer" og åbne mulighederne som vist på figuren;
- Vælg derefter båndindstillingen og tilføj udviklervinduet til listen over hovedvinduer, som vist på billedet nedenfor;
- Nu kan du gå direkte videre til at oprette selve brugermakroen.
Efter dens oprettelse vil hver celle blive automatiseret - det betyder, at enhver celle i brugerdokumentet vil udføre den samme type handling, som brugeren angiver; - Find i udviklerfanen speciel nøgle til at skabe. Dens placering er vist i figuren nedenfor;
- Tryk på tasten. Et oprettelsesvindue vises, hvor du skal angive et navn og en tastekombination, som den vil blive aktiveret med. Du kan også tilføje en kort beskrivelse af, hvordan makroen fungerer.
Dette skal gøres, hvis du har for mange af dem, for ikke at blive forvirret;
- Klik derefter på OK. Vinduet lukkes, og optagelsesprocessen begynder. For at stoppe optagelsen skal du trykke på den tilsvarende tast på kontrolpanelet;
- Begynd nu at udføre de handlinger, der vil blive optaget i makroen. Hver celle kan fyldes med bestemte data.
Du kan også arbejde med kun én celle, efter optagelse og aktivering af makroen vil den samme celle blive optaget i henhold til den specificerede algoritme; - Glem ikke at trykke på knappen Stop dataoptagelse. Efter at have gennemført alle ovenstående trin, vil det blive optaget og gemt i programmet.
Sådan aktiverer og arbejder du med makroer i Excel
For at gøre dette følg instruktionerne nedenfor:
- På udviklerfanen skal du finde en knap kaldet Makroer. Klik på den;
- Vælg den makro, du har brug for, fra listen, og klik på knappen "Kør";
- Du kan også køre den påkrævede makro ved hjælp af tastaturgenvejen, som blev angivet af brugeren på indledende fase dens skabelse;
- Efter at have klikket på udfør-knappen, vil alle handlinger, der blev udført under optagelsen, blive udført igen.
Makroer er mest praktiske at bruge, når specifik celle kræver gentagen kopiering.