How to make a string to a formula in Excel?

Question

I've built a formula like this using the CONCATENATE function:

=CONCATENATE("=SUM('D:\Tour\", TEXT(A2,"yyy"), "\", TEXT(A2,"mmmm"), "\", TEXT(A2,"mmm"), " ", TEXT(A2,"d"), "\[", B2, ".xlsx]Sheet1'!E:E)")

and it returns output like the following:

=SUM('D:\Tour\2017\February\Feb 1[11043.xlsx]Sheet1'!E:E)

Column A contains the date, and column B contains the filename.

At this point, I am copying the formula into notepad and then pasting back into Excel. Is there any way I can use the formula directly?

(I tried indirect formula but it doesn't work.)

Answer 1

Based on the answers to this question, you can write the following VBA function:

Function EV(s As String) As Variant
    EV = Evaluate(s)
End Function

and call it from Excel:

=EV("=A1+B1")

or

=EV(C2)

^{There is a second answer to that question which doesn't involve VBA, but I was unable to get it to work.}