VBA Function to convert string to formula

[axel]

Probably there is no solution, but I cannot imagine (maybe some on
knows better)...

I want to make a VBA function, which is able to convert a text strin
to a spreadsheet formula.

I was thinking about something like: TextToFormula(Sting)

Example:
Sheet1!A1 = 20
Sheet2!A1 = 10
Sheet3!A1 = 5

B1 = TextToFormula(C1)
C1 = '=Sheet1!A1-Sheet2!A1)/Sheet3!A1
Result in B1 should be = 2

Someone has any idea?

Thanks for your help,
Axe

 

[Jan Karel Pieterse]

Hi Axel,

I want to make a VBA function, which is able to convert a text string
to a spreadsheet formula.

I was thinking about something like: TextToFormula(Sting)

Example:
Sheet1!A1 = 20
Sheet2!A1 = 10
Sheet3!A1 = 5

B1 = TextToFormula(C1)
C1 = '=Sheet1!A1-Sheet2!A1)/Sheet3!A1
Result in B1 should be = 2

Someone has any idea?

Can be done without VBA.

- Select cell B1.
- Insert, name, define.
Name: EvalFormula
Refers to: =Evaluate(C1)
(note: NO dollar signs to make the cell references in the
formula absolute).

Now in cell B1 enter:
=EvalFormula

That will show the result of the formula of a cell to the immediate
right of the cell with the =EvalFormula formula.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[axel]

Hi Jan,
Thanks for this quick response. I have never seen the EVALUATE formul
before (and don't find it in the Excel help).
I tried it and it works fine, except it does not recalculat
automatically when you change the parameters (i.e. Sheet1!A1 = 20).
need to go into B1 and re-hit enter to refresh.

The bigger contect of my string to formula problem is, I have severa
work sheets with the same layout of data organisation (let's say o
each sheet are 50 cells with data). I do want to create one "analysi
sheet" with the same layout as the other sheets. On this analysis shee
I want to add, substract, devide or multiply the different sheets in
flexible way.

The principle solution (principle since I don't find the way to conver
a text to a formula....) I have in mind is the following:

1. Enter a generic formula syntax in cell C1 on te analysis shee
i.e.:
C1='=(Sheet1!xx-Sheet2!xx)/Sheet3!xx
So I need to enter only once which sheets I want to add, substract o
multipy in a generic way.

2. I would use the following formla within the 50 analysis cells t
convert my generic text formula to containing "xx" for the cells

=SUBSTITUTE(C1,"xx",ADDRESS(ROW(),COLUMN()))
If I enter this formula for all the 50 cells, I will get for example i
cell A10 on the analysis sheet: '=(Sheet1!A10-Sheet2!A10)/Sheet3!A10

3.) Now I just would need to find a way to run a function like:
A10 = TextToFormula(SUBSTITUTE(C1,"xx",ADDRESS(ROW(),COLUMN()))) (thi
is the function which
or
A10 = EVALUATE(SUBSTITUTE(C1,"xx",ADDRESS(ROW(),COLUMN()))) (whic
does not work like that unfortunately on a sheet)


If there is a solution to this, this will be a way to very efficientl
handle quick data analysis, without copying all the time the desired
formulas to all cells on the analysis sheet.

Thanks again for your earlier respond Jan! maybe you have anothe
idea?

Regards,
Axe

 

[Jan Karel Pieterse]

Hi Axel,

Thanks for this quick response. I have never seen the EVALUATE formula
before (and don't find it in the Excel help).

It is an old XL4 Macro formula.

I tried it and it works fine, except it does not recalculate
automatically when you change the parameters (i.e. Sheet1!A1 = 20). I
need to go into B1 and re-hit enter to refresh.

Try this defined name instead:

=EVALUATE((SUBSTITUTE(Sheet1!$C$1,"xx",ADDRESS(ROW(),COLUMN()))))+0*NOW(
)

But omit the apostrophe in front of your formula in Cell C1 (format the
cell as text first).

Warning!!! Do not copy the cells with the formula =EvalFormula to
another worksheet, Excel might crash.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Charles Williams]

If you want a VBA function try this

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(theInput)
Else
vEval = Application.Evaluate(theInput)
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

regards
Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

 

[axel]

Excellent Jan!

Your answers really helped me to get on the right track!

Since I don't get the evaluate function work on the sheet (maybe linke
to Excel version 2002), I wrote the following function:

Function myFunct(InputString)
myFunct = Application.Evaluate("=" & InputString)
End Function

Combined with your brilliant idea to add "+0*now()", this wil
calculate each time some data changes.

=myFunct(SUBSTITUTE($C$1,"xx",ADDRESS(ROW(),COLUMN())))+0*NOW()

Thanks a 1000 times - this makes my day! Maybe I should take off fo
the rest of the day...

Cheers.....Axe

 

[Jan Karel Pieterse]

Hi Axel,

Since I don't get the evaluate function work on the sheet (maybe linked
to Excel version 2002)

Worked fine for my XL XP.

Function myFunct(InputString)
myFunct = Application.Evaluate("=" & InputString)
End Function

You could include Application.Volatile and omit the 0*now bit.

Maybe I should take off for
the rest of the day...

You have my permission <bg>.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[David McRitchie]

A lot of usage that wants to do this is actually trying to document
a formula. If that is the actual purpose then it would be better
to start from a working formula and leave it in place and to show
the contents of the formula in another cell i.e. GetFormula in
http://www.mvps.org/dmcritchie/excel/formula.htm
That way there is not question that a function works.
---

 

[Harlan Grove]

...
...

Can be done without VBA.

- Select cell B1.
- Insert, name, define.
Name: EvalFormula
Refers to: =Evaluate(C1)
(note: NO dollar signs to make the cell references in the
formula absolute).

Now in cell B1 enter:
=EvalFormula

That will show the result of the formula of a cell to the immediate
right of the cell with the =EvalFormula formula.

**CAVEAT**

This is **HIGHLY** dangerous in Excel 2000 and prior versions. Copying cells
containing formulas with references to defined names calling XLM functions and
pasting into different worksheets will invariably *CRASH* excel WITH TOTAL DATA
LOSS. Vary bad advice without this caveat.

 

[Jan Karel Pieterse]

Hi Harlan,

Vary bad advice without this caveat.

I know, which is why I added it in my second post:

Warning!!! Do not copy the cells with the formula =EvalFormula to
another worksheet, Excel might crash.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Harlan Grove]

...
...

I know, which is why I added it in my second post:

...

You're right. Still, XLM is very dangerous.

 

[HOMOONAR]

Hi, I am new, interesting topic.
although i still don't know how to resolve the prolem i have..
i want to add +4 to a formula alread got =1+2+3
(so i have =1+2+3+4 shown as a value, but with that formula)
without maually doing so... as i have a lot of these
example:
a1= '=1+2+3'
i can replace the = sign with nothing, and then do ="="&A1"+3"

also anoth problem with charts.. just want to add a new line to an existing chart as quick as i can,
as i have loads of charts

for example:
table 1
1 2 3 4
1 3 4 5
2 4 6 5

each row has one chart on a separate worksheet.
if i have another table
table 2
2 3 3 1
1 4 5 6
2 3 5 6

and i want to add 2 3 3 1 to the 1 2 3 4 chart and so on
is there a quicker way?