Using Evaluate with string variable not with range object

Question

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.

Why are you trying to do this? Why not just "pure" VBA? A simple loop with `Mid$` for example. — BigBen, Apr 13 '21 at 14:57
You [cannot do that](https://stackoverflow.com/a/48724996/11683) inside the square brackets. If you want to do it anyway, [use `Evaluate` explicitly](https://stackoverflow.com/a/27591713/11683). — GSerg, Apr 13 '21 at 14:59
You could do this in VBA with a couple of lines of code - first convert the string to unicode using StrConv, then split the result on Chr(0). — norie, Apr 13 '21 at 15:00
@norie VBA strings are already Unicode. [Double Unicode](https://stackoverflow.com/a/14292880/11683) is not good for you. — GSerg, Apr 13 '21 at 15:02
@GSerg I don't think this is possible even using Evaluate - Excel doesn't like it unless you use a cell reference. — norie, Apr 13 '21 at 15:02
@norie can you show me how to comvert string to unicode (simple example)? I used these two lines `Dim x() As Byte x = StrConv("6548102", vbFromUnicode)` but doesn't see to have chr(0) — YasserKhalil, Apr 13 '21 at 15:06
@norie That is because `offset` requires a cell reference, not a literal value. There is otherwise no problem with composing a string for `Evaluate` in this fashion, unlike for the `[]`. — GSerg, Apr 13 '21 at 15:07

score 2 · Answer 1 · answered Apr 13 '21 at 15:38

2

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

answered Apr 13 '21 at 15:38

Scott Craner

148,073
10
49
81

Thanks a lot. But I need a concise approach. – YasserKhalil Apr 13 '21 at 15:43
1

What do you mean by `concise`? Because to me it is more concise then trying to fit a formula into vba using evaluate. Also this will be quicker over all. – Scott Craner Apr 13 '21 at 15:44
1

Less lines does not necessarily mean better or quicker code. – Scott Craner Apr 13 '21 at 15:48
Thanks a lot for the great advice. Thank you very much. – YasserKhalil Apr 13 '21 at 15:50
2

@YasserKhalil code is good when it's understandable and robust. The above snippet is longer than one line but anyone reading would understand what you're trying to do - and modify it, fix it, debug it accordingly. Your line of code may be concise but it's very hard to understand. Plus, it's neither more robust (you can't handle any exception, just need to trust Excel) nor more performant (you need to evaluate a formula). I really suggest you go for the above approach and forget about that string. – Matteo NNZ Apr 13 '21 at 17:28

norie · Accepted Answer · 2021-04-13T15:24:35.263

1

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

edited Apr 13 '21 at 15:24

answered Apr 13 '21 at 15:14

norie

9,609
2
11
18

Awesome. Thanks a lot. I got the digits from right to left (How can I get the digits from left to right?) – YasserKhalil Apr 13 '21 at 15:18
1

I thought that was what you wanted.:) I'll add code to get the digits in the right order. – norie Apr 13 '21 at 15:24
Thank you very much. – YasserKhalil Apr 13 '21 at 15:30
1

When using Evaluate() it's a good idea to explicitly use either the `Application.Evaluate()` or the `Worksheet.Evaluate()` version. The first one (above) will default to the scope of whatever sheet happens to be active, whereas the second one is scoped to a specific sheet, and is normally the most robust option. – Tim Williams Apr 13 '21 at 15:33
BTW `ROW(OFFSET(A1,,,LEN(""" & s & """)))` can be replaced with `ROW(1:" & Len(s) & ")` – Scott Craner Apr 13 '21 at 15:47
in fact the whole can be reduced: `Application.Evaluate("TRANSPOSE(MID(""" & s & """,1+" & Len(s) & "-ROW(1:" & Len(s) & "),1))")` – Scott Craner Apr 13 '21 at 15:50

Using Evaluate with string variable not with range object

2 Answers2

Split with Evaluate

Strings

Reverse