Home	Excel	Access
\| Feedback \| Checking spreadsheets \| The CELL() function \|

A visual basic function to display a cell formula

You may wish to display the script of a formula contained in a particular cell. This may be for audit purposes, training other users or to assist with building a spreadsheet model.

The Excel ribbon option '[File] | Options | Advanced | (options for this worksheet) Show Formulas in cells instead of their calculated results' will affect a particular worksheet so that it displays all of the formulas rather than the normal calculated values. This is an all or nothing option. It can not be applied to the contents of particular cells.

-	M	N
15	Site Visitors
16
17	Month	Number
18	May 07	9409
19	June07	9160
20	July 07	8564
21	Total	27133
22	Average	9044
23
24	[N22] =$N$21/3
25	[M24] =FormText(N22,1)

In the example above, the user defined 'FormText()' function is used in cells M24 and M25 to display the contents of N22 and M24.

Function FormText(CellRef As Range, Optional RefIndicator As Integer) As String

      ' RefIndicator: 0 do not show, 1 show reference, 2 show absolute reference

      Dim n As Integer, f As Integer

      

      If IsNull(RefIndicator) = True Then

      RefIndicator = 0

      End If

      

      FormText = CellRef.Formula

      

      If RefIndicator > 0 Then

 FormText = "[" & CellRef.Address & "] " & FormText

      End If

      

      n = 1

      f = InStr(1, FormText, "]")

      

      Do While n < f

    If RefIndicator = 1 And Mid(FormText, n, 1) = "$" Then

      FormText = Trim(Left(FormText, n - 1) & Mid(FormText, n + 1, 200))

    End If

    If Mid(FormText, n, 1) = "," Then

      FormText = Trim(Left(FormText, n) & " " & Mid(FormText, n + 1, 200))

    End If

   n = n + 1

      Loop

      End Function

The macro shown here should be entered into a Visual Basic module, either in a specific workbook or in your (hidden) Personal.xls macro workbook. It can then be used like any other Excel function - either typed directly into a cell or selected from the Paste Function wizard.

A useful tool for checking cell contents is my 'SheetMap' spreadsheet. It creates a duplicate of a worksheet, labelling each cell according to the type of contents.

Get this information as a document
accompanied by Excel worksheets

Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file:xlformtext.htm

Page last updated Jan 14

CMIDX S3 P9 Y