Display cell formula by function

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)

--
Additional information:
- I'm using Office XP
- I'm using Windows XP

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

"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:%[email protected]...
> 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)
>
> --
> Additional information:
> - I'm using Office XP
> - I'm using Windows XP
>
>

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

--
Earl Kiosterud
www.smokeylake.com

"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:%[email protected]...
> 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)
>
> --
> Additional information:
> - I'm using Office XP
> - I'm using Windows XP
>
>

"Barb Reinhardt" <[email protected]> 秎ン
news:%[email protected] い级糶...
> 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 ^-^" <[email protected]> wrote in message
> news:%[email protected]...
> > 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)
> >
> > --
> > Additional information:
> > - I'm using Office XP
> > - I'm using Windows XP
> >
> >
>
>

"Earl Kiosterud" <[email protected]> 秎ン
news:[email protected] い级糶...
> 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?!?


> --
> Earl Kiosterud
> www.smokeylake.com
>
> "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> news:%[email protected]...
> > 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)
> >
> > --
> > Additional information:
> > - I'm using Office XP
> > - I'm using Windows XP
> >
> >
>
>

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.



0-0 Wai Wai ^-^ wrote:
>
> "Earl Kiosterud" <[email protected]> 秎ン
> news:[email protected] い级糶...
> > 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?!?
>
> > --
> > Earl Kiosterud
> > www.smokeylake.com
> >
> > "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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)
> > >
> > > --
> > > Additional information:
> > > - I'm using Office XP
> > > - I'm using Windows XP
> > >
> > >
> >
> >

--

Dave Peterson

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 ^-^" <[email protected]> wrote in message
news:[email protected]...
>
> "Earl Kiosterud" <[email protected]> 秎ン
> news:[email protected] い级糶...
>> 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?!?
>
>
>> --
>> Earl Kiosterud
>> www.smokeylake.com
>>
>> "0-0 Wai Wai ^-^" <[email protected]> wrote in message
>> news:%[email protected]...
>> > 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)
>> >
>> > --
>> > Additional information:
>> > - I'm using Office XP
>> > - I'm using Windows XP
>> >
>> >
>>
>>
>
>

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.


"Earl Kiosterud" <[email protected]> 秎ン
news:[email protected] い级糶...
> 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 ^-^" <[email protected]> wrote in message
> news:[email protected]...
> >
> > "Earl Kiosterud" <[email protected]> 秎ン
> > news:[email protected] い级糶...
> >> 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?!?
> >
> >
> >> --
> >> Earl Kiosterud
> >> www.smokeylake.com
> >>
> >> "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> >> news:%[email protected]...
> >> > 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)
> >> >
> >> > --
> >> > Additional information:
> >> > - I'm using Office XP
> >> > - I'm using Windows XP
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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 ^-^ wrote:
>
> 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.
>
> "Earl Kiosterud" <[email protected]> 秎ン
> news:[email protected] い级糶...
> > 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 ^-^" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > "Earl Kiosterud" <[email protected]> 秎ン
> > > news:[email protected] い级糶...
> > >> 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?!?
> > >
> > >
> > >> --
> > >> Earl Kiosterud
> > >> www.smokeylake.com
> > >>
> > >> "0-0 Wai Wai ^-^" <[email protected]> wrote in message
> > >> news:%[email protected]...
> > >> > 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)
> > >> >
> > >> > --
> > >> > Additional information:
> > >> > - I'm using Office XP
> > >> > - I'm using Windows XP
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> >

--

Dave Peterson

"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> 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. :P

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.

0-0 Wai Wai ^-^ wrote:
>
> "Dave Peterson" <[email protected]> ???
> news:[email protected] ???...
> > 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. :P

--

Dave Peterson

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

"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:[email protected]...
>
>
> "Dave Peterson" <[email protected]> ???
> news:[email protected] ???...
>> 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. :P
>
>

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?



"Dave Peterson" <[email protected]> ???
news:[email protected] ???...
> 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.
>
> 0-0 Wai Wai ^-^ wrote:
> >
> > "Dave Peterson" <[email protected]> ???
> > news:[email protected] ???...
> > > 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. :P
>
> --
>
> Dave Peterson

> 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?
:-P

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



0-0 Wai Wai ^-^ wrote:
>
> 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?
>
> "Dave Peterson" <[email protected]> ???
> news:[email protected] ???...
> > 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.
> >
> > 0-0 Wai Wai ^-^ wrote:
> > >
> > > "Dave Peterson" <[email protected]> ???
> > > news:[email protected] ???...
> > > > 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. :P
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

"0-0 Wai Wai ^-^" <[email protected]> wrote in message
news:[email protected]...
>
>> 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?
> :-P
>
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