Excel, UDF’s en grafieken

Als ik het zeggen mag: ik vond mezelf aardig thuis in Excel [opzettelijke taalhas­pel]. Thuis dus op alle fronten: formules en functies, lijsten en tabellen, macro’s en VBA en ook … grafieken.
Dat dacht ik tot de zomer van 2013. Toen stuitte ik op ‘La Linea’ van Rober­to Mensa. Het was me totaal een raad­sel wat daar ge­beur­de. Ik heb er best een uurtje mee gespeeld voordat ik se­rieus aan de ontrafeling van dit wonder­tje begon.
Mensa maakt, behalve van al het beken­de werk, gebruik van drie, mij tot dan toe onbekende, fan­tas­tische tovertrucs. Later merkte ik dat veel anderen Mensa voor­gin­gen of volgden.

1. Functie wijzigt iets in een werkmap
   Dat functies een resultaat produceren op basis van argumentwaarden … nogal wiedes. Maar dat je met een, in VBA geprogrammeerde functie, een UDF (User Defined Function), ook een celwaarde of -commentaar, vorm, een gra­fiek of iets dergelijks kunt wijzigen; dat was een nieuw inzicht. Het werkt als volgt:
   De functie heeft een naam (Dinges) en een of meer argumenten (ArgA, ArgB, …). Maar in tegenstelling tot het normale functieprogrammeerwerk krijgt de functie geen eindwaarde in de finale coderegel, dus iets als Dinges = ArgA * ArgB. Nee, de functie is gevuld met (een) opdrachtregel(s) zoals je die in gewo­ne macro’s tegenkomt. Bijvoorbeeld:

   Function WijzigGrafiekType(GrafiekNaam, GrafiekTypeNummer)
     ActiveSheet.Shapes(GrafiekNaam).Chart.ChartType = _
         GrafiekTypeNummer
   End Function

   In een cel plaatse men =ALS.FOUT(WijzigGrafiekType(A1;A2);”Niet toegewezen nummer.”), in A1 de naam van de grafiek en in A2 bijvoorbeeld 51. 51 staat voor Kolomdiagram, gegroepeerd. Bij wijziging van dit getal verandert de verwezen grafiek van type. De ‘retourwaarde’ in de cel is 0 tenzij er een fout optreedt.
   Dit voorbeeld – en enkele andere – zijn te vinden in Macros.xlsm, blad XXI. Er zitten meer mooie UDF’s in deze map.

2. Hyperlink – MouseOverEvent
   Het is lastig om de truc in tekst te verklaren en een werkend voorbeeld is weer lastig om te begrijpen. Hier toch een poging tot beide. De enige manier om er grip op te krijgen: doe het tien keer zelf.Ik wist natuurlijk van het bestaan van de functie =HYPERLINK(locatie_link;makkelijke_naam). En dat, als je een cel met deze functie aanwijst – niet klikt – dat er dan een pop-up verschijnt. Dit lijkt wel een niet bestaande of -beschikbare gebeurtenis in het blad. (Het gaat overigens niet om de gebeur­tenis Worksheet_FollowHyperlink. Die werkt bij een met een hyperlink gevulde cel, gemaakt via Invoegen Hyperlink.)
   De truc is nu dat je voor locatie nìet naar een bestand of webpagina verwijst maar naar een UDF. In deze functie programmeer je een actie, ergens in de werkmap.
   De VBA-code van de functie is simpel zat:

   Function MouseOverEventFill()
     Range(“A1”).Value = “Een groots gebeuren!”
   End Function

   In een willekeurige cel plaatse men =HYPERLINK(MouseOverEventFill;”Wijs hier voor actie.”). Wijs naar de cel met de hyperlinkfunctie en cel A1 wordt gevuld ;-).
   Dit simpele voorbeeld is te vinden in HyperlinkMouseOverUDF.xlsm, blad HLinkVulWis.

   Nu iets ingewikkelder: de functie plaatst een bestaande waarde in een andere cel.
   De cellen-met-actie, bv. B4:B10, bevatten =HYPERLINK(MouseOver1(D4);D4) en dat verder t/m D10. De cellen D4:D10 bevatten de vriendelijke namen, tevens de te retour­neren waarden.
   Dit is de functiecode:

   Function MouseOver1(NaamFormule1)
   ‘NaamFormule1 is een willekeurige bereiknaam met een
   ‘willekeurige verwijzing of zelfs een niet-bestaande naam
     If NaamFormule1 = [Doelcel1].Value Then
       ‘Doe niks
     Else
       [Doelcel1] = NaamFormule1
     End If
   End Function
   

   [Doelcel1] is de bereiknaam van de doelcel maar Range(“A1”), Cells(x, y) of iets dergelijks mag ook.
   Wijs naar een van de actiecellen en de vriendelijke naam / de waarde verschijnt in de doelcel :-).
   Zie weer HyperlinkMouseOverUDF.xlsm, het blad MouseOverSourceTarget.

3. Dynamische namen
   Namen kunnen verwijzen naar – staan voor – een cel, een bereik (ook 2D of 3D) of een vast getal. Zie eventueel Formules&Functies.xlsm, de bladen NamenAlgemeen en NamenVoorbeelden.
   Het wordt spannend door een naam te ‘vullen’ met een functie. Een simpel voor­beeld kan zijn: met A4 als actieve cel =SOM(Blad1!A1:A3) als de vulling van de naam Optelling. Door plaatsing van =Optelling, ergens in Blad1, worden de drie bovenliggende cellen opgeteld. 
   Het gaat hier eigenlijk niet om een simpele SOM() maar om VERSCHUIVING(verw;rijen;kolommen;[hoogte];[breedte]).
   • verw is het ‘ankerpunt’. Die cel kun je selecteren of met INDIRECT() op­ha­len uit weer een verwezen cel.
   • rijen en kolommen bepalen het aantal rijen naar beneden en kolommen naar recht vanaf waar de functie waarden gaat ophalen. Negatieve waarden mogen zodat ook naar boven of naar links gewerkt wordt.
   • Met [hoogte] en [breedte] geef je op hoeveel cellen meedoen, verticaal en hori­zon­taal.
   Voor rijen, kolommen, [hoogte] en [breedte] kun je een getal typen of een cel met de waarde selecteren.
   Nog een stapje verder: met AANTAL() of AANTALARG() kunnen [hoogte] en [breedte] flexibel, dynamisch worden gemaakt.In veel gevallen is het boven­staan­de mijl op zeven. =SOM(A:A) telt gewoon alle waarden in kolom A op. Maar … bij grafieken ligt het een beetje anders. Als je daarbij 10 * 10 cellen kiest als bronbereik, dan leidt dat tot tien categorieën op de X-as, of ze nou gevuld zijn of niet. Zo niet als je de dynami­sche naam gebruikt als bron. Het aantal gevulde cellen bepaalt het beeld van de grafiek 😉.
   Zie HyperlinkMouseOverUDF.xlsm, het blad LineaChart en in Diversen­Gra­fie­ken­­aan­vulling.xlsm de bladen DynagrafiekPerRijI, II en III en ook Dynagra­fiek­Per­KolomI en II.

Deze blog werd gepubliceerd op Thursday, February 6th, 2014 om 21:24 en is gerubriceerd onder Opleiding & training. Je kunt reacties op deze blog volgen via de RSS-feed. Je kunt een reactie plaatsen, of trackbacken vanaf je eigen site.