Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Home > Nederlandse site > Artikelen > Excel Namen > XLM in naam
This page in English

Bereiknamen in Excel

Nog een stap verder: XL4 Macro functies in gedefinieerde namen

Nog minder bekend dan het gegeven dat men functies in namen kan toepassen is het feit, dat ook XLM macro functies (de oude macro commando’s van Excel versie 4) kunnen worden gebruikt. Hiermee kunnen oplossingen gemaakt worden die normaal gesproken slechts met VBA mogelijk zijn. Één waarschuwing vooraf is wel noodzakelijk: Bij verschillende versies van Excel kunnen cellen die gebruik maken van namen waarin XLM functies gebruikt zijn NIET naar een ander blad worden gekopieerd. Excel kan dan crashen! Kopiëren binnen een werkblad of het kopiëren van alleen de formule (door de tekst van de formule in het formulevak of in de cel te selecteren en te kopiëren) is geen probleem.

Voorbeeld 1: een lijst van bestanden in een werkblad aanmaken.

Het kan handig zijn een lijst met de bestanden in een bepaalde directory in een werkblad in Excel te krijgen. Met behulp van de macrofunctie BESTANDEN kan dit worden gerealiseerd. De naam "Bestanden" wordt gedefinieerd, met de volgende formule:

=BESTANDEN(Blad6!$C$2).

In een werkblad wordt vervolgens in bijvoorbeeld cel C2 de naam van de map en de gewenste wildcard ingevoerd, bijvoorbeeld c:\*.* . In een kolom, startend vanaf rij 1, kan de lijst met bestanden via de volgende formule worden opgeroepen:

=INDEX(Bestanden;RIJ())

Deze cel moet vervolgens omlaag gekopieerd worden totdat de formules #VERW! als resultaat geven, ten teken dat er geen verdere bestanden meer zijn. Zie Afbeelding 15. Door een ander pad of bestandsstructuur in cel C2 in te voeren kan een lijst voor een andere map of een andere set bestanden worden gemaakt.


Afbeelding 15: een lijst met bestanden maken

Voorbeeld 2: de vulkleur van een cel lezen.

Met de functie CEL.LEZEN kunnen allerlei eigenschappen van een cel worden opgevraagd. Onder andere de achtergrondkleur. Hiertoe is de volgende naam gedefinieerd, terwijl cel B2 actief was: Naam: CelKleur Verwijst naar: =CEL.LEZEN(63;Blad7!A2)+Nu()*0 De toevoeging NU()*0 zorgt ervoor, dat de naam bij elke herberekening wordt meegenomen. Het argument 63 geeft aan, dat de achtergrondkleur moet worden opgevraagd. Zie afbeelding 16.


Afbeelding 16: definitie van CelKleur

Merk op, dat een relatieve verwijzing is gebruikt, zodat de naam celkleur de kleurindex zal weergeven van de cel links ten opzichte van de cel met de formule =CelKleur Omdat een verandering van de achtergrondkleur van een cel geen herberekening veroorzaakt, zal deze formule helaas niet bijgewerkt worden als een ander vulkleur wordt gekozen. Met deze methode is het mogelijk om bijvoorbeeld alle blauwe cellen op te tellen in een bepaald bereik(zie afbeelding 17).


Afbeelding 17: optellen cellen met bepaalde kleur

Voorbeeld 3: Alleen de getoonde decimalen optellen in een cel.

Wanneer getallen in een cel worden afgerond door gebruik te maken van een bepaald getalsformaat (bijvoorbeeld op 1 cijfer achter de komma), dan kunnen afrondingsverschillen ontstaan wanneer een reeks cellen wordt opgeteld. Dit komt omdat Excel blijft rekenen met de getallen/uitkomsten die werkelijk in de cel staan en niet met de getoonde getallen. Via de functie CEL.LEZEN kan de getoonde waarde van een cel worden opgevraagd. Hiervoor is de onderstaande naam gebruikt:

Naam: GetalInCel Verwijst naar: =CEL.LEZEN(53;Blad8!A2)+NU()*0

Zie afbeelding 18.


Afbeelding 18: Optellen getoonde waarden

Duidelijk is te zien dat de SOM functie een ander resultaat (18,9) geeft dan men op basis van de getoonde cijfers zou verwachten (18,8). Natuurlijk kan dit effect ook worden bereikt door de functie AFRONDEN te gebruiken en vervolgens de afgeronde getallen op te tellen. In de hier gebruikte methode zal het resultaat zich echter automatisch aanpassen indien de opmaak van de getallen wordt aangepast (na herberekenen).

Voorbeeld 4: Automatisch naar het vorige en volgende werkblad verwijzen.

Het kan nog wel eens handig zijn om een methode te hebben waarmee verwezen kan worden naar cel(len) op het voorgaande werkblad. Dus zelfs wanneer dat blad verplaatst wordt, blijft de functie verwijzen naar het blad direct links van het huidige. Indien de formule gekopieerd wordt van Blad2 naar Blad3, zullen de formules op Blad3 nu verwijzen naar Blad2 en niet naar Blad1. Normaal zal een gekopieerde formule blijven verwijzen naar het blad waarnaar het al verwees. Zie ook het bestand Namen02.xls op de CD. Hiertoe is een combinatie van verschillende namen nodig. De volgende functie geeft een lijst van alle werkbladen in een werkmap:

=WERKMAP.LEZEN(1+0*NU())

Deze functie is gebruikt in de naam "AlleBladen". Het resultaat van AlleBladen zal deze matrix zijn:

{[namen02.XLS]Blad1;[namen02.XLS]Blad2;[namen02.XLS]Blad3}

De naam van het huidige blad (het blad waarin de onderstaande naam is toegepast) kan worden bepaald met:

=CEL.LEZEN(32+0*NU();INDIRECT(GetRC;ONWAAR))

De formule INDIRECT(GetRC;ONWAAR) heeft altijd als resultaat de cel waarin een naam gebruikt wordt die die functie aanroept. De functie GetRC bepaalt welke letters Excel moet gebruiken voor de aanduiding van rij en kolom. In een Nederlandse Excel versie is dat RK, in de Engelse RC:

Naam: GetRC

Formule: =SUBSTITUEREN(VERWIJZING.TEKST(!$A$1);1;"")

CEL.LEZEN(32;..) geeft de naam van het werkblad waarnaar deze functie verwijst, voorafgegaan door de bestandsnaam. Op Blad2 is het resultaat van deze functie (toegepast in de naam DitBlad) dus: [namen02.XLS]Blad2

Door nu de volgende functie te gebruiken kan de naam van het vorige werkblad worden bepaald (gedefinieerd als "VorigBlad"):

=INDEX(AlleBladen;VERGELIJKEN(DitBlad;AlleBladen;0)-1)

De VERGELIJKEN functie geeft hierbij de positie van de naam van het huidige blad in de lijst met werkbladen. Door 1 af te trekken van dit getal wordt dus de positie van het voorgaande werkblad aangegeven. Vervolgens wordt via de INDEX functie de naam van dat voorgaande werkblad bepaald. Op dezelfde manier kan het volgende blad worden verkregen (gedefinieerd als "VolgendBlad"):

=INDEX(AlleBladen;VERGELIJKEN(DitBlad;AlleBladen;0)+1)

Indien nu verwezen moet worden naar cel B2 op het vorige blad, dan kan dit met de onderstaande formule (in een cel):

=INDIRECT("'"&VorigBlad&"'!"&CEL("address";B2))

En evenzo voor het volgende werkblad:

=INDIRECT("'"&VolgendBlad&"'!"&CEL("address";B2))

Het bestand Arg2Name.zip op de download pagina geeft enkele voorbeelden van het toepassen van XLM macro functies in namen. Om inzicht te krijgen in wat nog meer mogelijk is, kan het Microsoft Help bestand voor de XLM functies (in verschillende talen, waaronder Nederlands) gevonden worden op deze pagina (helaas alleen bruikbaar voor Excel 2000 gebruikers):

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C09BF7F7-D30E-4CE9-8930-5D03748CA5CD

Het Engelse help bestand (bruikbaar in alle Excel versies) kan hier worden gevonden:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q128/1/85.asp&NoWebContent=1