|
Hallo Leute,
vielleicht kann mir jemand helfen. Ich habe eine Formel womit ich die Feiertage berechnen kann. Nur leider muss ich jetzt jedes Mal im Code das Jahr ändern. Das Jahr möchte ich aber in einer Zelle schreiben, das es automatisch übernommen wird.
Hier einmal mein Code.
Public Function Feiertage(intYear As Integer) As Variant
Dim varDates(13, 1) As Variant
Dim dEaster As Date
dEaster = Easter(intYear)
varDates(0, 0) = DateSerial(intYear, 1, 1)
varDates(0, 1) = "Neujahr"
varDates(1, 0) = DateSerial(intYear, 1, 6)
varDates(1, 1) = "Dreikšnig"
varDates(2, 0) = dEaster - 3
varDates(2, 1) = "Karfreitag"
varDates(3, 0) = dEaster + 1
varDates(3, 1) = "Ostermontag"
varDates(4, 0) = DateSerial(intYear, 5, 1)
varDates(4, 1) = "Tag der Arbeit"
varDates(5, 0) = dEaster + 39
varDates(5, 1) = "Christi Himmelfahrt"
varDates(6, 0) = dEaster + 50
varDates(6, 1) = "Pfingstmontag"
varDates(7, 0) = dEaster + 60
varDates(7, 1) = "Fronleichnam"
varDates(8, 0) = DateSerial(intYear, 10, 3)
varDates(8, 1) = "Tag der Einheit"
varDates(9, 0) = DateSerial(intYear, 11, 1)
varDates(9, 1) = "Allerheiligen"
varDates(10, 0) = DateSerial(intYear, 12, 24)
varDates(10, 1) = "Heiligabend"
varDates(11, 0) = DateSerial(intYear, 12, 25)
varDates(11, 1) = "1. Weihnachtstag"
varDates(12, 0) = DateSerial(intYear, 12, 26)
varDates(12, 1) = "2. Weihnachtstag"
varDates(13, 0) = DateSerial(intYear, 12, 31)
varDates(13, 1) = "Silvester"
Feiertage = varDates
End Function
Private Function Easter(Year As Integer) As Date
Dim D As Integer
D = (((255 - 11 * (Year Mod 19)) - 21) Mod 30) + 21
Easter = DateSerial(Year, 3, 1) + D + (D > 48) + 6 - _
((Year + Year \ 4 + D + (D > 48) + 1) Mod 7)
End Function
Sub test()
Dim dummy As Variant
dummy = Feiertage(2016)
Range("A1:B" & UBound(dummy, 1) + 1) = dummy
End Sub
Vielen dank schon mal für die Hilfe.
Gruß
Kai |