Using a function

[Robert E. Leonard Sr]

How do I install the below function in an excel spreadsheet? i received it
as suggestion to handle my question below!


Function ShowFormula(MyCell)
ShowFormula = MyCell.Address & " " & MyCell.Formula
End Function



I am trying to display in a spreadsheet, the formula in an adjent cell.
Any suggestions?

EXAMPLE:

cell A2 contain formula: =Mid(c3,5.2)

In cell B2 i would like to insert a formula displaying above formula.

I know that I can copy the formula to cell B2 and save as text by
inserting a space before = sign. What I am looking for is an automatic
fix.

Thanks, for your help
Bob Leonard
(e-mail address removed)

 

[Alan]

Hit Alt and F11 to open the VB editor
On the top toll bar, click 'Insert', select 'Module'
Copy and paste the function into the empty window
Hit Alt and F11 again to close the VB editor.

Now to show the formula in say A1, in any cell enter:-
=ShowFormula(A1)

This isn't case sensitive,

If you don't want $A$1 =A2+B2, just =A2+B2 change the function to:-

Function ShowFormula(MyCell)
ShowFormula = MyCell.Formula
End Function
Regards,

Alan.

 

[Niek Otten]

Hi Bob,

A standard answer:

================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose Insert>Module. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| How do I install the below function in an excel spreadsheet? i received it
| as suggestion to handle my question below!
|
|
| Function ShowFormula(MyCell)
| ShowFormula = MyCell.Address & " " & MyCell.Formula
| End Function
|
|
|
| I am trying to display in a spreadsheet, the formula in an adjent cell.
| Any suggestions?
|
| EXAMPLE:
|
| cell A2 contain formula: =Mid(c3,5.2)
|
| In cell B2 i would like to insert a formula displaying above formula.
|
| I know that I can copy the formula to cell B2 and save as text by
| inserting a space before = sign. What I am looking for is an automatic
| fix.
|
| Thanks, for your help
| Bob Leonard
| (e-mail address removed)
|
|
|