A function that returns the formula of a cell as a string
 

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Hi,

With a function. Alt + F11 to open VB editor. Right click 'This Workbook'
and insert module and paste this in

Function ShowFormula(Cell As Range) As String
ShowFormula = Cell.Formula
End Function

In a worksheet cell type
=showformula(A1)

Mike

"Lacty" wrote:

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Lacty,

Function MyForm(myC As Range) As String
MyForm = myC.Formula
End Function

Copy that code into a regular codemodule, then use it like

=MyForm(A3)

HTH,
Bernie
MS Excel MVP


"Lacty" wrote in message
...
Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Bernie

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem








On Mar 6, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Lacty,

Function MyForm(myC As Range) As String
MyForm = myC.Formula
End Function

Copy that code into a regular codemodule, then use it like

=MyForm(A3)

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Hi

Create an user defined function (UDF) for this. Open VBA Editor (Alt+F11),
add a module, and copy the code from below the
-----------

Option Explicit


Public Function ShowFormula(varCell As Range, Optional parVolatile As Date)
As String

ShowFormula = varCell.Formula

End Function
------------

Now, into B3 enter the formula
=SHOWFORMULA(A3,NOW())


Of-course this UDF example is written on fly, without any error checkings
etc., so to make it really usable, you have to improve it. But it will be a
good starting point.

--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Lacty" wrote in message
...
Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Mike

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem




On Mar 6, 2:08 pm, Mike H wrote:
Hi,

With a function. Alt + F11 to open VB editor. Right click 'This Workbook'
and insert module and paste this in

Function ShowFormula(Cell As Range) As String
ShowFormula = Cell.Formula
End Function

In a worksheet cell type
=showformula(A1)

Mike




"Lacty" wrote:
Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

I Don't of a method but lets wait and see if someone else does.

"Lacty" wrote:

Mike

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem




On Mar 6, 2:08 pm, Mike H wrote:
Hi,

With a function. Alt + F11 to open VB editor. Right click 'This Workbook'
and insert module and paste this in

Function ShowFormula(Cell As Range) As String
ShowFormula = Cell.Formula
End Function

In a worksheet cell type
=showformula(A1)

Mike




"Lacty" wrote:
Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

Atem,

AFAIK, there is no way - possibly using some Excel 4 XLM code, but that is often a dicey
proposition.

HTH,
Bernie
MS Excel MVP


"Lacty" wrote in message
...
Bernie

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem








On Mar 6, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Lacty,

Function MyForm(myC As Range) As String
MyForm = myC.Formula
End Function

Copy that code into a regular codemodule, then use it like

=MyForm(A3)

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty

A function that returns the formula of a cell as a string
 

On Mar 6, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Atem,

AFAIK, there is no way - possibly using some Excel 4 XLM code, but that is often a dicey
proposition.

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Bernie

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem

On Mar 6, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Lacty,

Function MyForm(myC As Range) As String
MyForm = myC.Formula
End Function

Copy that code into a regular codemodule, then use it like

=MyForm(A3)

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty



Bernie

I have gone with the UDF. It very simple and easy to use. If I take
this one step further, I have some cells with range names so using the
udf MyForm() returns = RangeName. Is it possible to write a code or
function that would return the actual cell address as oppose to the
range name?

Thanks very much

A function that returns the formula of a cell as a string
 

Atem,

Give this version a try - it will replace single name references in cases like

=myName

but not in cases like

=myName * 4


HTH,
Bernie
MS Excel MVP

Function MyForm(myC As Range) As String
Dim myAdd As String
MyForm = myC.Formula
On Error GoTo NotName
myAdd = Range(Mid(MyForm, 2, Len(MyForm))).Address(False, False)
MyForm = "=" & myAdd
NotName:
End Function




"Lacty" wrote in message
...
On Mar 6, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Atem,

AFAIK, there is no way - possibly using some Excel 4 XLM code, but that is often a dicey
proposition.

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Bernie

Thanks for the response. But isn't it possible within Excel other than
using a user defined function?

Kind regards

Atem

On Mar 6, 2:12 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Lacty,

Function MyForm(myC As Range) As String
MyForm = myC.Formula
End Function

Copy that code into a regular codemodule, then use it like

=MyForm(A3)

HTH,
Bernie
MS Excel MVP

"Lacty" wrote in message

...

Hi

Does anyone know how to write a formula which will return the formula
in another cell as a string?

e.g if A3 contains the formula A1+ A2, I want to write a formula in B3
with will return the formula in A3 as string '= A1 + A2. Hence the
value in B3 will be string '= A1 + A2

Thanks in advance

Lacty



Bernie

I have gone with the UDF. It very simple and easy to use. If I take
this one step further, I have some cells with range names so using the
udf MyForm() returns = RangeName. Is it possible to write a code or
function that would return the actual cell address as oppose to the
range name?

Thanks very much