Dette er mine shownotater fra mit optræden i afsnit 33 af Call for Help på TechTV Canada.
VBA-makrooptager
Hver kopi af Excel, der er solgt siden 1995, inkluderer den utroligt kraftfulde Visual Basic for Applications (VBA), der gemmer sig bag cellerne. Hvis du har Excel, har du VBA.
VBA giver dig mulighed for at automatisere alt, hvad du kan gøre i Excel, plus gøre flere ting, der ikke generelt er mulige i Excel.
Heldigvis for os inkluderede Microsoft en makrooptager med Excel. Dette giver dig mulighed for at skrive VBA-kode uden at være programmerer. Desværre er Macro Recorder fejlbehæftet. Det producerer ikke kode, der fungerer hver gang. Makrooptageren kommer dig tæt, men i mange tilfælde skal du rette op på koden for at få den pålideligt til at fungere. I andre tilfælde skal du vide, hvornår du skal bruge relativ optagelse for at få makroen til at fungere.
Tracy Syrstad og jeg skrev VBA og makroer Microsoft Excel 2016 for at hjælpe dig med at forstå begrænsningerne ved registreret kode og lære dig at rette optaget kode til noget, der fungerer hver gang.
På showet demonstrerede jeg processen med at optage en makro, der fungerer perfekt, hvis du ved, hvordan du bruger knappen Relativ reference. Nogen havde givet mig et Excel-regneark med hundredvis af navne og adresser. De ønskede postetiketter. Hvis du nogensinde har brugt Microsoft Word-flettefunktionen, ved du, at du har brug for hvert felt i en ny kolonne i regnearket. I stedet for havde dette særlige regneark dataene ned i kolonne A.
Processen til at rette en enkelt etiket er ret kedelig.
- Start med cellemarkøren på navnet i kolonne A.
- Tryk på pil ned for at flytte til adressen
- Ctrl + x for at klippe
- Pil op, højre pil for at flytte til kolonne B ved siden af navnet
- Ctrl + v for at indsætte
- Pil ned to gange, venstre pil én gang for at flytte til byen
- Ctrl + x for at klippe
- Pil op to gange, højre pil tre gange
- Ctrl + v for at indsætte
- Venstre pil to gange, pil ned én gang for at flytte til den nu tomme række.
- Hold Skift-tasten nede, mens du rammer pil ned to gange
- Alt + edr for at slette de tomme rækker
- Pil op og pil ned for kun at vælge navnet igen.
Her er animationen, der viser disse trin:
Opsætning af Excel til at tillade makroer
Selvom hver kopi af Excel indeholder VBA, deaktiverer Microsoft som standard muligheden for at køre makroer. Du skal foretage en engangsjustering for at lade makroer køre. Åbn Excel. Fra menuen skal du vælge Værktøjer> Makro> Sikkerhed. Hvis dit makrosikkerhedsniveau i øjeblikket er indstillet til højt, skal du ændre det til Medium.
Forberedelse til optagelse af makroen
Tænk på de nødvendige trin for at nå opgaven. Du vil sikre dig, at du starter med cellemarkøren på et navn og slutter med det på det næste navn. Dette giver dig mulighed for gentagne gange at køre makroen mange gange. Når du har trinene klar, skal du tænde for makrooptageren. Fra menuen skal du vælge Værktøjer> Makro> Optag ny makro.
Hvis du vil køre denne makro i mange dage, skal du give makroen et nyttigt navn. Hvis det er en engangsmakro at automatisere en engangsopgave, er det sandsynligvis fint at lade makronavnet være Macro1. Det vigtige felt her er genvejstastfeltet. Hvis du opretter en makro til engangsbrug, skal du tildele makroen til en genvejstast som Ctrl + a - det er ret nemt at trykke Ctrl + a gentagne gange, da tasterne er tæt på hinanden. Hvis du vil oprette en makro, som du vil bruge hver dag, vil du tildele makroen til en nøgle, der ikke allerede er en vigtig genvejstastkombination. Ctrl + j eller Ctrl + k er gode valg.
Engangsmakroer skal gemmes i ThisWorkbook. Hvis du har brug for at bruge makroen gentagne gange på mange forskellige projektmapper, kan du gemme makroen i den personlige makro-projektmappe.
Du får nu den mindste værktøjslinje i hele Excel; værktøjslinjen Stop optagelse. Det har kun to ikoner og ser sådan ud:
Hvis denne værktøjslinje kommer i vejen, skal du ikke trykke på X for at lukke den. Grib i stedet den blå bjælke og træk værktøjslinjen til en ny placering. Handlingen med at flytte værktøjslinjen registreres ikke i makroen. Hvis du ved et uheld lukker værktøjslinjen, skal du få den tilbage med Vis> Værktøjslinjer> Stop optagelse. Denne handling registreres dog.
Den første knap på værktøjslinjen er knappen "Stop optagelse". Dette er selvforklarende. Når du er færdig med at optage makroen, skal du trykke på værktøjslinjen stopoptagelse.
Den mere vigtige (og sjældent forståede) knap er knappen "Relativ reference".
I vores nuværende eksempel skal du trykke på knappen Relativ reference, før du begynder. Hvis du optager en makro med relative referencer slået til, registrerer Excel trin som dette:
- Flyt en celle ned
- Klip den aktuelle celle
- Flyt en celle op
- Flyt en celle til højre
- sæt ind
- etc.
Hvis du i stedet vil optage makroen uden relative referencer, registrerer makroen disse trin:
- Gå til celle A4
- Klip celle A4
- Gå til celle A3
- Gå til celle B3
- Indsæt i celle B3
- etc.
Dette ville være forfærdeligt forkert - vi har brug for makroen til at arbejde på celler, der er 1 og 2 celler fra startpunktet for makroen. Når du kører makroen igen og igen, vil startpunktet være række 4, række 5, række 6 osv. Optagelse af makroen uden relative referencer er slået til, vil have, at makroen altid kører på række 3 som udgangspunkt.
Følg disse trin:
- Vælg den relative referenceknap på værktøjslinjen Stop optagelse
- Cellemarkøren skal allerede være på et navn i kolonne A.
- Tryk på pil ned for at flytte til adressen
- Ctrl + x for at klippe
- Pil op, højre pil for at flytte til kolonne B ved siden af navnet
- Ctrl + v for at indsætte
- Pil ned to gange, venstre pil én gang for at flytte til byen
- Ctrl + x for at klippe
- Pil op to gange, højre pil tre gange
- Ctrl + v for at indsætte
- Venstre pil to gange, pil ned én gang for at flytte til den nu tomme række.
- Hold Skift-tasten nede, mens du rammer pil ned to gange
- Alt + edr for at slette de tomme rækker
- Pil op og pil ned for at vælge det næste navn på listen.
- Tryk på værktøjslinjen Stop optagelse
- Gem projektmappen
- Test makroen ved at trykke på genvejstasten tildelt ovenfor. Det skal perfekt rette det næste navn på listen
Når du først ser, at makroen fungerer som ønsket, kan du holde Ctrl + a nede for hurtigt at rette et par navne pr. Sekund. Hele listen med 500 navne kan rettes på et minut eller to.
Bonustip - Ikke på showet
Du kan nemt pakke makroen i en simpel sløjfe for at få den til at rette alle 500 navne uden at du behøver at trykke Ctrl + flere hundrede gange.
Tryk på Alt + F11-tasten for at åbne makroeditoren.
Hvis du ikke kan se Project - VBAProject-ruden, skal du trykke på Ctrl + r.
Højreklik på Module1, og vælg Vis kode. Du vil se kode, der ser sådan ud:
Nu behøver du ikke forstå, hvad denne kode gør, men du ved, at vi vil køre alt i denne makro en gang for hvert navn, vi har. Hvis du ved, at der er 462 navne, kan du tilføje 2 linjer for at køre koden 462 gange. Indsæt en ny linje med ordene " For i = 1 to 462" øverst i makroen . Indsæt en linje, der siger " Next i" nederst i makroen . Dette fortæller VBA at køre koden 462 gange.
Konklusion
Efter denne enkle makro viste jeg et eksempel på showet med en meget kompleks makro. Denne makro oprettede pivottabeller og diagrammer til 46 afdelinger i en virksomhed. Denne rapport tog normalt 40 timer hver måned. VBA-makroen kører nu og opretter alle 46 rapporter på mindre end 2 minutter.
Bogen VBA og makroer Microsoft Excel 2016 fører dig op i indlæringskurven, så du kan komme fra at optage enkle makroer som denne til at køre meget komplekse rapporter, der kan spare dig hundreder af timer om året. Selvfølgelig, hvis du ikke vil lære VBA, skal du leje en Excel-konsulent til at designe en rapport til dig.
