 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / March 2008A 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 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 > Hi > [quoted text clipped - 8 lines] > > Lacty Mike Thanks for the response. But isn't it possible within Excel other than using a user defined function? Kind regards Atem > Hi, > [quoted text clipped - 9 lines] > > Mike > > Hi > [quoted text clipped - 8 lines] > > > Lacty I Don't of a method but lets wait and see if someone else does. > Mike > [quoted text clipped - 31 lines] > > > > > Lacty 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 > Hi > [quoted text clipped - 8 lines] > > Lacty 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, > [quoted text clipped - 22 lines] > > > 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 > [quoted text clipped - 33 lines] >> >> > Lacty On Mar 6, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org> wrote: > Atem, > [quoted text clipped - 46 lines] > > >> > 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 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 > On Mar 6, 3:06 pm, "Bernie Deitrick" <deitbe @ consumer dot org> > wrote: [quoted text clipped - 58 lines] > > Thanks very much 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.  SignatureArvi Laanemets ( My real mail address: arvi.laanemets<at>tarkon.ee ) > Hi > [quoted text clipped - 8 lines] > > Lacty |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.