Excel VBA: "Change a Subroutine into a Function", for String Conversion

Question

Through my work, and copying others, I have cobbled together a Excel VBA Sub that separates a long sting with groups of (text groups) and (number groups) into a replacement string with spaces in between each seperate group; as per this example: • “123abc12aedsw2345der” • …Apply selection Sub() then becomes: • “123 abc 12 aedsw 2345 der” It converts the string in its original cell as per the “selection”, so I am currently left with the altered data in is original cell PROBLEM: I would like to change this into a FUNCTION where the transformed data would appear in the Function cell and leave the original cell intact. I have done hundreds of these but I cannot seem to get this to work as an independent FUNCTION. Below the finished and working Sub Routine I am trying to convert to an independent function to call from anywhere on the worksheet:

Sub SplitTextNumbersSelection()
Dim c As Range

'********** Inserts Space Before Number Groups ******************************
For n = 1 To 10
    For Each c In Selection
        c = InsertSpace(c.Text)
    Next
Next n
'****************Inserts Space Before Letter Groups ***********************
For n = 1 To 10
    For Each c In Selection
        c = InsertSpace2(c.Text)
    Next
Next n
'****************************************

End Sub

Function InsertSpace(str As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "([a-z])(\d)"
        '.Pattern = "(\d)([a-z])"
        InsertSpace = .Replace(str, "$1 $2")
    End With
End Function

Function InsertSpace2(str As String) As String
    With CreateObject("vbscript.regexp")
        '.Pattern = "([a-z])(\d)"
        .Pattern = "(\d)([a-z])"
        InsertSpace2 = .Replace(str, "$1 $2")
    End With
End Function

I am confused, you only need to type: = InsertSpace(A1) in the other cell and you are good? — Aldert
– Aldert, Commented Feb 24, 2022 at 19:26
Trying to have a function such as: FuntionNewString (TaggetCelForConversion) in my user function libary — RWB
– RWB, Commented Feb 24, 2022 at 20:01
the above Sub works just fine, I just cant seem to convert it to a User Function — RWB
– RWB, Commented Feb 24, 2022 at 20:13

Tim Williams · Accepted Answer · 2022-02-24 21:11:21Z

1

Bit simpler:

Function PrepString(v As String)
    Dim rv As String, i As Long, c1, c2
    For i = 1 To Len(v) - 1
        c1 = Mid(v, i, 1)
        c2 = Mid(v, i + 1, 1)
        If (c1 Like "[a-z]" And c2 Like "[0-9]") Or _
           (c2 Like "[a-z]" And c1 Like "[0-9]") Then
            rv = rv & c1 & " "
        Else
            rv = rv & c1
        End If
    Next i
    PrepString = rv & Right(v, 1)
End Function

answered Feb 24, 2022 at 21:11

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RWB Over a year ago

Wow - yes it is and works perfectly! Thanks

Aldert Over a year ago

OK, got it, you wanted them combined into one function :-)

Tim Williams Over a year ago

Doesn't really matter, but I think it's maybe easier to follow the logic if it's in one place.

RWB Over a year ago

I am new, so without the code, I never understood your comment above: "I am confused, you only need to type: = InsertSpace(A1) in the other cell and you are good? – "

Collectives™ on Stack Overflow

Excel VBA: "Change a Subroutine into a Function", for String Conversion

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related