How to evaluate an excel formula inside vb.net
I, in my opinion, have a completely different situation. I am trying to write a custom function for an excel based program that I use regularly. The main problem is that you cannot reuse code in terms of having a function and returning an evaluated formula. So I got an idea: I can pull data from excel with arguments, let vb.net put those arguments in the code and then evaluate it in excel. So let me give you an example.
This code will live in a specific name called "Source Code" in excel. ="Width+{0}+Height+{1}"
As you can see, two things are happening.
# 1. I make the whole formula line # 2. I put in some "placeholders" for my two arguments to be passed.
Now the two arguments will be placed also in specific names Cell B1 (Argument 1) =1+1=2 (Cell can be evaluated in excel)
Cell C1 (Argument 2)=10+10=20 (Cell can be evaluated in excel)
Now this is where the function happens. Cell D1=myformulaparsingfunction(Source_Code, Argument_1, Argument_2)
At the end of vb.net, I'll pull out the source_code string and then parse the placeholders with the arguments. So I end up with something like this ... ="Width+2+Height+20"
This is where I can turn to my problem. When I parse the formula, it is still a string. I need to be able to tell excel to evaluate this string as a formula. If any of you know a way to do this, or perhaps an alternative way to achieve what I'm trying to do, I'm open to hearing your idea. Hopefully the above has been written clear enough to understand where I am trying to go with this code. Thanks in advance.
Joe
source to share
You should use the method Evaluate
for whatever you want. A short sample:
Dim xlApp As Excel.Application
Set xlApp = New Excel.Application
xlApp.Visible = False
MsgBox xlApp.Evaluate("=5+(10+6)*100")
xlApp.Quit
Set xlApp = Nothing
More details: http://www.xtremevbtalk.com/archive/index.php/t-177848.html
source to share