Search code examples
vbascriptcontrol

evaluating strings in VBA

I have a string that is read from a cell in Excel, which is something like this:

""Horace Lai" & vbNewLine & Date"

or this

"Chr(149) & "level 1" & vbNewLine & Chr(9) & Chr(149) & "level 2" & Chr(149) & "level 1" & vbNewLine & Chr(9) & Chr(149) & "level 2" & vbNewLine & Chr(9) & Chr(9) & Chr(149) & "level 3""

I would like to be able to evaluate these strings so that ""Horace Lai" & vbNewLine & Date" becomes:

Horace Lai 2014-06-20

So far I have tried ScriptControl without success. It doesn't seem to like it when I have double quotes for the string.

Sub testing()

Dim sc As ScriptControl
Set sc = CreateObject("ScriptControl")
sc.Language = "JScript"

MsgBox sc.Eval(""Horace Lai" & vbNewLine & Date")

End Sub

The MsgBox line becomes red and I cannot run it.

Any ideas? Thanks -Horace

Solution

  • You specify JScript, instead:

    Dim test As String, result As String
test = "Chr(149) & ""level 1"" & vbNewLine & Chr(9) & Chr(149) & ""level 2"" & Chr(149) & ""level 1"" & vbNewLine & Chr(9) & Chr(149) & ""level 2"" & vbNewLine & Chr(9) & Chr(9) & Chr(149) & ""level 3"""

Dim sc As Object
Set sc = CreateObject("ScriptControl")
sc.Language = "VBScript"

result = sc.Eval(test)

Debug.Print result

•level 1
    •level 2•level 1
    •level 2
        •level 3