Med VBA för att skapa ett kalkylblad - dummies

Har du någonsin försökt att känna till en okänd (och komplicerad) Excel-arbetsbok? Det skulle vara tillrådligt att se en karta som ger en översikt över vilka celler som innehåller konstanter och vilka celler innehåller värden.

Du kan skapa ett VBA-verktyg som genererar en karta över det aktiva arbetsbladet. Kartan genereras på ett nytt arbetsblad och består av färgkodade celler som gör att du snabbt kan identifiera värden, text och formler.

Nedan ser du ett exempel på en sådan karta. Celler som innehåller text är gröna, de som innehåller ett numeriskt värde är gula och celler som innehåller formler är röda. En sådan karta kan hjälpa dig att hitta potentiella fel. Om exempelvis en formel i ett block med formler har skrivits över av ett värde kommer den cellen att stå ut i kartvisningen (som i cell Q11 i exemplet).

QuickMap VBA-koden

VBA-proceduren som genererar kalkylarkskartan anges nedan. Om du vill använda det här verktyget kopierar du bara koden och klistrar in den i en VBA-modul. Aktivera sedan ett kalkylblad och kör QuickMap-underrutinen.

Sub QuickMap () Dim FormulaCells Som Variant Dim TextCells Som Variant Dim NumberCells Som Variant Dim Area Som Range Om TypeName (ActiveSheet) "Worksheet" Därefter Avsluta Sub "Skapa objektvariabler för cellundergrupper" Vid Fel Fortsätt Nästa Set FormulaCells = Range ”A1”). SpecialCells _ (xlFormulas, xlNumbers + xlTextValues ​​+ xlLogical) Ange TextCells = Range ("A1"). SpecialCells (xlConstants, xlTextValues) Set NumberCells = Range ("A1"). SpecialCells (xlConstants, xlNumbers) På Error GoTo 0 'Lägg till ett nytt ark och formatera det Ark. Lägg till med celler. ColumnWidth = 2. Font. Storlek = 8. HorizontalAlignment = xlCenter Slut med applikation. ScreenUpdating = False 'Gör formeln celler om inte IsEmpty (FormulaCells) sedan för varje område i FormulaCells. Områden med ActiveSheet. Område (Område. Adress). Värde = "F". Interiör. ColorIndex = 3 Sluta med nästa område Slut Om 'Gör textcellerna om inte IsEmpty (TextCells) sedan för varje område i TextCells. Områden med ActiveSheet. Område (Område. Adress). Värdet = "T". Interiör. ColorIndex = 4 avsluta med nästa område slut Om 'Gör de numeriska cellerna om inte IsEmpty (NumberCells) sedan för varje område i NumberCells. Områden med ActiveSheet. Område (Område. Adress). Värde = "N". Interiör. ColorIndex = 6 sluta med nästa yta slutar om slutdel

Hur det fungerar

Proceduren kontrollerar först att det aktiva arket är ett arbetsblad. Om det inte är det finns en snabb utgång utan ytterligare åtgärder. När det aktiva arket är ett arbetsblad skapar proceduren tre objektvariabler genom att använda metoden SpecialCells för att identifiera de olika celltyperna.SpecialCells-metoden är mycket användbar. Om du inte är bekant med det, kolla in det i Excels hjälpfil. Lägg märke till användningen av On Error Resume Next. Detta är för att undvika det fel som uppstår om inga celler kvalificerar - till exempel om arbetsbladet inte har några formler.

Nästa lägger proceduren ett nytt arbetsblad, minskar cellbredd och ställer in den horisontella inriktningen i mitten. Detta steg är kosmetiskt. Delen stänger sedan av uppdateringen av skärmen för att påskynda sakerna lite.

De följande tre blocken av kod bearbetar cellerna. Om inga celler kvalificerar är objektvariabeln tom, så deltestet för detta. Sedan sluter rutinen genom varje område i Range-objektet och formaterar cellen. Du kan enkelt anpassa denna del av subrutinen för att tillämpa olika formateringar.

Kolla in Power Utility Pak-tillägget för en mycket mer sofistikerad version av det här verktyget.