Innholdsfortegnelse:
Video: Introduksjon til å bruke Excel for å lage dynamiske rapporter 2024
Har du noen gang prøvd å fornemme 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 inneholder verdier.
Du kan opprette et VBA-verktøy som genererer et kart over det aktive regnearket. Kartet genereres på et nytt regneark, og 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 å finne potensielle feil. For eksempel, hvis en formel i en blokk med formler er overskrevet av en verdi, vil den cellen skille seg ut i kartvisningen (som i celle Q11 i eksempelet).
QuickMap VBA-koden
VBA-prosedyren som genererer regnearket, er oppført nedenfor. Hvis du vil bruke dette verktøyet, kopier du bare koden og limer den inn i en VBA-modul. Aktiver deretter et regneark og utfør QuickMap-underrutinen.
Sub QuickMap () Dim FormulaCells Som Variant Dim TextCells Som Variant Dim NumberCells Som Variant Dim Area Som Range Hvis TypeName (ActiveSheet) "Worksheet" Deretter Avslutt Sub "Opprett objektvariabler for celle-undergrupper". Feil Fortsett Neste sett FormulaCells = Range “A1”). SpecialCells _ (xlFormulas, xlNumbers + xlTextValues + xlLogical) Set TextCells = Range ("A1"). SpecialCells (xlConstants, xlTextValues) Set NumberCells = Range ("A1"). SpecialCells (xlConstants, xlNumbers) På Feil GoTo 0 'Legg til et nytt ark og format det Ark. Legg til med celler. ColumnWidth = 2. Font. Størrelse = 8. HorizontalAlignment = xlCenter End With Application. ScreenUpdating = False 'Gjør formelcellene hvis ikke erEmpty (FormulaCells) deretter for hvert område i FormulaCells. Områder med ActiveSheet. Område (Område. Adresse). Verdi = "F". Interiør. ColorIndex = 3 Slutt med neste områdeavslutning Hvis 'Gjør tekstcellene hvis ikke IsEmpty (TextCells) deretter for hvert område i TextCells. Områder med ActiveSheet. Område (Område. Adresse). Verdi = "T". Interiør. ColorIndex = 4 Slutt med neste område End Hvis 'Gjør tallceller hvis ikke erEmpty (NumberCells) deretter for hvert område i NumberCells. Områder med ActiveSheet. Område (Område. Adresse). Verdi = "N". Interiør. ColorIndex = 6 Slutt med neste områdeavslutning Hvis sluttdel
Slik fungerer det
Prosedyren kontrollerer først at det aktive arket er et regneark. Hvis det ikke er det, er det en rask avslutning uten videre tiltak. Når det aktive arket er et regneark, oppretter prosedyren tre objektvariabler ved hjelp av SpecialCells-metoden for å identifisere de ulike celletyper.SpecialCells-metoden er veldig nyttig. Hvis du ikke er kjent med det, kan du sjekke det ut i Excels elektroniske hjelpefil. Legg merke til bruken av On Error Resume Next. 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 tilpasningen til midten. Dette trinnet er kosmetisk. Underen slår deretter av skjermoppdatering for å fange ting litt opp.
De neste tre blokkene med kode behandler cellene. Hvis ingen celler kvalifiserer, er objektvariablen tom, så deltestene for dette. Så løper rutinen gjennom hvert område i Range-objektet og formaterer cellen. Du kan enkelt tilpasse denne delen av underrutinen for å bruke annen formatering.
Ta en titt på Power Utility Pak-tillegget for en mye mer sofistikert versjon av dette verktøyet.