Excel, UDF’s en grafieken
Als ik het zeggen mag: ik vond mezelf aardig thuis in Excel [opzettelijke taalhaspel]. 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 Roberto Mensa. Het was me totaal een raadsel wat daar gebeurde. Ik heb er best een uurtje mee gespeeld voordat ik serieus aan de ontrafeling van dit wondertje begon.
Mensa maakt, behalve van al het bekende werk, gebruik van drie, mij tot dan toe onbekende, fantastische tovertrucs. Later merkte ik dat veel anderen Mensa voorgingen of volgden.
- 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 grafiek 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 gewone macro’s tegenkomt. Bijvoorbeeld:Function WijzigGrafiekType(GrafiekNaam, GrafiekTypeNummer)
ActiveSheet.Shapes(GrafiekNaam).Chart.ChartType = _
GrafiekTypeNummer
End FunctionIn 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. - 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 gebeurtenis 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 FunctionIn 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 retourneren 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. - 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 voorbeeld 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() ophalen 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 horizontaal.
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 bovenstaande 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 dynamische naam gebruikt als bron. Het aantal gevulde cellen bepaalt het beeld van de grafiek😉 .
Zie HyperlinkMouseOverUDF.xlsm, het blad LineaChart en in DiversenGrafiekenaanvulling.xlsm de bladen DynagrafiekPerRijI, II en III en ook DynagrafiekPerKolomI en II.