Excel, Auto IndexPagina |
|
W-tje. Automatische IndexPagina !
Wist je dat het mogelijk is om - met slechts een vleugje VBA – in Excel een geautomatiseerde indexpagina te maken? Via dit werkblad kunnen we snel naar een ander werkblad kunnen springen. Natuurlijk is het mogelijk om elke keer na het verwijderen van een werkblad, het toevoegen van een werkblad of het wijzigen van een naam een zelf te onderhouden indexpagina (inhoudsopgave) handmatig aan te passen. Het is echter ook mogelijk om met slechts een klein stukje VBA, maar met behulp van de nog steeds aanwezige Excel 4.0 Macro Functies een geautomatiseerde indexpagina te maken. Hieronder staat in stappen ingegeven wat je moet doen:
Start Excel op en open een nieuwe werkmap.
Stap 2. Sla deze op als werkmap met macro’s.
Legt de werkmap vast onder de naam (bijvoorbeeld) IndexPagina.xlsm.
Stap 3. Definieer een nieuwe naam “WerkBladLijst”.
Laat deze verwijzen naar (formule) =WERKMAP.LEZEN(1)
Stap 4. Definieer een nieuwe naam “WerkMapNaam”.
Laat deze verwijzen naar (formule) =WERKMAP.LEZEN(16)
Stap 5. Vul in de cel ($C$6) de formule =WerkMapNaam.
De naam van de werkmap (indien opgeslagen) wordt dan in deze cel getoond.
Stap 6. Vul in de cel ($D$7) de formule =INDEX(WerkBladLijst;RIJ()-6)
De formule (naam) uit stap 3. geeft een matrix terug met net zoveel elementen als er werkbladen in de opgeslagen werkmap zijn. Om het eerste element uit de matrix te kiezen gebruik je de formule =INDEX(WerkBladLijst; 1) De “1” in de formule kunnen we vervangen door de formule RIJ(). Door gebruik te maken van RIJ()-6 (vanaf de zevende rij) kan de formule naar beneden worden uitgevuld, waarbij iedere rij lager een hogere index uit de Werkbladlijst neemt.
Stap 7. Foutmeldingen afvangen.
Door de formule naar beneden uit te vullen en op basis van het rijnummer een steeds hogere index uit te vragen kan het voor gaan komen dat het rijnummer hoger is dan het aantal elementen (WerkBladNamen) uit de WerkBladLijst. Op dat moment wordt er een foutmelding gegeven die je wilt afvangen. Dit kun je doen door de formule ALS.FOUT() te gebruiken. De volledige formule ziet er dan als volgt uit:
=ALS.FOUT(INDEX(WerkBladLijst;RIJ()-6);””)
Stap 8. Werkmapnaam uit de WerkbladNaam verwijderen.
De Werkmapnaam maakt nog steeds deel uit van de werkbladnaam. Wil je de werkmapnaam uit de werkbladnaam verwijderen, dan kun je dit bereiken door gebruik te maken van de formule =SUBSTITUEREN(). De volledige formule (waarbij cel $C$6 de werkmapnaam bevat) ziet er dan als volgt uit:
=ALS.FOUT(SUBSTITUEREN(INDEX(WerkBladLijst;RIJ()-6);"["&$C$6&"]";"");"")
Stap 9. Instellen herberekening na wijziging in werkblad
De Excel 4.0 macro’s hebben de eigenaardigheid niet te worden herberekend na een wijziging in de namen van een werkblad of het toevoegen of verwijderen van een werkblad. Daarom moeten we een kleinigheid in VBA regelen om dit voor elkaar te krijgen. Ga met + naar de Visual Basic Editor. Kies het object ThisWorkbook en selecteer onder Workbook het event SheetActivate. . . Tussen het begin en einde van de procedure plaats je de coderegel: Application.CalculateFull. Het geheel ziet er dan als volgt uit:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.CalculateFull
End Sub
Stap 10. Maak de werkbladnamen clickable.
Het zou leuk zijn om een lijst met werkbladnamen te hebben waarop kan worden geklikt waarna na het betreffende werkblad wordt gesprongen. Hiervoor gebruiken we de formule =HYPERLINK(). We hebben het scherm van de Visual Basic Editor inmiddels afgesloten en staan nu weer op het werkblad “IndexPagina” waar we de oorspronkelijke formule aanvullen. De syntax van de formule HYPERLINK ziet er als volgt uit: =HYPERLINK(locatienaam; getoonde naam). De locatienaam moet naar een cel verwijzen. We hebben in ons voorbeeld gekozen om hiervoor cel $A$1 te gebruiken. Dit kan echter elke willekeurige cel in een werkblad zijn. De (uiteindelijke) formule ziet er dan als volgt uit:
=ALS.FOUT(HYPERLINK(INDEX(WerkBladLijst;RIJ()-6) & "!$A$1"; SUBSTITUEREN(INDEX(WerkBladLijst;RIJ()-6);"["&$C$6&"]";""));"")
Download het sjabloon AutomatischeIndexPagina en probeer de berekeningen een uit met andere bedragen en percentages. Het AutomatischeIndexPagina-sjabloon is gratis uit de webwinkel te downloaden.
Heb je nog vragen, neem dan gerust contact met ons op.
