evaluating strings in VBA

Question

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

Answer 1

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

Answer 2

Firstly load the name into a variable i.e. name = """Horace Lai""" (this will contain the quote marks)

Then use the replace function to search for quotes in name name = Replace(name,"""","")

Then run the msgbox of MsgBox (name & vbNewLine & Date)

This should remove the extra " marks that you face