VBA Excel

Excel als krachtig hulpmiddel bij conversies

Bij veel ICT trajecten spelen datamigratie en conversie een belangrijke rol. Het succes van het project is in belangrijke mate afhankelijk van de kwaliteit van de migratie en eventuele conversie. In veel gevallen kan MS Excel worden gebruikt voor dit proces. Zeker wanneer er gebruik wordt gemaakt van de geavanceerde functies van dit programma en het geïntegreerde “Visual Basic for Applications”(VBA).

Voldoende capaciteit

De meest recente versies van Excel kunnen grote hoeveelheden data aan. Een werkblad kan tegenwoordig ruim 1 miljoen regels en meer dan 16.000 kolommen aan. Daarnaast kan een Excel bestand een onbeperkt aantal werkbladen bevatten, dus wordt je slechts beperkt door het maximaal beschikbare aanwezige geheugen. In heel veel gevallen biedt Excel dan ook voldoende capaciteit om de datamigratie en conversie te kunnen ondersteunen. Slechts bij zeer grote hoeveelheden transactiegegevens is het noodzakelijk om gespecialiseerde database software te gebruiken.

Toegankelijk voor gebruikers

Voor gebruikers en key-users is Excel heel toegankelijk. Bijna alle zakelijke gebruikers beschikken over MS Office. Dit heeft als bijkomend voordeel dat onderlinge uitwisseling van gegevens zeer eenvoudig is. Daarnaast kunnen de gebruikers zelf de conversieregels opstellen en testen. Mede hierdoor kan dure (tijdelijke) capaciteit van externe conversie specialisten worden beperkt of teruggedrongen.

Transparantie en vertrouwen

Een ander voordeel voor de gebruiker is dat het hele conversieproces transparanter is en geen ondoorzichtige “black-box” wordt. Dit heeft tot gevolg dat men het migratie- en conversieproces beter gaat begrijpen, wat weer leidt tot meer vertrouwen. Door een betere samenwerking tussen ICT en de gebruikers in een omgeving die ze allebei begrijpen, zullen veel fouten vermeden kunnen worden. Dit toegenomen inzicht en vertrouwen is echter geen vervanging van het goed testen van de migratie. Testen blijft van essentieel belang en gebruikers zijn beter in staat om op een goede manier te testen.

Migratie- en conversieregels

Een migratie bestaat in principe uit een aantal opeenvolgende migratie- of conversieregels. Elke regel bepaalt welke waarde vanuit het oorspronkelijke systeem naar het nieuwe systeem wordt overgezet en de wijze waarop dit gebeurt. Daarbij worden gegevens in de meeste gevallen van de ene database naar de andere database overgezet.

Doordat databases bestaan uit 1 of meerdere tabellen, is dit te vergelijken met een Excel werkmap met 1 of meerdere werkbladen. Dit proces kan eenvoudig in Excel worden uitgevoerd, eventueel met een of meerdere tussenstappen, zodat het voor de gebruiker overzichtelijk blijft.

Er is sprake van een conversie als de data ook wordt gewijzigd. Een voorbeeld daarvan zijn bedrijfslocaties in het oude systeem worden aangeduid met plaatsnamen en in het nieuwe systeem worden vastgelegd als nummers. In dat geval worden de locatienamen met behulp van een conversietabel geconverteerd naar het nieuwe nummer.

VBA-Excel2

Met de standaard functies binnen Excel zoals “verticaal zoeken” en “horizontaal zoeken”, kan dan al heel veel bereikt worden. Excel kent daarbij geen beperking in het aantal te gebruiken conversietabellen.

Uitzonderingen op de regel eenvoudig te verwerken

In bijna alle gevallen zijn er wel uitzonderingen op de algemene migratie- en conversieregels. Eventueel kunnen deze uitzonderingen handmatig in het migratiebestand worden aangepast. Beter is het echter, om gebruik te maken van dezelfde werkwijze als bij de conversie. In dat geval maken we gebruik van een ‘uitzonderingentabel’ in plaats van een conversietabel. De enige randvoorwaarde is dat we binnen de desbetreffende migratie- of exstap de uitzondering op de regel kunnen identificeren.

Iteratief en repeteerbaar proces

Door de oorspronkelijke data ongewijzigd in Excel in te lezen en vervolgens stap voor stap om te zetten naar het gewenste resultaat, kan het proces meerdere keren herhaald worden. Het resultaat kan worden getest en de bevindingen worden teruggekoppeld. In veel gevallen leidt dit tot het opschonen en corrigeren van de data in het bronsysteem en het aanpassen van de migratie- of conversieregels. Vervolgens kan de data eenvoudig opnieuw worden verwerkt in Excel waarna de resultaten van de aanpassing weer beoordeeld kunnen worden.

Als de hoeveelheid data groot is kan er het beste gewerkt worden met VBA code. Elke migratie- of conversiestap zoals de gebruiker die zou uitvoeren, kan apart worden geprogrammeerd in (sub)routines. Door de stappen in de juiste volgorde na elkaar uit te laten voeren ontstaat het gewenste resultaat.

Deze werkwijze biedt bovendien nog twee voordelen. Wanneer je alle stappen in kleine routines verwerkt met duidelijk herkenbare namen, krijg je automatisch een goede beschrijving van het proces. Daarnaast is het zeer eenvoudig om stappen om draaien of in een andere volgorde te zetten.

Tips en trucs

  1. Plaats elke tabel op een apart werkblad.
  2. Maak bij grotere hoeveelheden data gebruik van VBA. Laat dit eventueel doen door een specialist, want hoe beter geprogrammeerd, hoe sneller de stappen doorlopen worden.
  3. Gebruik voor stamdata aparte Excel bestanden, zodat iedereen met dezelfde data werkt