10 Sätt att öka dina makroner - dummies

Eftersom dina Excel-makron blir alltmer robusta och komplexa kan du upptäcka att de förlorar prestanda. När man diskuterar makron är ordet prestanda vanligtvis synonymt med hastighet . Hastigheten är hur snabbt dina VBA-procedurer utför sina avsedda uppgifter. Följande är tio sätt att hjälpa till att hålla dina Excel-makronar på sin optimala prestationsnivå.

Avslutande arkberäkningar

Visste du att varje gång en cell som påverkar någon formel i ditt kalkylblad ändras eller manipuleras, räknar Excel om hela kalkylbladet? I arbetsblad som har en stor mängd formler kan detta beteende drastiskt sakta ner dina makron.

Du kan använda programmet. Beräkningsegenskaper för att berätta för Excel att växla till manuell beräkningsläge. När en arbetsbok är i manuell beräkningsläge, beräknas arbetsboken inte förrän du uttryckligen utlöser en beräkning genom att trycka på F9-tangenten.

Placera Excel i manuell beräkningsläge, kör koden och växla sedan tillbaka till automatisk beräkningsläge.

Sub Macro1 () Application. Beräkning = xlCalculationManual 'Placera din makrokod här Application. Beräkning = xlCalculationAutomatic End Sub

Inställning av beräkningsläget tillbaka till xlCalculationAutomatic utlöser automatiskt en omräkning av arbetsbladet, så det behöver inte tryckas på F9-tangenten när makroen har körts.

Inaktivera arkskärmens uppdatering

Du kanske märker att när dina makron körs, gör skärmen en hel del flimrande. Detta flimrar är Excel som försöker att omradera skärmen för att visa det aktuella läget i kalkylbladet. Tyvärr, varje gång Excel redraws skärmen tar det upp minnesresurser.

Du kan använda programmet. ScreenUpdating-egenskapen för att inaktivera skärmuppdateringar tills ditt makro har slutfört. Inaktivera skärmuppdatering sparar tid och resurser, så att ditt makro kan springa lite snabbare. När din makrokod har slutförts kan du aktivera skärmens uppdatering igen.

Sub Macro1 () Application. Beräkning = xlCalculationManual Application. ScreenUpdating = False 'Placera din makrokod här Application. Beräkning = xlCalculationAutomatic Application. ScreenUpdating = True End Sub

När du har satt egenskapen ScreenUpdating tillbaka till True, utlöser Excel automatiskt en ombakadragning av skärmen.

Slå av statusrapporter

Excel-statusfältet, som visas längst ned i Excel-fönstret, visar normalt hur vissa åtgärder i Excel utförs.Om ditt makro arbetar med mycket data kommer statusfältet att ta upp vissa resurser.

Det är viktigt att notera att avstängning av skärmuppdatering är skild från att stänga av statusfältet. Statusfältet fortsätter att uppdateras även om du inaktiverar skärmuppdatering. Du kan använda programmet. DisplayStatusBar-egenskapen för att tillfälligt inaktivera uppdateringar i statusfältet, ytterligare förbättra prestanda för ditt makro:

Sub Macro1 () Application. Beräkning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = False 'Placera din makrokod här Application. Beräkning = xlCalculationAutomatic Application. ScreenUpdating = Sann applikation. DisplayStatusBar = True End Sub

Berätta Excel för att ignorera händelser

Du kan implementera makron som händelseförfaranden, och berätta för Excel att köra viss kod när ett arbetsblad eller arbetsbok ändras.

Ibland gör standardmakroner ändringar som utlöser ett händelseförfarande. Om du till exempel har ett standardmakro som manipulerar flera celler på Sheet1, varje gång en cell på det arket ändras, måste ditt makro pausa medan Worksheet_Change-händelsen körs.

Du kan lägga till en annan nivå av prestandaökning genom att använda egenskapen EnableEvents för att berätta för Excel att ignorera händelser medan ditt makro körs.

Ställ egenskapen EnableEvents till False innan du kör ditt makro. När din makrokod är färdig kan du ange egenskapen EnableEvents till True.

Sub Macro1 () Application. Beräkning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = Falskt program. EnableEvents = False 'Placera din makrokod här Application. Beräkning = xlCalculationAutomatic Application. ScreenUpdating = Sann applikation. DisplayStatusBar = Sann applikation. EnableEvents = True End Sub

Döljer sidbrytningar

Varje gång ditt makro ändrar antalet rader, ändrar antalet kolumner eller ändrar sidinställningen i ett arbetsblad, är Excel tvungen att ta tid att beräkna de sidbrytningar som visas på arket.

Du kan undvika detta beteende genom att helt enkelt dölja sidbrytningarna innan du startar ditt makro.

Ställ egenskapen DisplayPageBreaks på False för att dölja sidbrytningar. Om du vill fortsätta att visa sidbrytningar efter att ditt makro körs, ställer du in egenskapen DisplayPageBreaks-arket tillbaka till True.

Sub Macro1 () Application. Beräkning = xlCalculationManual Application. ScreenUpdating = False Application. DisplayStatusBar = Falskt program. EnableEvents = Falskt Aktivitetsblad. DisplayPageBreaks = False "Placera din makrokod här Application. Beräkning = xlCalculationAutomatic Application. ScreenUpdating = Sann applikation. DisplayStatusBar = Sann applikation. EnableEvents = True Aktivitetsblad. DisplayPageBreaks = True End Sub

Uppdaterade pivottabelluppdateringar

Om ditt makro manipulerar pivottabeller som innehåller stora datakällor kan du uppleva dålig prestanda när du gör saker som att dynamiskt lägga till eller flytta pivotfält.

Du kan förbättra prestanda för ditt makro genom att suspendera omräkningen av pivottabellen tills alla pivottfältförändringar har gjorts. Ställ in pivottabellen. ManuelltUppdatera egenskapen till True för att skjuta omberäkning, kör din makrokod och ställ sedan in pivottabellen. ManuelltUppdatera egenskapen tillbaka till False för att utlösa omräkning.

Sub Macro1 () ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = True 'Placera din makrokod här ActiveSheet. Pivottabeller ("PivotTable1"). ManualUpdate = False End Sub

Hantering av kopiering och klistra in

Det är viktigt att komma ihåg att även om Macro Recorder sparar tid genom att skriva VBA-kod för dig skriver den inte alltid den mest effektiva koden. Ett utmärkt exempel är hur Macro Recorder fångar alla kopior och klistra åtgärder du utför när du spelar in.

Du kan ge dina makron ett litet steg genom att klippa mellann och utföra en direkt kopia från en cell till en destinationscell. Denna alternativa kod använder destinationsargumentet för att kringgå urklippet och kopiera innehållet i cell A1 direkt till cell B1.

Range ("A1"). Kopiera destination: = Område ("B1")

Om du bara behöver kopiera värden (inte formatering eller formler) kan du förbättra prestandan ännu mer genom att undvika kopieringsmetoden alla tillsammans. Ange enkelt värdet för destinationscellen till samma värde som finns i källcellen. Denna metod är ungefär 25 gånger snabbare än att använda kopieringsmetoden:

Område ("B1"). Värde = Område ("A1"). Värde

Om du bara behöver kopiera formler från en cell till en annan (inte värden eller formatering) kan du ställa in formuläret för destinationscellen till samma formel som finns i källcellen:

Område ("B1"). Formel = Range ("A1"). Formel

Använda med-satsen

Vid inspelning av makron kommer du ofta att manipulera samma objekt mer än en gång. Du kan spara tid och förbättra prestanda genom att använda Med-förklaringen för att utföra flera åtgärder på ett visst objekt i ett skott.

Med-satsen som används i följande exempel berättar Excel att tillämpa alla formateringsändringar på en gång:

Med Range ("A1"). Font. Fet = sant Kursiv = True. Underline = xlUnderlineStyleSingle End With

Att bli vana med att chunking åtgärder i Med uttalanden kommer inte bara att hålla dina makron springa snabbare men också göra det enklare att läsa din makro kod.

Undvik valmetoden

Makroinspelaren är förtjust i att använda Select-metoden för att explicit välja valda objekt innan de vidtar åtgärder på dem. Det finns vanligtvis inget behov av att välja objekt innan de arbetar med dem. Faktum är att du kan förbättra makroprestanda dramatiskt genom att inte använda Select-metoden.

Efter att ha spelat in dina makron, gör det vanligt att ändra den genererade koden för att ta bort Select-metoderna. I det här fallet ser den optimerade koden ut som följande:

Ark ("Sheet1"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet2"). Range ("A1"). FormulaR1C1 = "1000" Sheets ("Sheet3"). Range ("A1"). FormulaR1C1 = "1000"

Observera att ingenting väljs.Koden använder helt enkelt objekthierarkin för att tillämpa de åtgärder som behövs.

Begränsa resor till arbetsbladet

Ett annat sätt att påskynda makronerna är att begränsa antalet gånger du refererar till kalkylbladsdata i din kod. Det är alltid mindre effektivt att ta tag i data från kalkylbladet än från minnet. Det vill säga, dina makron kommer att springa mycket snabbare om de inte behöver repetera flera gånger med kalkylbladet.

Följande enkla kod tvingar exempelvis VBA att kontinuerligt återgå till Ark ("Sheet1"). Område ("A1") för att få numret som behövs för att jämförelsen ska utföras i If-satsen:

För ReportMonth = 1 till 12 Om Range ("A1"). Value = ReportMonth Sedan MsgBox 1000000 / ReportMonth End Om Next ReportMonth

En mycket effektivare metod är att spara värdet i Ark ("Sheet1"). Område ("A1") till en variabel som heter MyMonth. På så sätt refererar koden till MyMonth-variabeln istället för kalkylbladet:

Dim MyMonth som Integer MyMonth = Range ("A1"). Value For ReportMonth = 1 till 12 Om MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth Enda om Next ReportMonth

Överväg att använda variabler för att arbeta med data i minnet i motsats till att direkt referera till arbetsblad.