Extract Formula to Text

[Elton Law]

Hello,
Apologize if the question is too stupid.
In cell A4, it contains a formula =Sum(A1:B3)+A3/B2.
How to extract this formula in cell A6 as a string of text please ?
That is ... I want cell A6 to display =Sum(A1:B3)+A3/B2
I know Control + ~ can view this formula in a spreadsheet.
But that is view only. Another control + ~ will turn back to normal.
Also, put ' at the front and paste the formula can help one cell ... but
this is not applicable in VBA (marco writing).
VBA will capture the script as below
ActiveCell.FormulaR1C1 = "'=SUM(A1:B3)+A3/B2"
Next time the formula in cell 6 may be =A3-B2+A1 (not =SUM(A1:B3)+A3/B2)
Use Marcos to run will become overwrite the new formula to =SUM(A1:B3)+A3/B2
instead of =A3-B2+A1
Someone asked me to use Word as interface and then copy/paste as value.
But I want to use the functions within Excel or use Marcos to accomplish
changing cell formula to text.

Can any expert show me how to solve please ?
Thanks

 

[Gary''s Student]

Consider the following User Defined Function (UDF):

Function SeeFormula(r As Range) As String
SeeFormula = r.Formula
End Function

So if cell A1 contains:
=1+2

=SeeFormula(A1) in another cell will display the formula in A1 and not the
result!

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=SeeFormula(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs

 

[Elton Law]

Thanks Gary ....
That is great ....
I make it ....
But there is one more stupid question.
How to put this UDF in Excel and apply to all the workbooks I open please ?
I mean makie it built-in to Excel and I use every time I like.
Thanks so much !!!!!

 

[Gary''s Student]

You can save it as an add-in and install it whenever you want.

or

Put the file in your XLSTART folder, so it will always open and be available.

 

[Elton Law]

You are great great great great great great great great
All Solved ..................
Thanks a lot ....

 

[Ashish Mathur]

Hi,

You can also try this. Define a named range as
=GET.CELL(6,INDIRECT("R[-2]C",)) - give it a name as "formula". Now in cell
A6, enter =formula

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Elton Law]

Hi Gary,
Please let me ask one more question ....

I tried this one.
It is (1+2+3)/6 = 1

1
2
3
6
1
=(A1+A2+A3)/A4

Formula display correctly too. Thanks for your great help.

A5 is showing 1 as anwer.
A6 is showing the formula (you taught me. Thanks again).

Can cell A7 show the answer as A5 ?
Using =A5 in A7, A7 will become 1.
I want to display the answer in address (cell reference).
Is it feasible ?
Thanks