Omdat de vele en lange formules in mijn werkblad wat onoverzichtelijk worden, zou ik graag een methode hebben om cellen in een bepaalde kolom te kunnen addresseren op kolomnaam in plaats van de kolomletter.
Ter verduidelijking een vereenvoudigd voorbeeld:
In plaats van de formules in kolom C zou ik dus graag formules in de trend van kolom D willen gebruiken, zodat ik bij het terugkijken en bewerken van de formules in een oogopslag kan zien wat er gebeurt in plaats van steeds horizontaal te moeten scrollen om te zien welke gegevens er ook alweer in kolom AQ3 staan.
Nu had ik het volgende bedacht: ik definieer een naam voor de kolomnamen (A1 = "aantal" etc) en maak de volgende formule in VBA:
Op zich werkt dit prima, maar het probleem is het vernieuwen van de waardes: User Defined Functions worden alleen opnieuw berekend als een van de argumenten verandert. Gezien het argument van mijn functie de kolomtitel is en niet de daadwerkelijke cel met de data die gebruikt wordt, wordt de functie dus niet automatisch herberekend. Ik heb hier 2 mogelijke oplossingen voor gevonden:
- Het gebruik van Application.Volatile (True); hierbij wordt de UDF echter voor iedere occurence (orde N) uitgevoerd bij iedere wijziging van een willekeurige cel in het werkblad
- De namen definieren voor de hele kolom in plaats van alleen de titelcel; hierbij wordt de UDF weliswaar uitsluitend uitgevoerd bij wijzigingen in de betreffende kolom, maar met enkele honderden rijen in mijn werkblad is orde N nog steeds teveel om efficient data in te kunnen voeren in die kolom
Mijn vragen:
- is er misschien een standaardfunctie voor mijn doel (adresseren op kolomtitel in plaats van kolomletter) die ik over het hoofd heb gezien?
- is er iemand hier die een idee heeft hoe ik het update-probleem kan tackelen en hoe ik er dus voor kan zorgen dat bij het wijzigen van een cel in de invoerkolom, mijn UDF slechts voor de bijbehorende rij uitgevoerd wordt (orde 1)?
- of heeft iemand nog andere suggesties waarmee ik mijn doel kan bereiken? Voor de duidelijkheid: het moet in het uiteindelijke gebruik dus echt 1 functie zijn, aangezien het invoegen van meerdere geneste functies het geheel juist ingewikkelder zou maken in plaats van overzichtelijker.
Ter verduidelijking een vereenvoudigd voorbeeld:
Code:
A B C D
1 aantal prijs totaal gewenst:
2 10 1.95 =A2*B2 =at(aantal)*at(prijs)
3 20 2.95 =A3*B3 =at(aantal)*at(prijs)In plaats van de formules in kolom C zou ik dus graag formules in de trend van kolom D willen gebruiken, zodat ik bij het terugkijken en bewerken van de formules in een oogopslag kan zien wat er gebeurt in plaats van steeds horizontaal te moeten scrollen om te zien welke gegevens er ook alweer in kolom AQ3 staan.
Nu had ik het volgende bedacht: ik definieer een naam voor de kolomnamen (A1 = "aantal" etc) en maak de volgende formule in VBA:
Code:
Function at(kolom_rangenaam As range)
at = Cells(Application.Caller.row, kolom_rangenaam.Column)
End FunctionOp zich werkt dit prima, maar het probleem is het vernieuwen van de waardes: User Defined Functions worden alleen opnieuw berekend als een van de argumenten verandert. Gezien het argument van mijn functie de kolomtitel is en niet de daadwerkelijke cel met de data die gebruikt wordt, wordt de functie dus niet automatisch herberekend. Ik heb hier 2 mogelijke oplossingen voor gevonden:
- Het gebruik van Application.Volatile (True); hierbij wordt de UDF echter voor iedere occurence (orde N) uitgevoerd bij iedere wijziging van een willekeurige cel in het werkblad
- De namen definieren voor de hele kolom in plaats van alleen de titelcel; hierbij wordt de UDF weliswaar uitsluitend uitgevoerd bij wijzigingen in de betreffende kolom, maar met enkele honderden rijen in mijn werkblad is orde N nog steeds teveel om efficient data in te kunnen voeren in die kolom
Mijn vragen:
- is er misschien een standaardfunctie voor mijn doel (adresseren op kolomtitel in plaats van kolomletter) die ik over het hoofd heb gezien?
- is er iemand hier die een idee heeft hoe ik het update-probleem kan tackelen en hoe ik er dus voor kan zorgen dat bij het wijzigen van een cel in de invoerkolom, mijn UDF slechts voor de bijbehorende rij uitgevoerd wordt (orde 1)?
- of heeft iemand nog andere suggesties waarmee ik mijn doel kan bereiken? Voor de duidelijkheid: het moet in het uiteindelijke gebruik dus echt 1 functie zijn, aangezien het invoegen van meerdere geneste functies het geheel juist ingewikkelder zou maken in plaats van overzichtelijker.
