JoinIf, GetIf, Split functions for Excel

stringifs.bas

Option Explicit

Public Function GetIf(ByRef Rng As Range, ByVal If1 As Variant, ByVal If2 As Variant, Optional ByVal Indx As Variant) As Variant
    Dim i As Long, cell_count As Long
    cell_count = Rng.Cells.Count
    Dim Results As New Collection
    
    For i = 1 To cell_count
        If GetObject(If1, i) = GetObject(If2, i) Then Results.Add Rng.Cells(i)
    Next
    
    If IsMissing(Indx) Then
        Dim ResultsArray() As Variant
        ReDim ResultsArray(0 To Results.Count - 1)
        For i = 1 To Results.Count
            ResultsArray(i - 1) = Results(i)
        Next
        GetIf = ResultsArray
    Else
        GetIf = CvERR(xlErrNA)
        On Error Resume Next
        GetIf = Results(Indx + 1)
    End If
    
End Function

Public Function JoinIf(ByRef Rng As Range, Optional ByVal Delimiter As String = ",", Optional ByVal LastDelimiter As String = ",", Optional ByVal If1 As Variant, Optional ByVal If2 As Variant)
    Application.Volatile
    
    Dim i As Long, j As Long, joined_counter As Long, cell_count As Long
    Dim Cell As Range
    i = 0
    cell_count = Rng.Cells.Count
    If LastDelimiter = "" Then LastDelimiter = Delimiter
    
    'Due to the If1 / If2 functionality, we won't know which of the various cells in Rng
    'will be the last to be joined to Delimiter, so we keep an "ongoing" Result variable
    'and simultaneously assume the current iteration of the loop is the last in the
    'alternate Result2 string
    Dim Result As String, Result2 As String, LastJoined As String
    
    Result = ""
    For i = 1 To (cell_count)
        If GetObject(If1, i) = GetObject(If2, i) Or (IsMissing(If1) Or IsMissing(If2)) Then
            LastJoined = Rng.Cells(i)
            If LastJoined <> "" Then joined_counter = joined_counter + 1
            If i = 1 Then
                Result = LastJoined
            Else
                If LastJoined <> "" And joined_counter > 0 Then
                    Result2 = IIf(Result <> "", Result & LastDelimiter, "") & LastJoined
                    Result = IIf(Result <> "", Result & Delimiter, "") & LastJoined
                End If
            End If
        End If
    Next
    If joined_counter = 1 And LastJoined <> "" Then
        Result = LastJoined
    ElseIf joined_counter = 1 And LastJoined = "" Then
        Result = Result
    Else
        Result = Result2
    End If

    If joined_counter = 1 And LastJoined <> "" Then Result = LastJoined
    JoinIf = Result

End Function

Public Function FSplit(ByVal Expression As String, Optional ByVal Delimiter As String = " ", Optional ByVal Item As Long) As Variant
    Dim CallerRows As Long
    Dim CallerCols As Long
    CallerRows = 1
    CallerRows = 1
    If IsMissing(Item) Then
        'On Error Resume Next
        CallerRows = Application.Caller.Rows.Count
        CallerCols = Application.Caller.Columns.Count
        'On Error GoTo 0
    End If
    
    Dim SplitString() As String
    SplitString = Strings.Split(Expression, Delimiter)
    
    Dim Result() As String
    If (CallerRows + CallerCols > 2) Then
        ReDim ResultArray(1 To CallerRows, 1 To CallerCols)
        Dim r As Long, c As Long, i As Long
        For r = 1 To CallerRows
            For c = 1 To CallerCols
                i = i + 1
                Result(r, c) = SplitString(i)
            Next
        Next
        FSplit = Result
    Else
        If IsMissing(Item) Then
            Item = 0
        ElseIf Item < 0 Then
            Item = UBound(SplitString) + Item + 1
        End If
        FSplit = SplitString(Item)
    End If
    
End Function

Private Function GetObject(ByRef Obj As Variant, ByVal Index As Long) As Variant
    On Error GoTo SimpleReturn
    GetObject = Obj.Item(Index)
    Exit Function
    
SimpleReturn:
    GetObject = Obj
    
End Function