View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Elton Law[_2_] Elton Law[_2_] is offline
external usenet poster
 
Posts: 173
Default Extract Formula to Text

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200844


"Elton Law" wrote:

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" wrote:

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/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200844


"Elton Law" wrote:

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