Diverse functies

Inhoud

Inleiding

In dit artikel gaan we enkele UDF’s uit de add-in Handigheidjes maken.
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

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:

ax^{2}+bx+c=0 \Rightarrow x_{1},x_{2}=\frac{-b\pm \sqrt{b^{2}-4ac}}{2a}

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:

  1. a=0, b=0, c=0: Dan staat er 0=0 en is de oplossing ℝ of ℂ;
  2. a=0, b=0, c<>0: Dan staat er iets als 4=0 en dat betekent dat er geen oplossingen zijn;
  3. a=0, b<>0, c=0 of c<>0: Nu hebben we te maken met een eerstegraads vergelijking ax+b=0. De oplossing (eentje maar) is x=-c/b;
  4. a<>0, b=0 of b<>0, c=0 of c<>0: Nu hebben we een echte tweedegraads vergelijking te pakken.
    En nu zijn er twee smaken die afhangen van de waarde van de discriminant (=b2-4ac).
    Dus eerst de discriminant bepalen:

    1. discriminant>=0: Dan volgen de oplossingen uit de ABC-formule;
    2. 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:

ABC_formule

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:

DiagSom

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

Sigma functies

Onder Sigma functies wordt verstaan het optellen van opeenvolgende gehele, even of oneven getallen vanaf 1 of 2.

Daarvoor hebben we in de wiskunde een aantal formules tot onze beschikking zodat we in de code geen gebruik hoeven te maken van loops.

De formule voor het optellen van de gehele getallen 1 t/m n luidt:

\sum_{k=1}^{n}k=\frac{1}{2}n(n+1)

De UDF die daarbij hoort:

Sigma

Function Sigma(ByVal GeheelGetal As Long) As Long
'bepaalt de som van 1 t/m n 
    Sigma = 0.5 * GeheelGetal * (GeheelGetal + 1) 
End Function

Excel VBA kent geen functie om te bepalen of een geheel getal even of oneven is. Dit moeten we dus zelf doen:

IsEven

Private Function IsEven(ByVal GeheelGetal As Long) As Boolean
'test of n even is
    IsEven = (GeheelGetal Mod 2 = 0)
End Function

De formule voor het optellen van de oneven gehele getallen van 2 t/m n luidt:

\sum_{k=0}^{n}(2k-1)=\left ( \frac{n+1}{2} \right )^{2}

De bijbehorende UDF luidt:

SigmaOneven

Function SigmaOneven(ByVal GeheelGetal As Long) As Variant
'bepaalt de som van de oneven getallen van 1 t/m n
    If IsEven(GeheelGetal) Then
        SigmaOneven = "n.v.t."
    Else
        SigmaOneven = ((GeheelGetal + 1) / 2) ^ 2
    End If
End Function

De formule voor het optellen van de even gehele getallen van 2 t/m n luidt:

\sum_{k=0}^{n}2k=\frac{n}{2}\cdot (\frac{n}{2}+1)

De UDF ziet er dan als volgt uit:

SigmaEven

Function SigmaEven(ByVal GeheelGetal As Long) As Variant
'bepaalt de som van de even getallen van 1 t/m n
    If IsEven(GeheelGetal) Then
        SigmaEven = (GeheelGetal \ 2) * (GeheelGetal \ 2 + 1)
    Else
        SigmaEven = "n.v.t."
    End If
End Function

En dan zijn er nog drie varianten op dit thema.
SigmaVanTot die niet vanaf 1 begint maar van een ander (geheel) getal begint,
SigmaOnevenVanTot die niet vanaf 1 begint maar van een ander oneven (geheel) getal en
SigmaEvenTot die niet vanaf 2 begint maar van een andere even (geheel) getal.
De truc is hierbij vrij simpel: Bepaal de Sigma (dus vanaf 1) en trek daar het deel af van Sigma(startgetal – 1) of (startgetal – 2) als het SigemaEven of SigmaOneven betreft:

Sigma's van tot

Function SigmaVanTot(ByVal van As Long, ByVal tot As Long) As Variant
'bepaalt de som van "van" t/m "tot"
    SigmaVanTot = Sigma(tot) - Sigma(van - 1)
End Function

Function SigmaOnevenVanTot(ByVal van As Long, ByVal tot As Long) As Variant
'bepaalt de som van de oneven getallen van "van" t/m "tot"
    If IsEven(van) Or IsEven(tot) Then
       SigmaOnevenVanTot = "n.v.t.'"
    Else
       SigmaOnevenVanTot = SigmaOneven(tot) - SigmaOneven(van - 2)
    End If
End Function

Function SigmaEvenVanTot(ByVal van As Long, ByVal tot As Long) As Long
    If IsEven(van) And IsEven(tot) Then
       SigmaEvenVanTot = SigmaEven(tot) - SigmaEven(van - 2)
    Else
       SigmaEvenVanTot = "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:

Pasen

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:

Pinksteren

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:

Hemelvaart

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:

Moederdag

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:

Vaderdag

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:

Prinsjesdag

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:

Feest_Sorteer

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:

Feestdagen

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 = Pasen(jaar)
    dag = Mid(dagen, 2 * Weekday(CDate(vpasen)) - 1, 2)
    opl(2, 1) = "Pasen"
    opl(2, 2) = dag
    opl(2, 3) = vpasen
    vhemelvaart = Hemelvaart(jaar)
    dag = Mid(dagen, 2 * Weekday(CDate(vhemelvaart)) - 1, 2)
    opl(3, 1) = "Hemelvaart"
    opl(3, 2) = dag
    opl(3, 3) = vhemelvaart
    vpinksteren = 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.

Astronomie

Omdat ons universum zo ontzettend groot is zijn aardse afstanden niet handig om in de astronomie te gebruiken. De kilometers lopen al snel uit op zeer grote getallen.

Daarom gebruiken astronomen het lichtjaar en de parsec als eenheden voor afstanden.

Lichtjaar

De eenheid lichtjaar is gedefinieerd als de afstand die het licht in vacuüm in 1 jaar aflegt.

Hiervoor is het belangrijk te weten wat de snelheid van licht in vacuüm is en hoe lang een jaar precies duurt.

De snelheid van licht in vacuüm bedraagt 299.792.458 (tweehonderdnegenennegentig miljoen zevenhonderdtweeennegentigduizend vierhonderdachtenvijftig) m/s.

Met een jaar wordt een jaar volgens de Juliaanse kalender bedoeld en is 365,25 dagen.

We moeten dus weten hoeveel seconden er in 1 Juliaans jaar zitten zodat we daarna dit getal met de snelheid van het licht kunnen vermenigvuldigen om te weten hoelang een lichtjaar is.

Dit is: 365,25 × 24 × 60 × 60 × 299.792.458 =
9.460.730.472.580.800 (negen biljard vierhonderdzestig biljoen zevenhonderddertig miljard vierhonderdtweeenzeventig miljoen vijfhonderdtachtigduizend achthonderd) meter ofwel
9.460.730.472.580,8 (negen biljoen vierhonderdzestig miljard zevenhonderddertig miljoen vierhonderdtweeenzeventigduizend vijfhonderdtachtig) km.

Om aan te geven hoe ver een lichtjaar is kijken we even naar de Voyager 1. Dit is een ruimtezonde die al bijna een halve eeuw vanaf de aarde de ruimte in vliegt.
De Voyager 1 is nu zo’n 26 biljoen km bij de aarde vandaan. Dit komt overeen met 0,002748202 lichtjaar en dat is iets meer dan 1 lichtdag!

In Excel kun je nu twee UDF’s maken die een afstand in aardse grootheden omzet naar lichtjaren en andersom.

De UDF Metric2Lichtjaar heeft als parameters de grootte in aardse eenheden (mm, cm, dm ,m ,dam, hm, km) en de betreffende eenheid (als string) die default “km” is.

De UDF Lichtjaar2Metric heeft als parameters het aantal lichtjaren en wederom een optionele parameter waar de aardse eenheid als string kan worden aangegeven met de default waarde “km”.

Metric-Lichtjaar UDF's

Function Metric2Lichtjaar(ByVal grootte As Variant, Optional ByVal eenheid As String = "km") As Variant
    '299792.458 km/s = snelheid licht in vacuum
    Dim AantSec As Double
    Dim lj As Variant
    
    AantSec = CDbl(3600 * 365.25) * 24
    lj = AantSec * 299792.458
    Select Case LCase(eenheid)
        Case "mm"
            grootte = grootte / 1000000
        Case "cm"
            grootte = grootte / 100000
        Case "dm"
            grootte = grootte / 10000
        Case "m"
            grootte = grootte / 1000
        Case "dam"
            grootte = grootte / 100
        Case "hm"
            grootte = grootte / 10
        Case "km"
            grootte = grootte / 1
        Case Else
            grootte = -1
    End Select
    If grootte > 0 Then
        Metric2Lichtjaar = grootte / lj
    Else
        Metric2Lichtjaar = "#N/B#"
    End If    
End Function

Function Lichtjaar2Metric(ByVal grootte As Variant, Optional ByVal eenheid As String = "km") As Variant
    '299792.458 km/s = snelheid licht in vacuum
    Dim AantSec As Double
    Dim lj As Variant
    
    AantSec = CDbl(3600 * 365.25) * 24
    lj = AantSec * 299792.458
    Select Case LCase(eenheid)
        Case "mm"
            grootte = grootte * 1000000
        Case "cm"
            grootte = grootte * 100000
        Case "dm"
            grootte = grootte * 10000
        Case "m"
            grootte = grootte * 1000
        Case "dam"
            grootte = grootte * 100
        Case "hm"
            grootte = grootte * 10
        Case "km"
            grootte = grootte * 1
        Case Else
            grootte = -1
    End Select
    If grootte > 0 Then
        Lichtjaar2Metric = grootte * lj
    Else
        Lichtjaar2Metric = "#N/B#"
    End If
End Function
Parsec

Omdat ook een lichtjaar wat aan de kleine kant is werkt men in de astronomie ook met de Parsec (of eigenlijk meer met de Mega Parsec).

Voor een uitgebreide uitleg over de Parsec verwijs ik graag naar het internet.

De wiskundige definitie van een Parsec luidt:

Een Parsec is de lengte van de aanliggende zijde van een rechthoekige driehoek waarvan de overstaande zijde 1 Astronomische Eenheid (AE) bedraagt en de aanliggende hoek 1 boogseconde (1″) is:

Een Astronomische Eenheid is de gemiddelde afstand tussen de Aarde (A) en de Zon (Z) en is ongeveer 150.000.000 (honderdvijftig miljoen) km. Maar voor de berekening van een Parsec is “ongeveer” niet voldoende en is de AE gelijk aan 149.597.870.700 (honderdnegenenveertig miljard vijfhonderdzevenennegentig miljoen achthonderdzeventigduizend zevenhonderd) m.

We zijn gewend om hoeken in graden (of radialen) te meten waarbij een hoek tussen de 0° en 360° is.

Maar een graad kan ook nog verder worden onderverdeeld in uren en seconden. Zo bestaat 1° uit 60 (graad)minuten (60′) en 1 (graad)minuut bestaat uit 60 (graad)seconden (60″).
Dus 1° bestaat uit 3600 (graad)seconden.

En nu we dit allemaal weten kunnen we met de tan(gus) de grootte van de Parsec berekenen:

1 Parsec = 1 AE / tan(1/3600°) = 30.856.775.814.671.900 (dertig biljard achthonderdzesenvijftig biljoen zevenhonderdvijfenzeventig miljard achthonderdveertien miljoen zeshonderdeenenzeventigduizend negenhonderd) m.

De verhouding tussen de Parsec en het Lichtjaar bedraagt 3,261564, dus 1 Parsec = 3,261564 Lichtjaar.

Een MegaParsec is 1 miljoen Parsec.

De volgende 4 UDF berekenen respectievelijk Aardse eenheden naar Parsec (Metric2Parsec) en andersom (Parsec2Metric), Lichtjaar naar Parsec (Lichtjaar2Parsec) en andersom (Parsec2Lichtjaar). De parameters zij hiervoor al besproken.

Parsec UDF's

Function Metric2Parsec(ByVal grootte As Variant, Optional ByVal eenheid As String = "km") As Variant
    Const factor As Single = 3.261564
    
    Metric2Parsec = Metric2Lichtjaar(grootte, eenheid) / factor
End Function

Function Parsec2Metric(ByVal grootte As Variant, Optional ByVal eenheid As String = "km") As Variant
    Const factor As Single = 3.261564
    
    Parsec2Metric = Lichtjaar2Metric(grootte, eenheid) * factor
End Function

Function Lichtjaar2Parsec(ByVal lichtjaar As Variant) As Variant
    Const factor As Single = 3.261564
    
    Lichtjaar2Parsec = lichtjaar / factor
End Function

Function Parsec2Lichtjaar(ByVal parsec As Variant) As Variant
    Const factor As Single = 3.261564
    
    Parsec2Lichtjaar = parsec * factor
End Function