Har du noen gang prøvd å forstå en ukjent (og komplisert) Excel-arbeidsbok? Det ville være nyttig å se et kart som gir en oversikt over hvilke celler som inneholder konstanter og hvilke celler som inneholder verdier.
Du kan opprette et VBA-verktøy som genererer et kart over det aktive regnearket. Kartet er generert på et nytt regneark, og det består av fargekodede celler som lar deg raskt identifisere verdier, tekst og formler.
Nedenfor ser du et eksempel på et slikt kart. Celler som inneholder tekst er grønne, de som inneholder en numerisk verdi er gule, og celler som inneholder formler er røde. Et slikt kart kan hjelpe deg med å oppdage potensielle feil. For eksempel, hvis én formel i en blokk med formler har blitt overskrevet av en verdi, vil den cellen skille seg ut i kartvisningen (som i celle Q11 i eksemplet).
QuickMap VBA-koden
VBA-prosedyren som genererer arbeidsarkkartet er oppført nedenfor. Hvis du vil bruke dette verktøyet, kopierer du bare koden og limer den inn i en VBA-modul. Aktiver deretter et regneark og utfør QuickMap-subrutinen.
Sub QuickMap()
Dim FormulaCells som variant
Dim tekstceller som variant
Dim NumberCells som variant
Dim område som rekkevidde
Hvis TypeName(ActiveSheet) <> "Worksheet" Avslutt Sub
' Lag objektvariabler for celleundersett
Ved feil på Fortsett neste
Sett FormulaCells = Range(“A1”).SpecialCells _
(xlFormulas, xlNumbers + xlText Values + xlLogical)
Sett TextCells = Range(“A1”).SpecialCells(xlConstants, xlTextValues)
Sett NumberCells = Range(“A1”).SpecialCells(xlConstants, xlNumbers)
Ved feil GoTo 0
' Legg til et nytt ark og formater det
Ark.Legg til
Med celler
.ColumnWidth = 2
.Skriftstørrelse = 8
.HorizontalAlignment = xlCenter
Avslutt med
Application.ScreenUpdating = False
' Gjør formelcellene
Hvis ikke er tomme (formelceller) så
For hvert område i FormulaCells.Areas
Med ActiveSheet.Range(Area.Address)
.Value = "F"
.Interior.ColorIndex = 3
Avslutt med
Neste område
Slutt om
' Gjør tekstcellene
Hvis ikke er tomme (tekstceller) så
For hvert område i TextCells.Areas
Med ActiveSheet.Range(Area.Address)
.Value = "T"
.Interior.ColorIndex = 4
Avslutt med
Neste område
Slutt om
' Gjør de numeriske cellene
Hvis ikke er tomme (tallceller) så
For hvert område i NumberCells.Areas
Med ActiveSheet.Range(Area.Address)
.Value = "N"
.Interior.ColorIndex = 6
Avslutt med
Neste område
Slutt om
End Sub
Hvordan det fungerer
Prosedyren kontrollerer først at det aktive arket er et regneark. Hvis den ikke er det, er det en rask avslutning uten ytterligere handling. Når det aktive arket er et regneark, oppretter prosedyren tre objektvariabler ved å bruke SpecialCells-metoden for å identifisere de ulike celletypene. SpecialCells-metoden er veldig nyttig. Hvis du ikke er kjent med det, sjekk det ut i Excels online hjelpefil. Legg merke til bruken av Ved feil gjenoppta neste. Dette er for å unngå feilen som oppstår hvis ingen celler kvalifiserer - for eksempel hvis regnearket ikke har noen formler.
Deretter legger prosedyren til et nytt regneark, reduserer cellebredden og setter den horisontale justeringen til midten. Dette trinnet er kosmetisk. Suben slår deretter av skjermoppdatering for å få fart på ting litt.
De neste tre blokkene med kode behandler cellene. Hvis ingen celler kvalifiserer, er objektvariabelen Empty, så underdelen tester for dette. Deretter går rutinen gjennom hvert område i Range-objektet og formaterer cellen. Du kan enkelt tilpasse denne delen av subrutinen for å bruke forskjellig formatering.
Sjekk ut Power Utility Pak-tillegget for en mye mer sofistikert versjon av dette verktøyet.