Convert Text into Formula in VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-

18.5x20x15

instead of using the INSTR to find each "x" , any other method?

thanks for the help in advance
 
This works for your example. You should add error checking and ensure that
you are replacing the correct chars, but you get the idea.

Public Function MyEval(argCell As Range) As Variant
Dim TempStr As String

TempStr = Replace(argCell.Text, "x", "*")

MyEval = Evaluate(TempStr)

End Function

NickHK
 
Hi Norman,
I would like to have the text become a formula:-

=18.5*20*15

actually this kind of text also exist in the cell
18.5*20*15

then how can this change to the formula?
=18.5*20*15

Finally the value =5550
 
hi, ak !
How can the text in one cell convert into formula?
For example, this is the text in Cells(1,1) :-
18.5x20x15
instead of using the INSTR to find each "x" , any other method?
thanks for the help in advance
Click to expand...

i.e.: -> cells(1,1)="="&replace(cells(1,1),"x","*")

or, if you need for excel 97: -> cells(1,1)="="&application.substitute(cells(1,1),"x","*")

hth,
hector.
 
Hi AK,

Hector's suggestion will furnish a formula either for text
of the type 18.5x20x15 or 18.5*20*15.

Nick's function will return the formula value in either case.
 
I have a related question about converting text into a formula. I'm
developing some spreadsheets for student use. I would like the students to
be able to type in an expression for a function as text -- say something like
x^3-5*x^2+3 -- and then have the spreadsheet convert that text into an active
formula that can be used. By the way, the x would refer to a named cell,
which would then be used in a data table to create a full table.

If anyone can give me some suggestions, I would be most appreciative. Many
thanks for looking.
 
Back
Top