Convert Text into Formula in VBA

[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

 

[Norman Jones]

Hi AK,

Your question is not clear.starting with the text:

18.5x20x15

what is the deired result?

 

[NickHK]

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

 

[Guest]

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

 

[Héctor Miguel]

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

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.

 

[Norman Jones]

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.

 

[Guest]

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.