Well, one of the ways to find enough material on the topic is searching CodeProject:
http://www.codeproject.com/search.aspx?q=Expression+parser&doctypeid=1[
^].
But please, don't tell me "this is not VBA". The problem is more or less serious, and people capable of solving serious problems rarely agree to waste time on VBA. If you want a VBA solution, read some relevant article, understand how the code works and implement it in the language and the system you prefer.
I think that an alternative approach could be fruitful: hosting some available language interpreter in your code. In practice, the most likely and apparent candidate would be Javascript: it already has the function
eval
(
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/eval[
^]) which allows to interpret and run a script written in some string, from the same script:
http://www.codeproject.com/search.aspx?q=%28Javascript+interpreter%29+OR+%28%28host+OR+embed%29+Javascript%29&doctypeid=1[
^].
To get familiar with the problem of hosting/embedding of the interpreter, you can perform something like this "sophisticated" query:
http://www.codeproject.com/search.aspx?q=%28Javascript+interpreter%29+OR+%28%28host+OR+embed%29+Javascript%29&doctypeid=1[
^].
Of course, you can do a global Web search with these queries, Google/Bing it. This way, I found some articles even for VB (but not VBA):
http://www.freevbcode.com/ShowCode.asp?ID=2090[
^],
http://www.freevbcode.com/ShowCode.asp?ID=1263[
^].
The main obstacle? VBA, of course. You just need the simplest features, it looks like. So, probably, the shortest route will be to look at some available expression interpreter and write some minimalistic implementation after learning the main ideas.
In brief, the idea is: the input expression in a single string is parsed into an
expression tree. Then the calculation is performed by recursive traversal of the tree starting from the root:
http://en.wikipedia.org/wiki/Expression_tree[
^].
So, this is yet another option I would personally prefer: write the code from scratch following the article referenced above.
—SA