Koppelingen opsporen en verwijderen
Koppelingen maken het u gemakkelijk als u bijvoorbeeld bij het dagelijkse werk vaak gegevens nodig heeft uit andere Excel-werkmappen. (In de bronmap het bereik kopiëren en vervolgens op de doellocatie invoegen met Plakken speciaal -> Koppeling plakken.)
Soms bevat een werkmap koppelingen die verwijzen naar gegevens die u niet meer nodig hebt. Ze zijn dan hinderlijk omdat Excel iedere keer bij het openen van het bestand blijft vragen of de koppelingen moeten worden bijgewerkt. In de onderstaande tips laten we zien hoe u (overbodige) koppelingen kunt vinden en er desgewenst van verlost kunt worden.
1 Formuleweergavemodus
Standaard toont Excel in de formulebalk de inhoud van een cel: een waarde of een formule, hetzij met functies en operatoren hetzij een verwijzing in de vorm van een adres of een bereiknaam. Een koppeling is een speciaal type verwijzing; de syntaxis luidt:
=[Map1.xls]Blad1!$A$1
Als de werkmap met de verwijzingsbron op het moment dat u de formulebalk raadpleegt niet geopend is, voegt Excel het pad naar de werkmaplocatie toe aan de verwijzing:
='C:\Mijn documenten\[Map1.xls]Blad1'!$A$1
Een niet-geselecteerde cel met een koppeling toont alleen de waarde van de celinhoud (het resultaat van de koppeling). Normaal gesproken kunt u dus jammer genoeg niet zien hoe een waarde tot stand komt. Excel heeft echter een weergavemodus waarin de cellen niet de berekende waarden, maar de achterliggende formules tonen.
Ga als volgt te werk om formuleweergavemodus in te schakelen:
Stap voor stap
1 Selecteer de opdracht Extra -> Opties.
2 Open het tabblad Weergave en activeer in de categorie Vensteropties het selectievakje Formules.
3 Bevestig de nieuwe instelling met OK.
Excel toont van alle cellen met een formule niet de resulterende waarde maar de volledige formule. De meeste kolommen worden daardoor veel breder.
Op analoge wijze kunt u ook terugschakelen naar de normale weergave.
Afb. 1: Alle koppelingen zichtbaar gemaakt
TIP
Deze opdracht heeft alleen effect op het actieve werkblad. U moet de procedure dus herhalen voor elk werkblad waarin u naar koppelingen wilt zoeken.
2 Koppelingen documenteren
Met behulp van een macro kunt u een opsomming van de koppelingen documenteren op een nieuw werkblad. Ga als volgt te werk om zo’n macro te maken:
Stap voor stap
1 Druk op Alt+F11 om de Excel-ontwikkelomgeving te starten. Selecteer de opdracht Invoegen „Ã Module.
2 Selecteer de opdracht Invoegen „Ã Procedure, activeer het keuzerondje Sub, geef de macro de naam KoppelingenDocumenteren en klik op OK. Neem de volgende broncode op in de nieuwe macro:
Sub KoppelingenDocumenteren()
Dim bereik As Range
Dim cel As Range
Dim blad As Worksheet
Dim werkblad As Worksheet
Set werkblad = Sheets.Add
i = 1
For Each blad In ActiveWorkbook.Worksheets
blad.Activate
Set bereik = blad.Range(Cells(1, 1), Selection.SpecialCells(xlCellTypeLastCell))
For Each cel In bereik
If ((InStr(cel.Formula, "=") > 0) And (InStr(cel.Formula, "\") > 0)) Then
werkblad.Cells(i, 1) = cel.Address
werkblad.Cells(i, 2).Value = " " & cel.Formula
werkblad.Cells(i, 3) = cel.Value
i = i + 1
End If
Next
Next
End Sub
3 Maak via de werkset Besturingselementen een opdrachtknop en wijs daaraan via het snelmenu de macro toe.
De macro maakt een nieuw werkblad en noteert daar een overzicht van alle koppelingen (in de hele werkmap): celadres van de koppeling, verwijzing naar de koppelingsbron en de waarde van de koppeling.
Afb. 2: Documentatie van de koppelingen
3 Koppelingen verwijderen met een macro
De koppelingen in een werkmap kunt u wel bekijken in het dialoogvenster van de opdracht Bewerken -> Koppelingen, maar daar is geen mogelijkheid de koppelingen te verwijderen uit de werkmap. De hieronder afgedrukte macro vervangt alle koppelingen in een werkmap door hun waarden.
Macrocode
Neem de volgende macrocode (zoals in paragraaf 2 beschreven) op in de werkmap met koppelingen – of in het opstartbestand Persnl.xls als u dat altijd geopend houdt.
Sub KoppelingenVervangen()
Dim cel As Range
Dim werkblad As Worksheet
For Each werkblad In ActiveWorkbook.Worksheets
werkblad.Activate
For Each cel In werkblad.UsedRange
If InStr(cel.Formula, "„Ã") > 0 Then cel.Value = cel.Value
Next cel
Next werkblad
End Sub
Houd in gedachten dat deze macro een koppeling alleen verwijdert als het bestand met de brongegevens gesloten is.