Spela in en makro i Excel - TechTV-artiklar

Det här är mina shownoteringar från mitt framträdande i avsnitt 33 av Call for Help på TechTV Canada.

VBA Macro Recorder

Varje exemplar av Excel som sålts sedan 1995 innehåller den otroligt kraftfulla Visual Basic for Applications (VBA) som gömmer sig bakom cellerna. Om du har Excel har du VBA.

VBA låter dig automatisera allt du kan göra i Excel, plus göra fler saker som vanligtvis inte är möjliga i Excel.

Lyckligtvis för oss inkluderade Microsoft en makroinspelare med Excel. Detta låter dig skriva VBA-kod utan att vara programmerare. Tyvärr är Macro Recorder felaktig. Det kommer inte att producera kod som fungerar varje gång. Makroinspelaren kommer nära dig, men i många fall måste du fixa koden lite för att den ska fungera tillförlitligt. I andra fall måste du veta när du ska använda relativ inspelning för att få makrot att fungera.

Tracy Syrstad och jag skrev VBA och makron Microsoft Excel 2016 för att hjälpa dig att förstå begränsningarna för inspelad kod och för att lära dig hur du fixar inspelad kod till något som fungerar varje gång.

På showen demonstrerade jag processen att spela in ett makro som fungerar perfekt om du vet hur du använder Relativ referensknapp. Någon hade gett mig ett Excel-kalkylblad med hundratals namn och adresser. De ville ha postetiketter. Om du någonsin har använt Microsoft Word-kopplingsfunktionen vet du att du behöver varje fält i en ny kolumn i kalkylbladet. Istället hade detta specifika kalkylblad data som går ner i kolumn A.

Processen för att fixa en enda etikett är ganska tråkig.

Börja med cellpekaren på namnet i kolumn A.
Tryck på nedåtpilen för att flytta till adressen
Ctrl + x för att klippa
Uppåtpil, högerpil för att flytta till kolumn B bredvid namnet
Ctrl + v för att klistra in
Nedåtpil två gånger, vänsterpil en gång för att flytta till staden
Ctrl + x för att klippa
Uppåt två gånger, högerpil tre gånger
Ctrl + v för att klistra in
Vänsterpil två gånger, nedåtpil en gång för att flytta till den nu tomma raden.
Håll ned shift-tangenten medan du trycker på nedåtpilen två gånger
Alt + edr för att radera de tomma raderna
Uppåtpil och nedåtpil för att bara välja namnet igen.

Här är animationen som visar dessa steg:

Konfigurera Excel för att tillåta makron

Även om varje kopia av Excel innehåller VBA, stänger Microsoft som standard av möjligheten att köra makron. Du måste göra en engångsjustering så att makron kan köras. Öppna Excel. Välj Verktyg> Makro> Säkerhet på menyn. Om din makrosäkerhetsnivå för närvarande är hög, ändrar du den till Medium.

Förbereder för inspelning av makroen

Tänk på de steg som krävs för att uppnå uppgiften. Du vill se till att du börjar med cellpekaren på ett namn och slutar med det i nästa namn. Detta gör att du flera gånger kan köra makrot. När du har stegen redo att gå, slå på makroinspelaren. Från menyn väljer du Verktyg> Makro> Spela in ny makro.

Om du kommer att köra detta makro i många dagar, bör du ge makrot ett användbart namn. Om det är ett engångsmakro att automatisera en engångsuppgift, är det antagligen bra att lämna makronamnet som Macro1. Det viktiga fältet här är genvägsfältet. Om du skapar ett makro för engångsanvändning, tilldela makrot till en genvägstangent som Ctrl + a - det är ganska lätt att slå Ctrl + a upprepade gånger eftersom tangenterna ligger nära varandra. Om du ska skapa ett makro som du kommer att använda varje dag, vill du tilldela makrot till en tangent som inte redan är en viktig genvägskombination. Ctrl + j eller Ctrl + k är bra val.

Engångsmakron bör lagras i ThisWorkbook. Om du behöver använda makrot upprepade gånger på många olika arbetsböcker kan du lagra makrot i Personal Macro Workbook.

Du får nu det minsta verktygsfältet i hela Excel; verktygsfältet Stoppa inspelning. Den har bara två ikoner och ser ut så här:

Om detta verktygsfält kommer i vägen, tryck inte på X för att stänga det. Ta istället det blå fältet och dra verktygsfältet till en ny plats. Handlingen att flytta verktygsfältet registreras inte i makrot. Om du av misstag stänger verktygsfältet får du tillbaka det med Visa> Verktygsfält> Stoppa inspelning. Denna åtgärd kommer dock att spelas in.

Den första knappen i verktygsfältet är "Stoppa inspelning" -knappen. Detta är självförklarande. När du är klar med inspelningen av makrot trycker du på verktygsfältet stoppa inspelning.

Den viktigare (och sällan förstås) knappen är "Relativ referens" -knappen.

I vårt nuvarande exempel måste du trycka på knappen Relativ referens innan du börjar. Om du spelar in ett makro med relativa referenser aktiverade registrerar Excel steg så här:

Flytta ner en cell
Klipp av den aktuella cellen
Flytta en cell uppåt
Flytta en cell åt höger
Klistra
etc.

Om du istället spelar in makrot utan relativa referenser, registrerar makrot följande steg:

Gå till cell A4
Klipp cell A4
Flytta till cell A3
Gå till cell B3
Klistra in i cell B3
etc.

Detta skulle vara väldigt fel - vi behöver makrot för att arbeta på celler som är 1 och 2 celler från startpunkten för makrot. När du kör makrot om och om igen kommer startpunkten att vara rad 4, rad 5, rad 6 etc. Om du spelar in makrot utan att relativa referenser är aktiverade skulle makrot alltid köras på rad 3 som startpunkt.

Följ dessa steg:

Välj relativ referensknapp i verktygsfältet Stoppa inspelning
Cellpekaren bör redan ha ett namn i kolumn A.
Tryck på nedåtpilen för att flytta till adressen
Ctrl + x för att klippa
Uppåtpil, högerpil för att flytta till kolumn B bredvid namnet
Ctrl + v för att klistra in
Nedåtpil två gånger, vänsterpil en gång för att flytta till staden
Ctrl + x för att klippa
Uppåt två gånger, högerpil tre gånger
Ctrl + v för att klistra in
Vänsterpil två gånger, nedåtpil en gång för att flytta till den nu tomma raden.
Håll ned shift-tangenten medan du trycker på nedåtpilen två gånger
Alt + edr för att radera de tomma raderna
Uppåtpil och nedåtpil för att välja nästa namn i listan.
Klicka på verktygsfältet Stoppa inspelning
Spara arbetsboken
Testa makrot genom att trycka på kortkommandot ovan. Det borde perfekt fixa nästa namn i listan

När du väl har sett att makrot fungerar som önskat kan du hålla ned Ctrl + a för att snabbt fixa några namn per sekund. Hela listan med 500 namn kan fixas på en minut eller två.

Bonustips - Inte på showen

Du kan enkelt slå in makrot i en enkel slinga för att få det att fixa alla 500 namn utan att du behöver slå Ctrl + flera hundra gånger.

Tryck på Alt + F11-tangenten för att öppna makroredigeraren.

Om du inte ser Project - VBAProject-rutan, tryck Ctrl + r.

Högerklicka på Module1 och välj Visa kod. Du kommer att se kod som ser ut så här:

Nu behöver du inte förstå vad den här koden gör, men du vet att vi vill köra allt i det makrot en gång för varje namn som vi har. Om du vet att det finns 462 namn kan du lägga till två rader för att köra koden 462 gånger. Överst i makrot, infoga en ny rad med orden " For i = 1 to 462". Längst ner i makrot sätter du in en rad som säger " Next i". Detta berättar för VBA att köra koden 462 gånger.

Slutsats

Efter detta enkla makro visade jag ett exempel på showen av ett mycket komplext makro. Detta makro skapade pivottabeller och diagram för 46 avdelningar i ett företag. Denna rapport brukade ta 40 timmar varje månad. VBA-makrot körs nu och skapar alla 46 rapporter på mindre än 2 minuter.

Boken VBA och makron Microsoft Excel 2016 leder dig upp i inlärningskurvan så att du kan komma från att spela in enkla makron som den här till att köra mycket komplexa rapporter som kan spara hundratals timmar per år. Naturligtvis, om du inte vill lära dig VBA, anställ en Excel-konsult för att utforma en rapport åt dig.