UDF to return Date Components in an Array [VBA]

[drpdrpdrp]

I want to return the date components (Year, Month, DayOfYear, DayOfMonth) using a UDF.
Can someone explain why the code below won't work ?

Function DateYrMoDoyDom(InputDate As Range)
    'makes it easier to extract Year, Month, DayOfYear, DayOfMonth from date
    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
    
    DateYrMoDoyDom = a
End Function

 

[Fluff]

How are you entering the formula into the sheet?

 

[drpdrpdrp]

How are you entering the formula into the sheet?

Ctrl+Shift+Enter, in 4 cells

 

[Fluff]

And how many cells are there in "InputDate"?

 

[drpdrpdrp]

And how many cells are there in "InputDate"?

Only one cell, with a Date in it ('2022-04-03', for example)

 

[Rick Rothstein]

When you say "won't work", what does that mean? Exactly what happens when you try to use your UDF?

 

[Fluff]

Along with Rick's question, are the 4 cells in one row, one column, or something else?

 

[drpdrpdrp]

#VALUE! error, Rick.
I've attached an image, which should explain better. Let me know if I should send the workbook

 

[Fluff]

As The input is a single cell use

Function DateYrMoDoyDom(InputDate As Range)
    'makes it easier to extract Year, Month, DayOfYear, DayOfMonth from date
    Dim i As Long, rows As Long, r
    r = InputDate
    ReDim A(1 To 1, 1 To 4)
        A(1, 1) = Year(r)
        A(1, 2) = Month(r)
        A(1, 3) = r - DateSerial(Year(r), 1, 0)
        A(1, 4) = Day(r)
    
    
    DateYrMoDoyDom = A
End Function

 

[Rick Rothstein]

NOTE: I deleted my previous message as I developed a more compact solution

Like Fluff said, since the input is a single cell, here is another UDF that you can try...

Function DateYrMoDoyDom(DateIn As Range) As Variant
  DateYrMoDoyDom = Split(Format(DateIn, "yyyy mm y dd"))
End Function