CODEWITHSUNDEEP
excelvba
YasserKhalil
YasserKhalil

Reputation: 9568

Using Evaluate with string variable not with range object

I have a line of code that returns 1d array based on a value in range A1. Example suppose there's a value 6548102 in A1 and I used this line x = [TRANSPOSE(MID(A1,1+len(A1)-ROW(OFFSET(A1,,,LEN(A1))),1))] this line returned a 1d array of each digit in A1 This is my try

Sub Demo()
    Dim x
    Dim s As String
    s = "6548102"
    'x = [TRANSPOSE(MID(A1,1+len(A1)-ROW(OFFSET(A1,,,LEN(A1))),1))]
    x = [TRANSPOSE(MID(" & s & ",1+LEN(" & s & ")-ROW(OFFSET(" & s & ",,,LEN(" & s & "))),1))]
    Stop
End Sub

I tried to replace A1 with the string variable but it seems this trick doesn't work. Simply I need to deal with a string not a range with the same technique.

Upvotes: 0

Views: 124

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

It would be simple to just use VBA:

Sub ReverseDemo()
    dim s as string
    s = "6548102"

    dim x() as variant
    redim x(0 to len(s) - 1) as variant

    dim k as long
    k = 0 

    dim i as long
    for i = len(s) to 1 step -1
        x(k) = mid(s,i,1)
        k = k + 1
    Next i

    'Do something with x
End Sub

Upvotes: 2

norie
norie

Reputation: 9857

Split with Evaluate

Instead of using [] use Evaluate, and don't replace A1 in the OFFSET part of the formula with the value you want to split.

Sub Demo()
Dim x
Dim s As String

    s = 123
    x = Evaluate("TRANSPOSE(MID(""" & s & """,ROW(OFFSET(A1,,,LEN(""" & s & """))),1))")
    Debug.Print Join(x, "-")
    
End Sub

Strings

If you actually want to split a string you would need to add double quotes throughout.

Sub StringDemo()
Dim x
Dim s As String

    s = "Yassser"
    x = Evaluate("TRANSPOSE(MID(""" & s & """,ROW(OFFSET(A1,,,LEN(""" & s & """))),1))")
    Debug.Print Join(x, "-")
    
End Sub

Actually, you probably want to use the second code as it will work for both strings and numbers.

Reverse

If, for some reason you wanted the characters/digits in reverse order you can use this.

Sub ReverseDemo()
Dim x
Dim s As String

    s = "Reverse"
    x = Evaluate("TRANSPOSE(MID(""" & s & """,1+LEN(""" & s & """)-ROW(OFFSET(A1,,,LEN(""" & s & """))),1))")
    Debug.Print Join(x, "-")
End Sub

Upvotes: 1

Related Questions