CODEWITHSUNDEEP
vb.netexcel
joeb
joeb

Reputation: 877

How to evaluate excel formula inside vb.net

I have a rather different situation in my opinion. I'm 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 had the idea for the following. I can pull data in from excel with the arguments, let vb.net place those arguments within the code and then evaluate it back in excel. So let me give an example.

This code would live in a defined name called "Source Code" in excel. ="Width+{0}+Height+{1}" As you can see there are two things going on.
#1. I am making the entire formula a string #2. I am inserting some "placeholders" for my two arguments that will be passed in.

Now the two arguments will be placed in defined names as well 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 here is where the function happens. Cell D1 =myformulaparsingfunction(Source_Code, Argument_1, Argument_2)

In the vb.net back end I will pull in the source_code string, then parse the placeholders with the arguments. So i will get something like this... ="Width+2+Height+20"

This is where i get to my problem. When i parse the formula it still remains a string. I need to have a way to tell excel to evaluate this string as a formula. If any of you know of a way to do this or perhaps an alternate way to achieve what I am trying to do, I am open to hearing your idea. Hopefully, the above was written clear enough to understand where I am trying to go with this code. Thanks in advance.

Joe

Upvotes: 3

Views: 1945

Answers (1)

Ksenia
Ksenia

Reputation: 497

You should use Evaluate method for what you want. 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

Read more: http://www.xtremevbtalk.com/archive/index.php/t-177848.html

Upvotes: 4

Related Questions