VBA for Dates Computation is too Slow

[drpdrpdrp]

The following VBA UDF returns a 4-value-array using a Date value as input. Even though it is simple, if I ran for ~10'000 rows calculation time is not satisfactory (>30seconds).
What am I doing wrong and how should I write the code to make it faster?

' YrMoDoy
Function YrMoDoyDom(InputDate)

    Dim Yr As Variant
    Dim Mo As Variant
    Dim Doy As Variant
    Dim Woy As Variant
   
    Yr = Year(InputDate)
    Mo = Month(InputDate)
    Doy = InputDate - DateSerial(Year(InputDate), 1, 0)
    Dom = Day(InputDate)
    'Woy = WorksheetFunction.IsoWeek(InputDate)

    YrMoDoyDom = Array(Yr, Mo, Doy, Dom)
End Function

 

[JGordon11]

You can change the function to act on the whole column of InputDates with one function call. Invoke as =YrMoDoyDom(A1:A10000) and enter with ctrl-shift-enter not just enter. Should fill almost instantaneously.

Function YrMoDoyDom(InputDate As Range)
    Dim i As Long, rows As Long, r
    r = InputDate
    rows = UBound(r)
    ReDim a(1 To rows, 1 To 4)
    For i = 1 To rows
        a(i, 1) = Year(r(i, 1))
        a(i, 2) = Month(r(i, 1))
        a(i, 3) = r(i, 1) - DateSerial(Year(r(i, 1)), 1, 0)
        a(i, 4) = Day(r(i, 1))
    Next i
    
    YrMoDoyDom = a
End Function

 

[DanteAmor]

You have functions and also matrix, which are going to be executing each change in the sheet.
I suggest you change the code to macro, and run it every time you want a data update.
The execution for 10,000 records is immediate.
Fit column B and the result in column C

Sub MacroX()
  Dim a As Variant, b As Variant
  Dim i As Long
  a = Range("B2", Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 4)
  For i = 1 To UBound(a, 1)
    b(i, 1) = Year(a(i, 1))
    b(i, 2) = Month(a(i, 1))
    b(i, 3) = a(i, 1) - DateSerial(Year(a(i, 1)), 1, 0)
    b(i, 4) = Day(a(i, 1))
  Next
  Range("C2").Resize(UBound(b, 1), 4).Value = b
End Sub

 

[drpdrpdrp]

You can change the function to act on the whole column of InputDates with one function call. Invoke as =YrMoDoyDom(A1:A10000) and enter with ctrl-shift-enter not just enter. Should fill almost instantaneously.

Function YrMoDoyDom(InputDate As Range)
    Dim i As Long, rows As Long, r
    r = InputDate
    rows = UBound(r)
    ReDim a(1 To rows, 1 To 4)
    For i = 1 To rows
        a(i, 1) = Year(r(i, 1))
        a(i, 2) = Month(r(i, 1))
        a(i, 3) = r(i, 1) - DateSerial(Year(r(i, 1)), 1, 0)
        a(i, 4) = Day(r(i, 1))
    Next i
   
    YrMoDoyDom = a
End Function

Ohhh, makes sense. Read the whole array onto memory (as "r") and then calculate on it.
Any specific reason why *r* was not declared as a specific type?

 

[drpdrpdrp]

You have functions and also matrix, which are going to be executing each change in the sheet.
I suggest you change the code to macro, and run it every time you want a data update.
The execution for 10,000 records is immediate.
Fit column B and the result in column C

Sub MacroX()
  Dim a As Variant, b As Variant
  Dim i As Long
  a = Range("B2", Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 4)
  For i = 1 To UBound(a, 1)
    b(i, 1) = Year(a(i, 1))
    b(i, 2) = Month(a(i, 1))
    b(i, 3) = a(i, 1) - DateSerial(Year(a(i, 1)), 1, 0)
    b(i, 4) = Day(a(i, 1))
  Next
  Range("C2").Resize(UBound(b, 1), 4).Value = b
End Sub

You have functions and also matrix, which are going to be executing each change in the sheet.
I suggest you change the code to macro, and run it every time you want a data update.
The execution for 10,000 records is immediate.
Fit column B and the result in column C

Sub MacroX()
  Dim a As Variant, b As Variant
  Dim i As Long
  a = Range("B2", Range("B" & Rows.Count).End(3)).Value
  ReDim b(1 To UBound(a, 1), 1 To 4)
  For i = 1 To UBound(a, 1)
    b(i, 1) = Year(a(i, 1))
    b(i, 2) = Month(a(i, 1))
    b(i, 3) = a(i, 1) - DateSerial(Year(a(i, 1)), 1, 0)
    b(i, 4) = Day(a(i, 1))
  Next
  Range("C2").Resize(UBound(b, 1), 4).Value = b
End Sub

Thank you DanteAmor. I understand the logic of loading the entire array onto memory before running calculations now.
Unfortunately this one has to be a UDF but I could get that working from your + JGordon11's reply.

 

[JGordon11]

Ohhh, makes sense. Read the whole array onto memory (as "r") and then calculate on it.
Any specific reason why *r* was not declared as a specific type?

r needed to be of variant type to accept the array of values stored in the range. If you don't specify the data type VBA defaults to variant