Inhoud
In dit artikel gaan we enkele UDF’s uit de add-in Handigheidjes maken. Weet u het nog? Om de oplossingen (ook wel wortels genoemd) van een tweedegraads vergelijking te krijgen kun je gebruik maken van de beroemde (en evenzo beruchte) ABC-formule. Deze luid: En dit kunnen we vrij eenvoudig in een UDF omzetten. We moeten wel rekening houden met de verschillende waarden die a, b en c kunnen hebben. We onderscheiden:Inleiding
Het is van belang dat u de training Excel Programmeren heeft gevolgd.
We gaan hier niet uitgebreid in op de VBA-code maar, indien noodzakelijk, meer op de wiskundige inhoud.ABC-Formule
En nu zijn er twee smaken die afhangen van de waarde van de discriminant (=b2-4ac).
Dus eerst de discriminant bepalen:
- discriminant>=0: Dan volgen de oplossingen uit de ABC-formule;
- discriminant<0: Dan zijn de oplossingen complexe getallen. Dat zijn getallen p+qi waarbij p, q ∈ ℝ en i2=-1.
Nu krijgt p de waarde -b/2a en q de waarde van ± √(-discriminant)/2a i.
Het resultaat van de UDF geven we terug als een array met x1, x2 en de discriminant.
In code kan het er als volgt uitzien:
Function ABC_Formule(ByVal a, ByVal b, ByVal c) As Variant
Dim d As Variant
Dim x1 As Variant, x2 As Variant
If a = 0 And b = 0 And c = 0 Then
ABC_Formule = Array(“R”, “R”, “n.v.t.”)
ElseIf a = 0 And b = 0 Then
ABC_Formule = Array(“n.v.t.”, “n.v.t.”, “n.v.t.”)
ElseIf a = 0 Then
x1 = -c / b
x2 = x1
ABC_Formule = Array(x1, x2, “n.v.t.”)
Else
d = b * b – 4 * a * c
If d >= 0 Then
x1 = (-b – Sqr(d)) / (2 * a)
x2 = (-b + Sqr(d)) / (2 * a)
Else
x1 = -b / (2 * a) & ” ” & -Sqr(-d) / (2 * a) & ” i”
x2 = -b / (2 * a) & ” + ” & Sqr(-d) / (2 * a) & ” i”
End If
ABC_Formule = Array(x1, x2, d)
End If
End Function
Uiteraard kan deze functie nog robuuster worden gemaakt met een On Error systeem.
DiagSom
Stel dat je een vierkante matrix hebt en je wilt van iedere kolom, rij en hoofddiagonalen de som weten.
Voor de rijen en kolommen kun je gewoon de functie SOM gebruiken, maar voor de hoofddiagonalen is er geen standaard functie.
Die gaan we dus maar zelf maken.
Kijk eens naar het voorbeeld:
| 16 | |||
| 2 | 4 | 6 | 12 |
| 1 | 3 | 5 | 9 |
| 7 | 8 | 9 | 24 |
| 10 | 15 | 20 | 14 |
De vetgedrukte getallen krijg je met de SOM-functie. Voor de rode getallen bestaat (nog) geen functie.
Een vierkante matrix heeft dus evenveel rijen als kolommen en heeft twee hoofddiagonalen, namelijk één van linksboven naar rechtsonder en één van linksonder naar rechtsboven. Daar moeten we dus rekening mee houden.
We noemen de UDF DiagSom en deze krijgt twee parameters. De eerste is de reeks waarvan de DiagSom willen berekenen en de tweede is een Boolean die aangeeft van welke hoofddiagonaal we de som willen bepalen. Deze tweede parameter maken we optioneel met als default waarde de hoofddiagonaal van linksboven naar rechtsonder.
In de functie moeten we eerst bepalen of de matrix (reek) vierkant is, want anders zijn er geen hoofddiagonalen.
Verder zullen we per cel bepalen of er een numerieke waarde instaat en zo ja dan wordt deze meegenomen in de te bepalen som.
Een en ander kan er als volgt uitzien:
Function DiagSom(ByVal Reeks As Range, Optional lbro As Boolean = True) As Variant
Dim r As Integer, k As Integer
Dim som As Variant
som = 0
If Reeks.Columns.Count = Reeks.Rows.Count Then
For r = 1 To Reeks.Rows.Count
For k = 1 To Reeks.Columns.Count
If lbro Then
If r = k Then
If IsNumeric(Reeks(r, k).value) Then
som = som + Reeks(r, k).value
End If
End If
Else
If r = Reeks.Columns.Count – k + 1 Then
If IsNumeric(Reeks(r, k).value) Then
som = som + Reeks(r, k).value
End If
End If
End If
Next k
Next r
DiagSom = som
Else
DiagSom = “n.v.t.”
End If
End Function
Feestdagen
Hoe handig is het om de feestdagen in een bepaald jaar op een rijtje te hebben? Daar gaan we in deze paragraaf voor zorgen.
Als voorbeeld de feestdagen in het jaar 2026:
| 2026 | ||
| Nieuwjaar | do | 1-1-2026 |
| Drie Koningen | di | 6-1-2026 |
| Valentijnsdag | za | 14-2-2026 |
| Carnaval | zo | 15-2-2026 |
| Goede vrijdag | vr | 3-4-2026 |
| Pasen | zo | 5-4-2026 |
| Tweede paasdag | ma | 6-4-2026 |
| Koningsdag | ma | 27-4-2026 |
| Dodenherdenking | ma | 4-5-2026 |
| Bevrijdingsdag | di | 5-5-2026 |
| Moederdag | zo | 10-5-2026 |
| Hemelvaart | do | 14-5-2026 |
| Pinksteren | zo | 24-5-2026 |
| Tweede pinksterdag | ma | 25-5-2026 |
| Vaderdag | zo | 21-6-2026 |
| Prinsjesdag | di | 15-9-2026 |
| Sinterklaas | za | 5-12-2026 |
| Kerst | vr | 25-12-2026 |
| Tweede kerstdag | za | 26-12-2026 |
| Oudejaarsdag | do | 31-12-2026 |
Veel Christelijke feestdagen hangen af van wanneer Pasen valt. Volgens de Christelijke kerk val Pasen op de eerste zondag na de eerste volle maan in de lente. De lente valt, volgens de kerk, op 21 maart.
De feestdagen Carnaval, Goede vrijdag, tweede paasdag, Hemelvaart en eerste en tweede Pinksterdag zijn allemaal afhankelijk van eerst paasdag.
De andere feestdagen hebben een vaste datum in het jaar.
We moeten dus beginnen met een UDF die de eerste Paasdag berekent. Deze functie heeft als parameter het jaar waarvan we de eerste Paasdag willen bepalen.
Deze functie maakt gebruik van het algoritme van Meeus, Jones en Butcher. De uitleg staat als commentaar tussen de regels:
Function Pasen(ByVal jaar As Long) As Date
‘gebruikt het algoritme van Meeus/Jones/Butcher
Dim a, b, c, d, e, g, h, i, j, k, l, m, n, o
‘maanstanden herhalen zich elke 19 jaar; a bepaalt waar in deze cyclus
a = jaar Mod 19
‘b bepaalt eeuwgetal (19 of 20 etc.)
b = jaar \ 100
‘c bepaalt jaar binnen de eeuw (78 of 26 etc.)
c = jaar Mod 100
‘d, e, g berekenen correcties voor schrikkeljaren en kleine afwijkingen in maandcycli
d = b \ 4
e = b Mod 4
g = (8 * b + 13) \ 25
‘h, i, j bepalen de epacta, dit is de ouderdom van de maan op 1 januari van het jaar
h = (11 * (b – d – g) – 4) \ 30
i = (7 * a + h + 6) \ 11
‘j geeft aantal dagen dat kerkelijke volle maan valt na 21 maart
j = (19 * a + (b – d – g) + 15 – i) Mod 29
‘k, l, m bepalen wanneer het de zondag na de volle maan is (daar Pasen op een zondag moet vallen)
k = c \ 4
l = c Mod 4
m = ((32 + 2 * e) + 2 * k – l – j) Mod 7
’n bepaalt de maand (maart (3) of april (4))
n = (90 + (j + m)) \ 25
‘o bepaalt de dag in de maand
o = (19 + (j + m) + n) Mod 32
Pasen = CDate(o & “-” & n & “-” & jaar)
End Function
Pinksteren valt 49 dagen na Pasen. De UDF Pinksteren bepaalt eerst de datum van Pasen en telt daar door middel van de functie DateAdd er 49 dagen bij op:
Function Pinksteren(ByVal jaar As Long) As Date
Dim d, p
p = HJG_Pasen(jaar)
p = CDate(p)
d = DateAdd(“d”, 49, p)
Pinksteren = d
End Function
Hemelvaart valt 10 dagen voor Pinksteren:
Function Hemelvaart(ByVal jaar As Long) As Date
Dim d, p
p = Pinksteren(jaar)
p = CDate(p)
d = DateAdd(“d”, -10, p)
Hemelvaart = d
End Function
Voor moederdag, vaderdag en Prinsjesdag geldt dat ze allemaal op een bepaalde weekdag in een bepaalde maand vallen.
Moederdag valt op de tweede zondag van Mei. In de UDF moeten we dus beginnen op 1 Mei en dan het aantal zondagen tellen. Met de functie Weekday bepalen we de dag in de week van een bepaalde datum:
Function Moederdag(ByVal jaar As Long) As Date
Dim sd As Date
Dim wd As Integer
Dim teller As Integer
sd = CDate(“1-5-” & jaar)
teller = 0
Do While teller < 2
wd = Weekday(sd)
If wd = 1 Then
teller = teller + 1
End If
sd = DateAdd(“d”, 1, sd)
Loop
Moederdag = DateAdd(“d”, -1, sd)
End Function
Vaderdag is op de derde zondag van juni:
Function Vaderdag(ByVal jaar As Long) As Date
Dim sd As Date
Dim wd As Integer
Dim teller As Integer
sd = CDate(“1-6-” & jaar)
teller = 0
Do While teller < 3
wd = Weekday(sd)
If wd = 1 Then
teller = teller + 1
End If
sd = DateAdd(“d”, 1, sd)
Loop
Vaderdag = DateAdd(“d”, -1, sd)
End Function
En Prinsjesdag valt op de derde dinsdag van September:
Function Prinsjesdag(ByVal jaar As Long) As Date
Dim sd As Date
Dim wd As Integer
Dim teller As Integer
sd = CDate(“1-9-” & jaar)
teller = 0
Do While teller < 3
wd = Weekday(sd)
If wd = 3 Then
teller = teller + 1
End If
sd = DateAdd(“d”, 1, sd)
Loop
Prinsjesdag = DateAdd(“d”, -1, sd)
End Function
Voor de UDF Feestdagen, die we zo gaan presenteren, maken we twee versies. Een uitgebreide versie en één met alleen de officiële Nederlandse feestdagen.
Om het eenvoudig te maken later de UDF uit te breiden met andere feestdagen is de volgorde waarin de feestdagen worden bepaalt in de UDF zelf niet van belang. Voordat de UDF de resultaten weergeeft, in een Array, wordt alles eerst gesorteerd op datum.
De feestdagen komen in de UDF in een tweedimensionale Array te staan die met een aparte Sorteer functie wordt gesorteerd. Omdat deze sorteerfunctie alleen binnen de module wordt gebruikt is deze als Private gedeclareerd. De parameter is in dit geval ByRef omdat deze zelfde array gesorteerd moet worden teruggegeven:
Private Sub Feest_Sorteer(ByRef opl() As String)
Dim d1, d2
Dim i, j
Dim tmp(1 To 1, 1 To 3) As String
d1 = UBound(opl, 1)
d2 = UBound(opl, 2)
For j = 1 To d1
For i = 1 To d1 – 1
If CDate(opl(i, 3)) > CDate(opl(i + 1, 3)) Then
tmp(1, 1) = opl(i, 1)
tmp(1, 2) = opl(i, 2)
tmp(1, 3) = opl(i, 3)
opl(i, 1) = opl(i + 1, 1)
opl(i, 2) = opl(i + 1, 2)
opl(i, 3) = opl(i + 1, 3)
opl(i + 1, 1) = tmp(1, 1)
opl(i + 1, 2) = tmp(1, 2)
opl(i + 1, 3) = tmp(1, 3)
End If
Next i
Next j
End Sub
En dan nu de UDF waar het allemaal om draait. Vooraf nog even een opmerking over Koningsdag. Tot en met 2013 was dit Koninginnedag en werd gevierd op 30 April. Vanaf 2014 wordt Koningsdag op 27 April gevierd. Voor beide dagen geldt dat wanneer deze datum op een zondag valt de feestdag naar de zaterdag ervoor wordt verplaatst. De UDF houdt daar allemaal rekening mee:
Function FeestDagen(ByVal jaar As Long, Optional Uitgebreid As Boolean = False)
Dim dagen As String, dag As String
Dim vpasen, vpinksteren, vhemelvaart
Dim vnieuwjaar, vkoning, vkerst, vbevrijding
Dim opl(1 To 7, 1 To 3) As String
Dim oplext(1 To 20, 1 To 3) As String
dagen = “zomadiwodovrza”
dag = Mid(dagen, 2 * Weekday(CDate(“1-1-” & jaar)) – 1, 2)
vnieuwjaar = “1-1-” & jaar
opl(1, 1) = “Nieuwjaar”
opl(1, 2) = dag
opl(1, 3) = vnieuwjaar
vpasen = HJG_Pasen(jaar)
dag = Mid(dagen, 2 * Weekday(CDate(vpasen)) – 1, 2)
opl(2, 1) = “Pasen”
opl(2, 2) = dag
opl(2, 3) = vpasen
vhemelvaart = HJG_Hemelvaart(jaar)
dag = Mid(dagen, 2 * Weekday(CDate(vhemelvaart)) – 1, 2)
opl(3, 1) = “Hemelvaart”
opl(3, 2) = dag
opl(3, 3) = vhemelvaart
vpinksteren = HJG_Pinksteren(jaar)
dag = Mid(dagen, 2 * Weekday(CDate(vpinksteren)) – 1, 2)
opl(4, 1) = “Pinksteren”
opl(4, 2) = dag
opl(4, 3) = vpinksteren
dag = Mid(dagen, 2 * Weekday(CDate(“25-12-” & jaar)) – 1, 2)
vkerst = “25-12-” & jaar
opl(5, 1) = “Kerst”
opl(5, 2) = dag
opl(5, 3) = vkerst
If jaar < 2014 Then
dag = Mid(dagen, 2 * Weekday(CDate(“30-4-” & jaar)) – 1, 2)
vkoning = “30-4-” & jaar
If dag = “zo” Then
dag = Mid(dagen, 2 * Weekday(CDate(“29-4-” & jaar)) – 1, 2)
vkoning = “29-4-” & jaar
End If
Else
dag = Mid(dagen, 2 * Weekday(CDate(“27-4-” & jaar)) – 1, 2)
vkoning = “27-4-” & jaar
If dag = “zo” Then
dag = Mid(dagen, 2 * Weekday(CDate(“26-4-” & jaar)) – 1, 2)
vkoning = “26-4-” & jaar
End If
End If
If jaar < 2014 Then
opl(6, 1) = “Koninginnedag”
Else
opl(6, 1) = “Koningsdag”
End If
opl(6, 2) = dag
opl(6, 3) = vkoning
dag = Mid(dagen, 2 * Weekday(CDate(“5-5-” & jaar)) – 1, 2)
vbevrijding = “5-5-” & jaar
opl(7, 1) = “Bevrijdingsdag”
opl(7, 2) = dag
opl(7, 3) = vbevrijding
If Uitgebreid Then
Dim i As Integer, j As Integer
For i = 1 To 7
For j = 1 To 3
oplext(i, j) = opl(i, j)
Next j
Next i
oplext(8, 1) = “Goede vrijdag”
oplext(8, 2) = “vr”
oplext(8, 3) = DateAdd(“d”, -2, vpasen)
oplext(9, 1) = “Tweede paasdag”
oplext(9, 2) = “ma”
oplext(9, 3) = DateAdd(“d”, 1, vpasen)
oplext(10, 1) = “Tweede pinksterdag”
oplext(10, 2) = “ma”
oplext(10, 3) = DateAdd(“d”, 1, vpinksteren)
oplext(11, 1) = “Tweede kerstdag”
oplext(11, 2) = Mid(dagen, 2 * Weekday(CDate(“26-12-” & jaar)) – 1, 2)
oplext(11, 3) = “26-12-” & jaar
oplext(12, 1) = “Oudejaarsdag”
oplext(12, 2) = Mid(dagen, 2 * Weekday(CDate(“31-12-” & jaar)) – 1, 2)
oplext(12, 3) = “31-12-” & jaar
oplext(13, 1) = “Drie Koningen”
oplext(13, 2) = Mid(dagen, 2 * Weekday(CDate(“6-1-” & jaar)) – 1, 2)
oplext(13, 3) = “6-1-” & jaar
oplext(14, 1) = “Carnaval”
oplext(14, 2) = Mid(dagen, 2 * Weekday(DateAdd(“ww”, -7, vpasen)) – 1, 2)
oplext(14, 3) = DateAdd(“ww”, -7, vpasen)
oplext(15, 1) = “Dodenherdenking”
oplext(15, 2) = Mid(dagen, 2 * Weekday(CDate(“4-5-” & jaar)) – 1, 2)
oplext(15, 3) = “4-5-” & jaar
oplext(16, 1) = “Valentijnsdag”
oplext(16, 2) = Mid(dagen, 2 * Weekday(CDate(“14-2-” & jaar)) – 1, 2)
oplext(16, 3) = “14-2-” & jaar
oplext(17, 1) = “Sinterklaas”
oplext(17, 2) = Mid(dagen, 2 * Weekday(CDate(“5-12-” & jaar)) – 1, 2)
oplext(17, 3) = “5-12-” & jaar
oplext(18, 1) = “Moederdag”
oplext(18, 2) = “zo”
oplext(18, 3) = Moederdag(jaar)
oplext(19, 1) = “Vaderdag”
oplext(19, 2) = “zo”
oplext(19, 3) = Vaderdag(jaar)
oplext(20, 1) = “Prinsjesdag”
oplext(20, 2) = “di”
oplext(20, 3) = Prinsjesdag(jaar)
End If
If Uitgebreid Then
Feest_Sorteer oplext
Else
Feest_Sorteer opl
End If
If Uitgebreid Then
FeestDagen = oplext
Else
FeestDagen = opl
End If
End Function
Als u een feestdag wilt toevoegen dan kan dat gewoon onderaan de code. U moet dan wel de eerste dimensie van de Array oplext met 1 verhogen en dat hoogste nummer gebruiken voor de Array (dus oplext(21, 1) = “mijn feestdag”), etc.
Zo kunt u bijvoorbeeld de Islamitische dagen Ramadam (de 1e dag), Suikerfeest (Eid al-Fitr) en Offerfeest (Eid al-Adha) toevoegen. Dit kan met de volgende formules:
ramdate=Int((Jaar – 1900) * 354.367 + 1421.44)
If Jaar < 1997 Then
sprong = -366
If Jaar > 2030 Then
sprong = 365
Ramadan = ramdate + sprong
Het Suikerfeest is 30 dagen later en het Offerfeest 98 dagen later.