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

[Lacty]

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

 

[Mike H]

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

 

[Bernie Deitrick]

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]

Bernie

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

Kind regards

Atem

 

[Arvi Laanemets]

Hi

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

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.

 

[Lacty]

Mike

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

Kind regards

Atem

 

[Mike H]

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

 

[Bernie Deitrick]

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]

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

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

 

[Bernie Deitrick]

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