range.formula

[Osmar Mateus]

I need to solve this problem:
In a cell named item I have a text "unit price".
In a cell named unitprice I have a number 3.45.
In a cell named linetext I have a text "=concatenate(item;unitprice)"
Is it possible to use range.formula to do this using VBA? Is there another way to do this?
I need this type of solution because the formulas in linetext will change based in other cells.

Thanks a lot.

[Tarball]

Of course this is possible and its pretty trivial too, simply concantenate the values of each cell or apply a formula..do whatever.

If you have an example spreadsheet, please post it and explain where you would like to see the change.

[Excelenator]

Of course this is possible and its pretty trivial too, simply concantenate the values of each cell or apply a formula..do whatever.

You really shouldn't be condescending to people who are asking for help.

[Osmar Mateus]

I am sorry because I have dificuties to expres myself in english.

The problem is that the concatenated formula is just an example. The content of this cell will keep changing. Next time, maybe the content will be =unitprice * quantity.

I would like to know if it is a way of executing a formula that is inside a cell as a text.

Thanks for your help.

[shg]

Yes, you can evaluate a text formula.

• Select cell B1.
• Do Insert > Name > Define, create the name Eval (or anything else you want to call it), and in RefersTo enter =Evaluate(A1)
• Now put a text formula in some cell, say F2: =5^3. Note the leading apostrophe (or you could format the cell as text before entering). That's pretty mindless, but any valid Excel formula will work.
• In the next cell to the right, enter =Eval

Presto, 125!

Here's what's entered:

Please Login or Register  to view this content.

Here's what shows up

Please Login or Register  to view this content.

So wherever you enter it, =Eval will evaluate the text formula to its left. Pretty neat, huh?

[Excelenator]

Ok so, if I understand this correctly, Osmar is trying to evaluate a formula in a cell that is the result of a concatenation formula of actual named ranges?

I have attached a file with what I interpret this to be. The only problem that I am having is that the =Eval is not volatile, meaning it doesn't automatically recalcualte with the spreadsheet. Is this normal behavior?

[Osmar Mateus]

Dear sgh

I did exactly as you said. Unfortunatelly it didnīt work as you can see in the attachmente.

Thanks a lot.

Osmar

[Tarball]

You really shouldn't be condescending to people who are asking for help.

Quite the contrary, I meant no disrespect and I surely was not being condescending. I am always willing to help and glad to do it.

Its interesting that you read a negative tone when you can also read my post with an upbeat "Of course you can!, Let us show you how!" tone....why are you so quick to think the negative?

[Excelenator]

Sorry, must be the negative conotation that I derived from your use of the word "trivial" and lack of exclamation points or smileys to give me that warm and fuzzy feeling from your post!

[shg]

Osmar, I'm sorry to tell you I can't answer your question. From your attachment, it appears you have indeed done it correctly.

My guess is that your Spanish version of Excel uses a different name for the XLM Evaluate function. Perhaps someone else knows.

[Osmar Mateus]

Dear shg

You helped me indeed. Now I will try to make my portuguese (brazilian) version work.

Osmar

[Osmar Mateus]

Dear Excelenator

I tried the workbook that you sent to me and it worked perfectly! It helped me to find the equivalent expression in Portuguese (avaliar).

If I am not asking too much, is there a VBA property (object, method) that does the same?

Thanks a lot.

Osmar

[shg]

Osmar,

VBA also has an Evaluate function:

Please Login or Register  to view this content.

Brackets are a shortcut to the function:

Please Login or Register  to view this content.

[just66horns]

In the example way above, what does the evaluate color blue stuff do??
This does not seem to work in Excel 2007

[shg]

It's a mistake; they were supposed to be HTML tags to make the formula blue to stand out.