Converting a sub into function

[SharmaAntriksh]

Hello All,

I want to convert the below Sub Procedure into a function that I can call into Excel sheet.

Sub SplitTextIntoMultipleCells()
    
    Dim Counter As Long
    Dim WordArray() As Variant
    Dim WordList As Dictionary
    Dim dKey As Variant
    
    Set WordList = New Dictionary
    
    WordArray = Sheet1.Range("A1:A10")
    
    For Counter = LBound(WordArray, 1) To UBound(WordArray, 1)
        WordList.Add Counter, Split(WordArray(Counter, 1), " ")
    Next Counter
    
    For Each dKey In WordList.Keys
        Sheet1.Range(Range("D1").Offset(dKey - 1, 0), Range("D1").Offset(dKey - 1, UBound(WordList.Item(dKey)))).Value = WordList(dKey)
    Next dKey


End Sub

So far this is what I could do but I am not able to think of how to convert it fully into a function

Function SplitTextIntoMultipleCells(InputText As Range) As String
    
    Dim Counter As Long
    Dim WordArray() As Variant
    Dim WordList As Dictionary
    Dim dKey As Variant
    
    Set WordList = New Dictionary
    
    WordArray = InputText
    
    For Counter = LBound(WordArray, 1) To UBound(WordArray, 1)
        WordList.Add Key:=Counter, Value:=Split(WordArray(Counter, 1), " ")
    Next Counter
    
    For Each dKey In WordList.Keys
        Sheet1.Range(Range("D1").Offset(dKey - 1, 0), Range("D1").Offset(dKey - 1, UBound(WordList.Item(dKey)))).Value = WordList(dKey)
    Next dKey
    
End Function


Sub b()
    
    SplitTextIntoMultipleCells Range("A1:A10"), Range("E1")
    
End Sub

 

[Fluff]

Why not just use a normal xl formula in D1 copied down & across
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",500)),COLUMN(A1)*500-499,500))