Spela in Ändra Kör Excel-makro - Excel-tips

Detta är det 19: e veckotipset för Excel på.com. Många av Excel-tipsen involverar någon form av makrotrick. Den här veckan, för Excel-användare som aldrig har skrivit ett makro, erbjuder jag en grundfärg för hur man spelar in och anpassar sedan ett användbart Excel-makro.

Exempel på adressdata

Låt oss säga att du har 400 rader adressdata som visas i den övre siffran till vänster. Namnfältet finns i kolumn A, gatuadressen i kolumn B och staden i kolumn C.

Ditt mål är att konvertera data till en enda kolumn som visas i den andra figuren.

Detta enkla problem kommer att användas för att illustrera hur man spelar in, modifierar och sedan kör ett enkelt makro.

För Excel 95-användare: När du har spelat in makrot lägger Excel ditt makro på ett ark som heter Module1 i din arbetsbok. Du kan bara klicka på arket för att komma åt makrot.

Även om det finns 400 poster i det här kalkylbladet vill jag spela in en liten bit av makrot som tar hand om bara den första adressen. Makrot antar att cellpekaren är på förnamnet. Den infogar tre tomma rader. Den kopierar cellen till höger om originalcellen till cellen under den ursprungliga cellen. Det kommer att kopiera stadscellen till cellen 2 rader under den ursprungliga cellen. Den ska sedan flytta cellpekaren nedåt så att den står i nästa namn.

Nyckeln är att tänka igenom den här processen innan du spelar in den. Du vill inte göra många misstag när du spelar in makrot.

Så placera din cellpekare i cell A1. Gå till menyn och välj Verktyg> Makro> Spela in ny makro. Dialogrutan Spela in makro föreslår namnet på Macro1. Det här är bra, så tryck OK.

Excel-makroinspelaren har en mycket dum standardinställning som du absolut måste ändra för att detta makro ska fungera. I Excel 95, gå till Verktyg> Makro> Använd relativa referenser I Excel 97-2003 klickar du på den andra ikonen i verktygsfältet Stoppa inspelning. Ikonen ser ut som ett litet kalkylblad. En röd cell i C3 pekar mot en annan röd cell i A3. Ikonen heter Relativ referens. När den här ikonen är "på" finns det en viss färg som omger ikonen. Ikonen kommer ihåg den senaste inställningen från den aktuella Excel-sessionen, så du kan behöva klicka på den ett par gånger för att ta reda på vilken metod som är på eller inte. I Excel 2007 använder du Visa - Makron - Använd relativa referenser.

OK, vi är redo att gå. Följ dessa steg:

Tryck på nedåtpilen en gång för att flytta till cell B1.
Håll ned shift-tangenten och tryck nedåtpilen två gånger för att välja raderna 2, 3 och 4
Från menyn väljer du Infoga och sedan Rader för att infoga tre tomma rader.
Tryck på uppåtpilen och sedan högerpilen för att flytta till cell B2.
Tryck Ctrl X för att klippa cell B2.
Tryck på nedåtpilen, vänsterpilen och sedan Ctrl V för att klistra in i cell A2.
Slå upp pil, högerpil, högerpil, Ctrl X, vänsterpil, vänsterpil, nedåtpil, nedåtpil, Ctrl V för att flytta C1 till A3.
Tryck på nedåtpilen två gånger så att cellpekaren nu står på nästa namn i rad A5.
Klicka på ikonen "Stoppa inspelning" i verktygsfältet för att stoppa inspelningen av makrot.

Du har spelat in ditt första makro. Låt oss ta en titt. Gå till Verktyg> Makro> Makron. I listan markerar du Makro1 och trycker på knappen Redigera. Du borde se något som ser ut så här.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Hej, om du inte är programmerare ser det förmodligen ganska skrämmande ut. Låt det inte vara. Om det finns något du inte förstår finns det utmärkt hjälp. Klicka på markören någonstans i nyckelordet Offset och tryck på F1. Förutsatt att du installerade VBA-hjälpfilen ser du hjälpavsnittet för nyckelordet Offset. Hjälpen berättar syntaxen för uttalandet. Det står att det är Offset (RowOffset, ColumnOffset). Fortfarande inte särskilt tydlig? Leta efter det gröna understrukna ordet "exempel" nära toppen av hjälpen. Excels VBA-exempel låter dig lära dig vad som händer. I exemplet med Offset står det att aktivera cellen två rader nedan och tre rader till höger om den aktuella cellen, skulle du använda:

ActiveCell.Offset(3, 2).Activate

OK, så det är en ledtråd. Offset-funktionen är ett sätt att flytta runt Excel-kalkylbladet. Med tanke på den här informationen kan du se vad makrot gör. Den första förskjutningen (1, 0) är där vi flyttade cellpekaren ner till A2. Nästa förskjutning är där vi flyttade upp en rad (-1 rader) och över en kolumn. Du kanske inte förstår något annat i makrot, men det är ändå användbart.

Gå tillbaka till Excel-kalkylbladet. Sätt din cellpekare i cell A5. Välj Verktyg> Makro> Makron> Makro1> Kör. Makrot körs och din andra adress formateras.

Du kanske säger att det är svårare att välja hela denna långa stora rad med kommandon än att bara formatera för hand. OK, gör sedan Verktyg> Makro> Makron> Alternativ. I genvägsrutan, säg Ctrl + w är genvägstangenten för detta makro. Klicka på OK och avvisa sedan dialogrutan Makro med Avbryt. Nu när du trycker på Ctrl w körs makrot. Du kan formatera en adress i ett enda tangenttryckning.

Är du redo för den stora tiden? Hur många adresser har du kvar? Jag slog Ctrl wa några gånger, så jag har 395 kvar. Gå tillbaka till ditt makro. Vi ska sätta hela makrokoden i en slinga. Infoga en ny rad som säger "Gör tills activecell.value =" "" före den första raden med makrokod. Infoga en rad som säger "Loop" före End Sub-raden. Do-slingan utför allt mellan Do- och Loop-raden tills den går in i en tom linje. Makronet ser nu ut så här:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Gå tillbaka till ditt Excel-ark. Sätt cellpekaren på nästa namn. Klicka på Ctrl w och makrot formaterar alla dina poster på några sekunder.

Författarna till Excel-böcker säger att du inte kan göra något användbart genom att spela in ett makro. Inte sant! För den person som skulle behöva klippa och klistra in 800 gånger är detta makro mycket användbart. Det tog några minuter att spela in och anpassa. Ja, professionella programmerare kommer att påpeka att koden är fruktansvärt ineffektiv. Excel placerar en hel massa saker där som de inte behöver lägga in där. Ja, om du visste vad du gör kan du utföra samma uppgift med halva raderna som körs på 1,2 sekunder istället för 3 sekunder. ÄN SEN DÅ? 3 sekunder är mycket snabbare än de 30 minuter som uppgiften skulle ha tagit.

Några fler tips för början makroinspelare:

Apostrofen används anger en kommentar. Allt efter apostrof ignoreras av VBA
Detta är objektorienterad programmering. Den grundläggande syntaxen är object.action. Om en objektorienterad kompilator spelade fotboll skulle det sägas "ball.kick" för att sparka bollen. Så "Selection.Cut" säger att göra en "redigera> klipp" på det aktuella valet.
I exemplet ovan är Range-modifierarna relativt den aktiva cellen. Om den aktiva cellen finns i B2 och du säger "ActiveCell.Range (" A1: C3 "). Välj", sedan markerar du kolumnområdet 3 rad med 3 med början i cell B2. Med andra ord väljer du B2: D4. Att säga "ActiveCell.Range (" A1 ")" säger för att välja 1 x 1 cellintervall som börjar med den aktiva cellen. Detta är otroligt överflödigt. Det motsvarar att säga "ActiveCell.Select".
Spara din arbetsbok innan du kör ett makro för första gången. På det här sättet, om det har ett fel och gör något oväntat, kan du stänga utan att spara och återgå till den sparade versionen.

Förhoppningsvis kommer detta enkla exempel att ge dig nybörjare makroinspelare modet att spela in ett enkelt makro nästa gång du har en återkommande uppgift att utföra i Excel.