return a formula instead of a value of a cell

[willemeulen]

Is it possible to let excel return a formula instead of the cells value. At
this stage I need to write all possible formula's for each cell (vlookup will
return the corret one depending on a code inserted by user). I was wondering
if it was possible to return a formula instead of a value. This would make my
lookup table considirable smaler and avoids repeating all formulas 60 times.
And I only have to edit one formula/cell to correct mistakes.

Example:
Cell B3 on Sheet2 contains the following

O&row/P&row * Q&row

I want excel to actually return this formala to the cell on sheet1 and not
the result.

thanks

 

[Dave Peterson]

You could use a UDF (User Defined Function) that returns a string--but it's not
a formula anymore.

 

[Gary''s Student]

Here is a User Defined FUnction to return the formula:

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


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 use the UDF from the normal Excel window, just enter it like a normal
Excel Function:

=formulaa(Sheet1!A1)

To remove the UDF:

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

To learn more about UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx