Extract Formula to Text

  • Thread starter Thread starter Elton Law
  • Start date Start date
E

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
 
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
 
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 !!!!!
 
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.
 
You are great great great great great great great great
All Solved ..................
Thanks a lot ....
 
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
 
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
 
Back
Top