(Solved) – Convert UDF that Parses TIme-Starts to an Array formula

  • by
(solved)-–-convert-udf-that-parses-time-starts-to-an-array-formula

I created a UDF to parse time-starts from a delimited string.
– Returns an Array(0 to 23) that represent hours in the day
– Each time-start is separated by a comma
@ is used to signify multiple time-starts

For example 5@8p returns 5 as the 20th element in the 0 based array.

AssignmentList("2@12a,3@6a,10@12p,6p,5@8p")(0)

Watch Window

Sample Data Set

Sub Setup()
    Range("A1:AA1").Value = Array("1st", "2nd", "3rd", "12PM", "1AM", "2AM", "3AM", "4AM", "5AM", "6AM", "7AM", "8AM", "9AM", "10AM", "11AM", "12PM", "1PM", "2PM", "3PM", "4PM", "5PM", "6PM", "7PM", "8PM", "9PM", "10PM", "11PM")
    Range("A2:C2").Value = Array("12a", "10a,3@12p", "6p,5@8p")
    Range("D2:AA2").FormulaArray = "=AssignmentList($A2:$C2)"
End Sub

Function AssignmentList(ByRef Source As Variant) As Variant
    Dim Assignments(0 To 23) As Double
    Dim Item  As Variant, At As Variant
    Dim Text As String
    Text = WorksheetFunction.TextJoin(",", True, Source)

    For Each Item In Split(Text, ",")
        If InStr(Item, "@") > 0 Then
            At = Split(Item, "@")
            Assignments(Hour(At(1))) = Assignments(Hour(At(1)))   At(0)
        Else
            Assignments(Hour(Item)) = Assignments(Hour(Item))   1
        End If
    Next

    AssignmentList = Assignments
End Function

I would like to convert this function to an Array Formula but do not know where to start. References or advice as where to start would be greatly appreciated.

I am also interested in anyway that I could improve my UDF. Ultimately, I will use whichever function gives me the best performance.

Leave a Reply

Your email address will not be published. Required fields are marked *