Cel.Lezen

Een paar weken geleden kreeg ik via de website ginfo.nl een vraag over de Excel-functie Cel.Lezen.

Ik begon aan mijn geheugen te twijfelen, want ik (her)kende de functie niet.
Dus maar even gegoogeled; wat bleek: Cel.Lezen is een restant uit een ver verleden, toen VBA nog niet geïmplementeerd was, een zogenaamde Excel4Macro.
cellezenCel.Lezen is één van de vele functies, die niet meer gedocumenteerd zijn, maar nog wel gebruikt kunnen worden; niet direct, als een standaard-functie, maar via een omweg (het gebruik van de optie Namen beheren).
Steeds meer mogelijkheden van Cel.Lezen zijn (of worden) in nieuwere versies van Excel ingebouwd, maar soms kan deze functie toch nog goed bruikbaar zijn.
En los daarvan: het is nooit weg om nog eens met het onderdeel Namen van Excel te stoeien!

Gebruik van Cel.Lezen

Wat kun je doen met Cel.Lezen?
Deze macro/functie levert 66 (!) verschillende soorten informatie over de inhoud of de opmaak van een cel. CelLezenHierbij valt te denken aan de celverwijzing (welke rij, welke kolom) maar ook de inhoud, of de cel een formule bevat, welke kleuren gebruikt zijn etc.
In het Voorbeeldbestand is in het tabblad InfoType een totaal-overzicht opgenomen.

De functie Cel.Lezen heeft 2 parameters nodig: de eerste is het InfoTypeNummer en de tweede is de cel, waarvan de informatie opgehaald moet worden.

CelLezenZoals hiervoor al aangegeven, kan Cel.Lezen niet rechtstreeks aangeroepen worden; dit kan wel via een eigen formule/naam geregeld worden.

Een voorbeeld (zie tabblad VB in het Voorbeeldbestand):
het is altijd goed om te weten of en waar in een kolom (bijvoorbeeld C) formules staan.
We gaan dat in kolom D als volgt aangeven:

  1. plaats de cursor in cel D3
  2. kies binnen de menu-tab Formules de optie Namen beheren en dan de button Nieuw
  3. CelLezenvul het scherm in:
    * kies als naam BevatFormule
    * het bereik, waar deze naam geldig is, beperken we tot het betreffende tabblad (zie hieronder), dus Vb
    * en we verwijzen niet, zoals gebruikelijk is voor een Naam, naar een cel(bereik), maar naar een formule, namelijk
    =CEL.LEZEN(48;C3)
    (InfoTypeNummer 48 geeft als resultaat WAAR als de betreffende cel een formule bevat)
    * klik OK
  4. in het overzichtsscherm Namen beheren zien we onze nieuwe naam
  5. tik in cel D3 de formule =BevatFormule
  6. kopieer de formule in D3 naar beneden tot en met cel D15

NB1 als je goed kijkt in het overzichtsscherm Namen beheren, dan zul je zien dat onze formule iets is aangepast: aan cel C3 is ook de bladnaam Vb toegevoegd; dit is niet te voorkomen. Dat is ook de reden, dat we hiervoor de naam alleen maar geldig maken op het tabblad Vb

NB2 we hebben een relatieve verwijzing naar C3 gebruikt (zonder $-tekens); dit zorgt er voor, dat de formule in D4 naar C4 ‘kijkt’ etc.
Het effect hiervan zie je ook in Namen beheren: de formule bij Verwijst naar: is afhankelijk van de plaats van de cursor in het tabblad.

BevatFormule (vervolg)

Hiervoor is met behulp van de zelf-gedefinieerde naam BevatFormule een mogelijkheid gecreëerd om zichtbaar te maken of cellen al dan niet een formule bevatten.

In het tabblad Vb2 van het Voorbeeldbestand is een alternatieve methode gebruikt:
op dezelfde manier als hiervoor is, met cel D3 geselecteerd, een nieuwe naam gemaakt, BevatFormule2.
Deze naam is alleen geldig in Vb2 en heeft als verwijzing
=CEL.LEZEN(48;D3)

LET OP deze formule verwijst dus naar de cel zelf. Wanneer we ergens in het tabblad dan ook intikken =BevatFormule2, dan zal het resultaat altijd WAAR zijn!

Deze naam is in het voorbeeld gebruikt om via Voorwaardelijke opmaak zichtbaar te maken waar ergens in kolom C een formule wordt gebruikt.

NB de naam BevatFormule2 kan ook voor de gehele werkmap werkend worden gemaakt: wijzig de verwijzing naar cel D3 in INDIRECT(“RK”;Onwaar)

Overzicht

CelLezenOm de werking van Cel.Lezen verder te verduidelijken is in het Voorbeeldbestand het tabblad Ovz opgenomen. Hierbij is gebruik gemaakt van de naam CelInfo met als formule
=CEL.LEZEN($C$2;B5)
LET OP
de eerste parameter is absoluut, de tweede relatief: waar de formule ook gebruikt wordt, het InfoType is altijd de inhoud van cel C2

Door in cel C2 een nummer tussen 1 en 66 in te voeren wordt in C5:C9 de informatie over de overeenkomende cellen in kolom B weergegeven.

Met behulp van InfoTypeNummer=1 zien we de (absolute) celverwijzingen etc.

Herberekenen

Eén van de nadelen van dit soort implementatie van functies is, dat Excel het resultaat niet altijd bijwerkt.
Excel probeert namelijk zo efficiënt met de rekentijd om te gaan en beoordeelt na het aanpassen van een cel welke andere cellen allemaal opnieuw berekend moeten worden.
CelLezenMaar, wanneer je bijvoorbeeld de achtergrondkleur van een cel wijzigt, zal Excel geen enkele cel spontaan gaan herberekenen. En helaas ook niet wanneer we Excel daartoe dwingen door op F9 (Handmatig herberekenen) te drukken.

Wijzig in het tabblad Kleur van het Voorbeeldbestand bijvoorbeeld de achtergrondkleur van cel B3 in grijs. Wat gebeurt er met C3? En als je op F9 drukt? Niets dus.
Het enige wat helpt, is om cel C3 te selecteren, op F2 te drukken (inhoud cel wijzigen) en dan op Enter.
Voor de code-kolom is als Naam de volgende formule gebruikt:
=CEL.LEZEN(38;B3).

Maar voor alles (?) is een oplossing. We moeten gewoon zorgen dat onze formule een element bevat, dat bij een herberekening altijd een nieuwe waarde krijgt. Een voorbeeld van zoiets is de functie Nu(), die bij iedere wijziging in Excel opnieuw wordt bepaald.
Dus nogmaals een Naam vastleggen (CelKleur2):
=CEL.LEZEN(38+Nu()*0;B3).
Wijzig één van de kleuren in kolom E en druk op F9: et voilà!

NB1 de functie Nu() levert een getal op; dat zou de inhoud van de eerste parameter veranderen, dat is natuurlijk ook weer niet de bedoeling. Vandaar het vermenigvuldigen met 0.

NB2 de truc om te zorgen dat Excel wat ‘actiever’ is bij het herberekenen heb ik ooit op de site van Jan Karel Pieterse gevonden: jkp-ads.com.

Waarschuwingen

Waarschuwing1: aangezien Cel.Lezen voor Excel een soort macro is, moet een bestand, dat gebruik maakt van deze formule, opgeslagen worden met de extensie xlsm.

Waarschuwing2: bij het googelen kwam ik enkele keren tegen, dat het kopieren van cellen met verwijzingen naar Cel.Lezen naar andere tabbladen, kan zorgen voor een crash van Excel. OEPS!

Waarschuwing3: of Microsoft de  Excel4Macro’s in een volgende versie nog zal blijven ondersteunen is maar de vraag.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Verplichte velden zijn gemarkeerd met *