Display cell formula by function

[0-0 Wai Wai ^-^]

Hi.
Is there a way to perform a function which display the formula of the target
cell (NOT the value of the cell)?

{Fictitious function}
ShowFormula(TargetCell)

Eg:
A1 = sum(3, 4)
A2 = ShowFormula(A1)
Answer of A2: sum(3, 4)

 

[Barb Reinhardt]

You could copy the equation to the cell and then put " in front of it (short
term)

 

[Earl Kiosterud]

Wai,

There is now. Put this in a module in your workbook:

Function ShowFormula(Target As Range) As String
If Target.HasFormula Then ShowFormula = Target.Formula
End Function

 

[0-0 Wai Wai ^-^]

You could copy the equation to the cell and then put " in front of it (short
term)

Thanks for your suggestion.
It is a good one for most people.
However I need the ShowFormula to be dynamically linked.
So when the formula changes, the ShowFormula will update too.

 

[0-0 Wai Wai ^-^]

Wai,

There is now. Put this in a module in your workbook:

Function ShowFormula(Target As Range) As String
If Target.HasFormula Then ShowFormula = Target.Formula
End Function

Thanks a lot.
Sorry for my ignorance. I don't know why I can't make it work.

What I did is:
- go to "Tools | Macro | Macros... | Create"
- type the following:
Sub ShowCellFormulaFunction()
End Sub

Function ShowFormula(Target As Range) As String
If Target.HasFormula Then ShowFormula = Target.Formula
End Function

- save the macro
- run it

When I run it, nothing happens.
Strange?!?

 

[Dave Peterson]

You can toss this one:

Sub ShowCellFormulaFunction()
End Sub

But this one:

Function ShowFormula(Target As Range) As String
If Target.HasFormula Then ShowFormula = Target.Formula
End Function

is a function.

If your formula is in A1, then put this in B1:
=showformula(a1)

And watch what happens.

 

[Earl Kiosterud]

Wai Wai,

In addition to what Dave said, you need to type or paste (from here) the
function into a module in the VBE. It's not a sub. It's a function.

Function ShowFormula(Target As Range) As String
If Target.HasFormula Then ShowFormula = Target.Formula
End Function"

Earl Kiosterud
www.smokeylake.com

 

[0-0 Wai Wai ^-^]

Thanks a lot.

By the way, how can I make a function/macro available for all worksheets (or a
particular type of worksheets)?
Thanks a lot.

 

[Dave Peterson]

Do you mean available to workbooks?

If yes, then open the workbook with the code (say it's named myMacros.xls)
Then use a formula like:
=mymacros.xls!showformula(a1)

If you save that workbook with the code as an addin, you can use:
=showformula(a1)

(but the addin still needs to be open)

 

[0-0 Wai Wai ^-^]

Do you mean available to workbooks?

Sorry for the ambiguity.
What I mean is every time I open any *.xls (no matter new or old, I am able to
use this function.
..
..

If yes, then open the workbook with the code (say it's named myMacros.xls)
Then use a formula like:
=mymacros.xls!showformula(a1)

If you save that workbook with the code as an addin, you can use:
=showformula(a1)

(but the addin still needs to be open)

Do you mean:
- I should create a new worksheet as a template (which contains my functions)
- Then when I use that user-defined function, I simply call it from that
worksheet template
Note: That worksheet template must open when any other worksheets have this
function (in their cells)
??

If so, it seems not to be a perfect solution.
But if it is the only workaround, I will accept it.
Thanks for your help.

 

[Dave Peterson]

Not a template (*.xlt). Save it as an Addin (*.xla).

You can either install the addin via tools|Addins (and browse to where you saved
it).

Or put it in your XLStart folder.

 

[kcc]

Just out of curiosity, are you using this to build dynamic documentation for
Sarbanes-Oxley?
kcc

 

[0-0 Wai Wai ^-^]

Thanks.
By the way, how can I add my user-defined function to a category?
Here's what I found:
==============================
How to add your UDF to a category

Another Frequently Asked Question is, how to add your UDF to a certain category
in the "Paste Function" dialog box.
By default all UDF's are added to the "User Defined" category. This is how you
can change the category
In the VBE, copy and paste the following:
Code:
Private Sub ChangeCategory()
Application.MacroOptions Macro:="<Enter the name of your function here>",
Category:=7 ' Add to Text Category
End Sub
==============================

As to the code [Category:=7 ' Add to Text Category], what is it?
What text category do I need to type?

 

[0-0 Wai Wai ^-^]

Just out of curiosity, are you using this to build dynamic documentation for
Sarbanes-Oxley?

Hehe...
Actually I use it to build up some tutorials for others.

Just out of curiosity, why do you think I use this to build dynamic
documentation for Sarbanes-Oxley?

 

[Dave Peterson]

Take a look at VBA's help for .macrooptions. You'll see a long list of
categories. (14 builtin and the rest user defined.)

Thanks.
By the way, how can I add my user-defined function to a category?
Here's what I found:
==============================
How to add your UDF to a category

Another Frequently Asked Question is, how to add your UDF to a certain category
in the "Paste Function" dialog box.
By default all UDF's are added to the "User Defined" category. This is how you
can change the category
In the VBE, copy and paste the following:
Code:
Private Sub ChangeCategory()
Application.MacroOptions Macro:="<Enter the name of your function here>",
Category:=7 ' Add to Text Category
End Sub
==============================

As to the code [Category:=7 ' Add to Text Category], what is it?
What text category do I need to type?

Not a template (*.xlt). Save it as an Addin (*.xla).

You can either install the addin via tools|Addins (and browse to where you saved
it).

Or put it in your XLStart folder.

 

[kcc]

Hehe...
Actually I use it to build up some tutorials for others.

Just out of curiosity, why do you think I use this to build dynamic
documentation for Sarbanes-Oxley?

I'm just finishing a lengthy documentation project that took much
longer than it needed to because I had to write a list of all the
formulas in the spreadsheet with technical and plain English
explanations while the spreadsheets were still in development.
I wanted to use a VBA function that would automatically track
the changes, but the same project also has locked me out of macros.
kcc