i-CoDe : Consultancy & Development

Voeg uw eigen functies toe aan Excel

07 februari 2010

Wellicht gebruikt u, zelfs als u een doorwinterde Excel gebruiker bent, slechts een fractie van de mogelijkheden van dit programma.

Maar toch … precies die éne functie die u zo vaak nodig heeft, zijn ze in Redmond vergeten in te bouwen. Geen nood. In Excel kunt u eenvoudig zelf functies toevoegen en opslaan als een "add-in" of invoegtoepassing.  

Laten we als voorbeeld de berekening van de datum van paaszondag nemen. Dat is niet al te moeilijk, maar toch te complex om het even in één celletje te klaren met een formule. Het is nuttig, want m.b.v. de paasdatum kunnen we meteen de data berekenen van een aantal wettelijke feestdagen. Bovendien past het helemaal bij deze tijd van het jaar.

Het doel
De berekeningsmethode
De functie definiëren
De functie testen
Het werkblad opslaan
Zelfgedefinieerde functies gebruiken in meerdere werkbladen

Het doel

Het is altijd prettiger navigeren als je weet waar je naartoe moet. In ons geval is ons doel het volgende: we hebben een functie nodig die een jaartal neemt als parameter, en de datum van Pasen geeft als resultaat. In Excel zou dat er zo uitzien:

De berekeningsmethode

Hoe wordt de paasdatum berekend? Een volledige ontleding van de definitie van de paasdatum laat ik voor sites als wikipedia e.a. Na wat googlen vond ik volgende formules:

A = Mod(Year ÷ 19)

B = Int(Year ÷ 100)

C = Mod(Year ÷ 100)

D = Int(B ÷ 4)

E = Mod(B ÷ 4)

F = Int[(B + 8) ÷ 25]

G = Int[(B - F + 1) ÷ 3]

H = Mod[(19xA + B - D - G + 15) ÷ 30]

I = Int(C ÷ 4)

K = Mod(C ÷ 4)

L = Mod[(32 + 2xE + 2xI - H - K) ÷ 7]

M = Int[(A + 11xH + 22xL) ÷ 451]

P = Int[(H + L - 7xM + 114) ÷ 31]

Q = Mod[(H + L - 7xM + 114) ÷ 31]

Easter date will be on P month, Q+1 day

 

Wie over een gezonde dosis argwaan beschikt mag dit proberen te doorgronden, maar voor het gemak neem ik dit zonder meer aan als waarheid.

De functie definiëren

Tijd om de handen uit de mouwen te steken:

• Start Excel (u krijgt automatisch een nieuw werkblad)
• Druk Alt+F11 om de VBA editor te starten. In Excel worden functies gedefinieerd in “VBA” (Visual Basic for Applications), een vrij eenvoudige programmeertaal.
• Rechtsklikken in het vak “VBA project”, en kies dan in het menu “Invoegen” en “module”
  
• In de nieuwe module typt u letterlijk het volgende:
  
  Function PaasDatum(jaar As Integer) As Date
  End Function
  
  VBA helpt door de laatste lijn zelf in te voegen wanneer u de eerste bevestigt met Enter

Wat u nu ziet in de VBA editor is het standaard skelet voor een functie. Die begint steeds met het woord “Function” en eindigt met “End Function”. Daartussen schrijft u formules en instructies om de gewenste berekeningen uit te voeren.

In ons geval kunnen we de bovenstaande formules bijna letterlijk gebruiken om de functie af te werken. De volledige functie ziet er als volgt uit:

Function PaasDatum(jaar As Integer) As Date
  a = jaar Mod 19
  b = Int(jaar / 100)
  c = jaar Mod 100
  d = Int(b / 4)
  e = b Mod 4
  f = Int((b + 8) / 25)
  g = Int((b - f + 1) / 3)
  h = (19 * a + b - d - g + 15) Mod 30
  i = Int(c / 4)
  k = c Mod 4
  l = (32 + 2 * e + 2 * i - h - k) Mod 7
  m = Int((a + 11 * h + 22 * l) / 451)
  p = Int((h + l - 7 * m + 114) / 31)
  q = (h + l - 7 * m + 114) Mod 31
  PaasDatum = DateSerial(jaar, p, q + 1)
End Function

Let op de voorlaatste lijn. Het lijkt alsof we het resultaat toewijzen aan een variabele met de naam “PaasDatum”, dezelfde naam dus als de naam van onze functie. Dat is de VBA manier om aan Excel duidelijk te maken wat de uitkomst is van onze berekeningen, m.a.w. het resultaat van een functie wordt steeds op die manier doorgegeven naar Excel. 

That’s it! U hebt nu een functie toegevoegd aan het standaardrepertoire van Excel. 

De functie testen

Eens kijken of het werkt …

• Navigeer terug naar het (nog lege) Excel-werkblad .
• Typ in cel B2 het getal 2010
• Typ dan in cel C2 de formule =PaasDatum(B2), druk Enter.

Wat verschijnt er: 40272! Oei, dat is niet het verwachte antwoord. Wat is er aan de hand?

Het resultaat van onze functie is wel degelijk juist. Achter de schermen behandelt Excel datums als gewone getallen. Elke dag heeft zijn nummer. We moeten Excel alleen nog een handje helpen om het resultaat terug om te vormen naar een datum die wij “humans” kunnen begrijpen.

Dat doet u door een andere celopmaak te kiezen. In plaats van “Standaard” kiest u “Korte datumnotatie”, en dan verschijnt er wel degelijk “04/04/2010”. En dat lijkt mij juist. U kunt naar believen het jaartal in B2 wijzigen en vaststellen hoe de datum in cel C2 volgt.

Het werkblad opslaan

Al dat werk moet natuurlijk bewaard worden. Let op! U moet de werkmap opslaan als een “Excel-werkmap met macro’s”. In de gewone indeling “Excel-werkmap” zal uw functie niet mee opgeslagen worden. Dat is één van de beveiligingen die in versie 2007 ingebouwd werd.

Zelfgedefinieerde functies gebruiken in meerdere werkbladen

Natuurlijk wilt u al dat werk graag in meer dan één werkblad gebruiken. Dat kan heel gemakkelijk door het werkblad op te slaan als een “Excel Invoegtoepassing”:

• Maak het werkblad leeg, zodat alleen de functiedefinitie in de module overblijft.
• Kies “Opslaan als…” en kies als indeling “Excel Invoegtoepassing”.
• Als u de nieuwe functie steeds ter beschikking wilt hebben, dan moet u zorgen dat de nieuwbakken invoegtoepassing ingeladen wordt telkens als Excel start. Dat doet u zo:
• Klik op het office-menu en en klik dan op “Excel-opties” (rechts onderaan in het menu)
• Selecteer in de linkse kolom “Invoegtoepassingen”
• Klik op de knop “Start”
• Zet een vinkje naast “PaasDatum”
  
• Klik OK
• Sluit Excel, open Excel opnieuw en test de functie PaasDatum in een nieuw werkblad. U zult zien dat de functie onmiddellijk beschikbaar is in nieuwe werkbladen, zonder dat u daar nog iets voor hoeft te doen. Het nieuwe werkblad kunt u ook opslaan als een gewoon werkblad zonder macro’s.

Let wel op als u dit laatste werkblad wilt delen met anderen. Als iemand anders het op zijn PC wil gebruiken, zal hij ook uw invoegtoepassing nodig hebben.

---