On Fri, 21 Dec 2007 08:50:11 -0500, Ron Rosenfeld
wrote:

This version works a bit better in that it does not resolve multicell range
references at all, but rather leaves them "as is"

So, for example:
=ROUND(A1-A2,1)+SUM(B3:B8)

might get displayed as:

=ROUND(976.1234-0,1)+SUM(B3:B8)


Comments in the UDF have been updated:

===============================================
Function ShowFV(rg As Range)
'substitutes the contents of cell references for the references
'when doing ShowFormula
'BUT, it does NOT recognize RANGES, so will leave range references unchanged
'Also, does NOT recognize NAME'd references
'Requires A1 cell reference style

Dim str As String
Dim re As Object, mc As Object
Dim sRepl As String

'test for valid single cell reference
If rg.Count < 1 Then
ShowFV = CVErr(xlErrRef)
Exit Function
End If

str = rg.Formula
Set re = CreateObject("vbscript.regexp")
re.Global = False
'Pattern should recognize only cell references in range A1:IV65536
'Does NOT recognize range reference, e.g: A1:A10
re.Pattern = "([^:])\b(([A-Z]|[A-I][A-V])([1-9]\d{0,3}|[1-5]\d{0,4}|" & _
"6[0-5][0-4]\d\d|655[0-2]\d|6553[0-6]))\b(?!:)"
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1))
str = re.Replace(str, sRepl)
Loop
ShowFV = str
End Function
============================================
--ron