Ha Johan,
Ik heb de Excel op de mail gezet, voor de 'kijkers'
Als je in een situatie komt waarbij je naar veel cellen moet kijken om je berekening te maken kan een eigen functie maken handig zijn. Dit is helemaal niet zo moeilijk. In Excel ga je via ALT-F11 naar VBA (Visual Basic for Applications) en je kiest in het menu voor 'invoegen - module'.
Je krijgt nu een leeg wit blad waar je kan beginnen met 'bouwen'.
Start altijd eerst met de regel
Option Explicit
Dit dwingt VBA (dwingt jou) om de variabelen die je gebruikt 'juist' te schrijven. Maak je een spelfout dan zal VBA je hier op wijzen. Hieronder de 2 formules die ik in dit specifieke geval heb gemaakt voor Johan. Je ziet dat ik veel gebruik maak van parameters 'as Range'. Dit maakt het makkelijk om de Excel cellen mee te sturen waar ik in mijn functie 'iets mee wil doen':
Function BerekenRecupMinuten(Werkuren As Range, VorigeCel As Range, Gewerkt As Range) As Double
Dim nwerkuren As Double
Dim nrecupminuten As Double
Dim nresultaat As Double
Dim ngewerkt As Boolean
'We slaan eerst even de waarden uit de cellen die we hebben meegekregen op in tijdelijke (werk) variabelen
If IsNumeric(Werkuren.Value) Then 'We moeten even checken of we wel met een tijd te maken hebben, som gebeurt het dat er teksten zoals Terugname feestdag van 11/11/2015 in staan!
nwerkuren = Werkuren.Value
End If
nrecupminuten = VorigeCel.Value
'Kijk of gewerkt = Ja dan tijdelijk variabele ngewerkt = true, anders ngewerkt = false
If (Gewerkt.Value = "Ja") Then
ngewerkt = True
Else
ngewerkt = False
End If
'Nu gaan we kijken, hebben we werkuren > 0 (ik heb die dag gewerkt) dan 12 minuten optellen bij nrecupminuten (de waarde uit de vorige cel)
If (nwerkuren > 0) Then
'Controleer of nrecupminuten < 60
If (nrecupminuten < 60) Then
nresultaat = nrecupminuten + 12
Else
If (ngewerkt = True) Then
nresultaat = 12
Else
nresultaat = 0
End If
End If
Else
nresultaat = nrecupminuten
End If
'We eindigen de functie altijd met het teruggeven van de berekende waarde aan de functie(naam), dit zodat deze netjes in de cel komt als 'uitkomst'
BerekenRecupMinuten = nresultaat
End Function
Function BerekenRecupUren(VorigeCel As Range, RecupMinuten As Range) As Double
Dim nvorigecel As Double
Dim nheeluur As Boolean
Dim resultaat As Double
nvorigecel = VorigeCel.Value
If (nvorigecel >
Then
resultaat = nvorigecel - 8
Else
resultaat = nvorigecel
End If
'Nu kijken we of RecupMinuten op 60 staat, is dat het geval dan hogen we het resultaat met 1 op
If (RecupMinuten.Value = 60) Then
resultaat = resultaat + 1 'Je kan ook verkort resultaat += 1 zetten
End If
'We eindigen de functie altijd met het teruggeven van de berekende waarde aan de functie(naam), dit zodat deze netjes in de cel komt als 'uitkomst'
BerekenRecupUren = resultaat
End Function
De 2 subs hieronder gaan wat ver maar zorgen ervoor dat als men op het functie teken klikt in Excel er extra help tekst wordt getoond:
Sub RegisterUDF() 'Dit gebruik je om help teksten bij je zelfgemaakte formules te zetten, deze SUB wordt aangeroepen in ThisWorkbook.Open() (bij openen Excel).
Dim s As String
s = "JoVaDro 2016: bereken recup minuten, hou rekenening met 'gewerkt'" & vbLf _
& "BerekenRecupMinuten(Werkuren;VorigeCel;Gewerkt)"
Application.MacroOptions Macro:="BerekenRecupMinuten", Description:=s, Category:=9
End Sub
Sub UnregisterUDF()
Application.MacroOptions Macro:="BerekenRecupMinuten", Description:=Empty, Category:=Empty
End Sub
In Excel zelf zet je de zelfgemaakte functienaam, haakje open + cellen / waarden opgeven:
=BerekenRecupMinuten(Werkuren!E14;G6;I6)
Zie afbeelding in bijlage
Gr. Mike