write a specific split function with vba

Question

I am writing a vba code to split some texts base on a delimiter and then write each word in a cell. but the code is only able to split numbers and not the text. I don't know what is wrong. please help me

Function mysplit(strText As String, delimiter As String)
    Dim i As Integer
    Dim nchoose As Integer
    Dim str1 As String
    Dim str2 As String
    Dim delimiterposition As Integer
    Dim r1 As Range
    
    Set r1 = Application.Caller
    delimiterposition = InStr(strText, delimiter)
    i = 1
    nchoose = 1
    Do Until delimiterposition = 0
        str1 = Left(strText, delimiterposition - 1)
        strText = Right(strText, Len(strText) - delimiterposition)
        delimiterposition = InStr(strText, delimiter)
        Evaluate "other_cell_writer(" & r1.Offset(0, i).Address(False, False) & "," & str1 & "," & nchoose & ")"
        i = i + 1
        nchoose = -1 * nchoose
        Loop
        Evaluate "other_cell_writer(" & r1.Offset(0, i).Address(False, False) & "," & strText & "," & nchoose & ")"
        mysplit = "ok"
End Function

Sub other_cell_writer(ResultCell As Range, str1 As String, nchoose As Integer)
    ResultCell.Offset(1, 0).Formula = str1
    ResultCell.Value = str1
    If nchoose = 1 Then
        ResultCell.Interior.ThemeColor = xlThemeColorAccent2
        ResultCell.Offset(1, 0).Interior.ThemeColor = xlThemeColorAccent2
        ResultCell.Offset(1, 0).Interior.TintAndShade = 0.799981688894314
    ElseIf nchoose = -1 Then
        ResultCell.Interior.ThemeColor = xlThemeColorAccent5
        ResultCell.Offset(1, 0).Interior.ThemeColor = xlThemeColorAccent5
        ResultCell.Offset(1, 0).Interior.TintAndShade = 0.799981688894314
    End If
End Sub

You can just write the split values to a range like this Range("A2:E2") = Split("1,2,3,4,5", ",") and then format them any way you want. — braX
– braX, Commented Aug 14, 2021 at 8:13
I want to run it as an excel function which it gives me " #value! ". I read somewhere to use a sub in my function to solve this problem — ma1ah
– ma1ah, Commented Aug 14, 2021 at 8:20

Tim Williams · Accepted Answer · 2021-08-14 22:37:37Z

You're not quoting the arguments correctly in the call to Evaluate

This worked for me:

Function mysplit(strText As String, delimiter As String)
    Dim c As Range
    Set c = Application.ThisCell
    c.Parent.Evaluate "other_cell_writer(" & c.Address() & ",""" & strText & """,""" & delimiter & """)"
    mysplit = "ok"
End Function

Function other_cell_writer(FormulaCell As Range, str1 As String, delim As String)
    
    Dim arr, v, i, n
    
    arr = Split(str1, delim)
    For i = LBound(arr) To UBound(arr)
        With FormulaCell.Offset(0, n + 1)
            .Value = arr(i)
            .Interior.Color = IIf(i Mod 2 = 0, vbRed, vbYellow)
        End With
        n = n + 1
    Next i
    
End Function

Note you really want the Worksheet.Evaluate method and not the default (implicit) Application.Evaluate, which will use the active sheet as the context for evaluating the call.

If strText might contain double-quotes you will need to escape those before concatenating it into the call to Evaluate.

Collectives™ on Stack Overflow

write a specific split function with vba

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related