MeadInKent
| 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 function 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.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

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