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.

You cannot do that inside the square brackets. If you want to do it anyway, use Evaluate explicitly. — GSerg
– GSerg, Commented Apr 13, 2021 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
– norie, Commented Apr 13, 2021 at 15:00
@norie VBA strings are already Unicode. Double Unicode is not good for you. — GSerg
– GSerg, Commented Apr 13, 2021 at 15:02
@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
– GSerg, Commented Apr 13, 2021 at 15:07

Scott Craner · Accepted Answer · 2021-04-13 15:38:33Z

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, 2021 at 15:38

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

5 Comments

YasserKhalil Over a year ago

Thanks a lot. But I need a concise approach.

Scott Craner Over a year ago

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 Over a year ago

Less lines does not necessarily mean better or quicker code.

YasserKhalil Over a year ago

Thanks a lot for the great advice. Thank you very much.

Matteo NNZ Over a year ago

@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.

norie · Accepted Answer · 2021-04-13 15:24:35Z

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, 2021 at 15:24

answered Apr 13, 2021 at 15:14

norie

9,9372 gold badges14 silver badges19 bronze badges

6 Comments

YasserKhalil Over a year ago

Awesome. Thanks a lot. I got the digits from right to left (How can I get the digits from left to right?)

norie Over a year ago

I thought that was what you wanted.:) I'll add code to get the digits in the right order.

YasserKhalil Over a year ago

Thank you very much.

Tim Williams Over a year ago

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.

Scott Craner Over a year ago

BTW ROW(OFFSET(A1,,,LEN(""" & s & """))) can be replaced with ROW(1:" & Len(s) & ")

|

Collectives™ on Stack Overflow

Using Evaluate with string variable not with range object

2 Answers 2

5 Comments

Split with Evaluate

Strings

Reverse

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Split with Evaluate

Strings

Reverse

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related