 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 documentaccompanied by Excel worksheets Click here for details about obtaining thisfile. It has been rewritten for Excel 2010.

 file:xlformtext.htm Page last updated Jan 14 © MeadInKent.co.uk 2016 CMIDX S3 P9 Y