How do you display a formula in a cell

[Dave L]

Can someone remind me please how to display a formula rather than a result
in a specific cell, but not the whole worksheet please

Thanks
Dave

 

[Niek Otten]

Hi Dave,

Use this UDF:

' =======================================

' Niek Otten

' March 31, 2006

Function ShowFormula(a As Range)

If Application.ReferenceStyle = xlR1C1 _

Then ShowFormula = a.FormulaR1C1 _

Else: ShowFormula = a.Formula

End Function

' =======================================



If you're new to UDFs, follow these steps:



================================================

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(..)

================================================

 

[Ken Wright]

Put an apostrophe in front of the formula, or remove the = sign.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[vandenberg p]

Hello:

Actually if I understand your question there is no way to do it.
(Quattro Pro has supported this feature for many version. You
can change part of the spreadsheet to display the formula
while another part displays results. The results are not
affect by the display. So you can display a result that depends
on a value that is displayed as a formula.)

So you have two options. One like below (or my slightly
different version) that will display the contents of cell someplace
else (next to it or above it or below it) without affecting the
results of any calculations. Or you can the other suggestion
in this tread which is to make it a label by using an '.
The problem with it is that any cell that depends on the
value will now not calculate correctly. It is unfortunate
that Excel text display does not do exactly what it says.
Which is: "The cell is displayed exactly as entered."

My version of a routine below is: (I keep in Personal.xls)

Private Function celltext(cell)
Dim result
If cell.HasArray Then
result = "{" & cell.FormulaLocal & "}"
Else
result = cell.FormulaLocal
End If
celltext = result
End Function


The only difference from that given by Niek Otten is that
if the formula is an array (entered with cntrl-Shift-enter
then the "{}" will show so that you can see it is
an array formula.


Pieter Vandenberg


: Hi Dave,

: Use this UDF:

: ' =======================================

: ' Niek Otten

: ' March 31, 2006

: Function ShowFormula(a As Range)

: If Application.ReferenceStyle = xlR1C1 _

: Then ShowFormula = a.FormulaR1C1 _

: Else: ShowFormula = a.Formula

: End Function

: ' =======================================



: If you're new to UDFs, follow these steps:



: ================================================

: 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


:> Can someone remind me please how to display a formula rather than a result
:> in a specific cell, but not the whole worksheet please
:>
:> Thanks
:> Dave

 

[Niek Otten]

<the "{}" will show so that you can see it is an array formula>

Nice addition, Pieter!
The other difference is that mine looks after the reference system too. I know not many people use R1C1, but some do. However, the
function is not volatile for changing the reference system.

 

[David McRitchie]

See http://www.mvps.org/dmcritchie/excel/formula.htm
for the reminder and a lot of similar material. If the formula
display is not next to the actual use but only a representative
formula you might prefer GetFormulaD instead of GetFormula

 

[Dave L]

See http://www.mvps.org/dmcritchie/excel/formula.htm
for the reminder and a lot of similar material. If the formula
display is not next to the actual use but only a representative
formula you might prefer GetFormulaD instead of GetFormula

I haven’t had time to play with any of the suggestions yet, but I’m sure
one will work

Thanks
Dave