Mob. : 06 539 960 34   Mail: bjdvries@gmail.com   Mijn CV op Linkedin: http://nl.linkedin.com/in/bernarddevries

Onderstaande tekst heeft als doel herkenning op te roepen bij vakgenoten, zoals administrateurs,
rapporteurs en Excel VBA experts, binnen het vermogensbeheer.


Mijn ervaring met Excel VBA

Excel VBA Expert
Ik ben als VBA Expert werkzaam op de administratieve afdelingen van het Asset Management.
Afdelingen zoals accounting, backup en rapportage.
Mijn belangstelling voor VBA is met name ontstaan vanuit de rollen fondsadministrateur en rapporteur aan toezichthouders.
Voor de financiële instellingen waarvoor ik een opdracht heb uitgevoerd, komt het volgende steeds weer terug:
Aan de financiële administratie worden datamatrices geleverd door de interne automatisering en/of externe bronnen.
Met behulp van Excel, wordt deze data gevormd en geconverteerd tot de layout en/of het rapport dat verlangd wordt.
Deze dataconversie kan door data-toevoeging, calculatie, filtering en groepering zover gaan dat er sprake is van een
gereviseerde matrix cq database.

De financiële administrateur.
De financiële administrateurs werken doorgaans met deadlines en daarmee vaak onder tijdsdruk.
Naast de administratieve kennis wordt van hen grote vaardigheden gevraagd op het gebied van Excel.
Datamatrices aangeleverd door interne en externe bronnen moeten worden samengevoegd, geconverteerd, gefilterd,
gesorteerd en gecontroleerd.
Dit vraagt van de administrateur om geavanceerd om te gaan met Excel functies als filteren, sorteren, verticaal
en/of horizontaal zoeken, het doorgronden van grafische toepassingen en draaitabellen.
De administrateur moet vaardig zijn in het bouwen van lange, gecompliceerde formules en kennis hebben hoe ze werken.
Wanneer de ene complexe structuur, bijvoorbeeld een draaitabel, de bron is van de andere complexe structuur,
bijvoorbeeld ook een draaitabel, dan ontstaat de mogelijkheid van de blackbox.
Dat wil zeggen het verliezen van grip op de werking van programma’s en/of het programma is erg persoonsgebonden geworden.
Excel VBA geeft de mogelijkheid om de formulestructuur meer eenvoudig te houden.
Ingewikkelde calculaties worden niet in de cel gedefinieerd maar worden binnen het script opgedeeld in
eenvoudige calculaties, en worden vervolgens binnen het script uitgevoerd.
De uitkomst wordt als een “hard” getal in de cel weergegeven.

Al dan niet complexe formules worden ook toegepast binnen een matrix met een groot aantal regels cq records.
Hoe groter de matrix hoe langer het duurt dat alle formules gecalculeerd zijn.
Het is geen uitzondering dat de responstijd kan oplopen van 15 minuten tot een half uur.
Responstijden van Excel programma’s en deadlines gaan vaak niet goed samen.
Uit oogpunt van een snellere responstijd zal meestal de IT expert adviseren dat een matrix vanaf +/- 10.000 records
beter ondergebracht kan worden in een database binnen Microsoft Access (het zusje van Excel).
Daar is zondermeer wat voor te zeggen vanwege tal van voordelen die Access biedt.
Alleen mijn ervaring is dat dat meestal niet gebeurd op de financiële administraties.
En dat laat zich ook verklaren.
Ondanks overeenkomsten met Excel vraagt de Microsoft Access database een andere kennis dan Excel.
Er is geen tijd om daarin te verdiepen vanwege deadlines.
Ook zal menig administrateur overwegen dat zijn specifiek administratieve vakkennis niet moet
wijken voor operationele kennis van Excel en Access.
De opmerking die ik vaak hoor is, dat men teveel tijd kwijt is aan gegevensverwerking om genoeg tijd over te houden
voor analyse en interpretatie.
Ik ben aangenomen als administrateur en niet als it'er.
Dus laten we het alleen houden bij Excel.

Excel VBA
Wat is Excel VBA?
Simpel gezegd: Met Excel VBA kunnen herhalende Excel instructies door de administrateur ondergebracht worden
in een script.
Bijvoorbeeld iedere dag moet binnen dezelfde matrix, met nieuwe data weliswaar, dezelfde sortering worden gemaakt,
dezelfde filter uitgevoerd en dezelfde draaitabel worden gemaakt.
Door middel van een druk op de knop worden bovengenoemde handelingen door het script uitgevoerd.
De bewustwording dat deze Excel VBA mogelijkheid bestaat en dat deze mogelijkheid voor iedere office-gebruiker ter
beschikking staat, zie ik de laatste jaren steeds meer uitgedrukt in de functieomschrijvingen van de vacatures.
Vaak is het hebben van Excel VBA kennis een pre, maar het wordt ook steeds meer als een voorwaarde in de
functieomschrijvingen genoemd.

Excel VBA alleen een script van gebruikersinstructies?
De simpele definitie van Excel VBA hierboven is voor de beeldvorming, maar Excel VBA kan veel meer.
Excel VBA kan calculaties uitvoeren binnen het programma cq script, waarbij overbodige handelingen, uitgevoerd door
het Excel-programma zelf, kunnen worden vermeden.

Voorbeeld van “overbodige” handelingen door het Excel-programma zelf:
Wanneer er een formule in een cel van de spreadsheet gewijzigd wordt dan zou de herberekening voor die gewijzigde cel
alleen voldoende zijn, maar de praktijk is dat Excel alle cellen die een formule bevatten herberekend.
Wanneer er sprake is van bijvoorbeeld 100 records, dan gebeurd dit in een fractie van een seconde.
De overbodige herberekeningen worden niet als hinderlijk ervaren, en de zekerheid wordt verkregen dat elke verandering
onmiddellijk wordt doorgevoerd.
Maar ingeval van bijvoorbeeld 50.000 records, kan de wijziging van één cel een verwerkingstijd betekenen van
tientallen seconden of enkele minuten, omdat de hele sheet herberekend wordt.
Dan wordt er vaak gekozen om binnen Excel de optie 'calculeren' te veranderen van 'automatisch' naar 'handmatig'.
Daarmee kunnen allerlei wijzigingen in de spreadsheet gedaan worden zonder dat enige calculatie plaatsvindt.
Calculatie vindt plaats wanneer de toets F9 wordt aangeklikt.
Dat laatste wordt nog al eens vergeten, met een foutieve output als gevolg.

Wanneer een cel herberekend wordt, gebeurd op de 'achtergrond' meer dan alleen calculeren.
Bijvoorbeeld na de calculatie van iedere cel wordt het scherm “geupdate” om het nieuwe resultaat te laten zien.
Dus bij 50.000 records wordt op zijn minst 50.000 keer het scherm “gerefreshed”.
Zo zijn er nog een aantal handelingen te noemen die worden uitgevoerd na de calculatie van één cel.
Binnen Excel VBA is het mogelijk om zo te instrueren dat het programma zich alleen bezig houdt met het calculeren.
Dus nadat record 50.000 is gecalculeerd, dan in één keer alle records “refreshen”.
Dat levert enorme besparingen op ten aanzien van responstijden.
Van bijvoorbeeld van vijf minuten naar tien seconden of nog minder.

Intern geheugen
Kennis en toepassing van de werking van het interne geheugen via VBA heeft enorme voordelen wanneer er gewerkt
wordt met grote aantallen records. (Big data)
Verwerkingstijden cq responstijden zijn enkele procenten in vergelijking met de responstijden van procedures,
die niet van toepassingen gebruikmaken om met het intern geheugen efficiënt om te gaan.
Wordt dit wel toegepast dan kan binnen korte tijd een matrix (Database) worden gecreëerd die vloeiend aansluiting
vindt bij rapportages zoals bijvoorbeeld DRA en FTK of andere rapporten .
De haalbaarheid van deadlines wordt aanzienlijk verbeterd.

Microsoft Access
Wordt er wel van Access gebruikgemaakt of van Oracle dan is een dergelijke database met Excel VBA uitstekend te benaderen.
Dit kan via Excel VBA waarbij in het script SQL instructies zijn opgenomen.
Dit kan zo geregeld worden dat de Access database voor het oog van de gebruiker niet in beeld komt.

Inzetbaarheid
De inzetbaarheid maakt Excel VBA zo aantrekkelijk.
Want iedere professionele financiële instelling, werkt met Microsoft Office.
Het materiaal is reeds aanwezig.
Mogelijk dat in het menu opties van Excel de box “Ontwikkelaar” of “Developer” aangevinkt moet worden om
het menu in de menubalk zichtbaar te krijgen, maar dat is ook het enige.
De verschillen tussen de versies Office 2003, 2007, 2010 en 2013 zijn wat VBA betreft niet zo, dat er sprake is van
totaal andere programmastructuren.
Al gaat de voorkeur naar de modernste Office versie, maar een oudere versie is met betrekking tot Excel VBA geen hinderpaal.

Is het wenselijk dat Excel VBA kennis permanent op een administratieve afdeling aanwezig is?
Ik denk van wel.
Ik ben zeer erkentelijk voor de ervaring die ik heb opgedaan bij de uitvoering van Excel VBA opdrachten,
waarbij de opdrachtgever mij liet samenwerken met een collega (in vaste dienst) die zelf deskundig was op het
gebied van Excel VBA.
De samenwerking is voor mij en deze collega een enorme boost geweest om goede VBA programma’s te ontwikkelen
en kennis te delen en daarmee veel nieuwe inzichten op te doen.
De opdracht kwam tot een zeer goede uitvoering en met een mooi resultaat.
En de kennis van de ontwikkeling bleef binnen de organisatie van de opdrachtgever.
Zodat de mogelijkheid van aanpassing, wijzigingen, etc. permanent ter beschikking bleef staan.

Bernard, 7 december 2015.