Function to convert a formula to text

[lawson]

Hello all,

i presently have a function that converts a formula entered in a cell into
text such that a viewer can see the formula as it was typed:

Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function

the user simply types =ShowFormula(cell) and the formula is revealed.

the problem is that the result is in the format =d4+d5-SQRT(f5) when i would
like it to read =44+33-sqrt(69)

is there a way to modify the function such it it shows the numbers from the
linked cells rather than the address of the cell?

thank you very much

 

[Gary]

Lawson,

Clicking Ctrl+` will show all formalas on the worksheet.

 

[lawson]

yes, but that does nothing for me in terms of showing the values like i asked.

 

[Stan Brown]

Thu, 20 Dec 2007 11:29:02 -0800 from lawson

i presently have a function that converts a formula entered in a cell into
text such that a viewer can see the formula as it was typed:

Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function

the user simply types =ShowFormula(cell) and the formula is revealed.

the problem is that the result is in the format =d4+d5-SQRT(f5) when i would
like it to read =44+33-sqrt(69)

is there a way to modify the function such it it shows the numbers from the
linked cells rather than the address of the cell?

Yes, there's a way, but it would take a LOT of programming. You'd
essentially have to duplicate some of the Excel parser.

It's not as simple as just looking for cell references, looking up
the cell values, and replacing them. You'd also have to be prepared
to cope with defined names.