On Wed, 30 May 2012 15:43:00 +0000, plotzmoney wrote:


Ron, thank you for the response. I was looking for what you were eluding
to in your last question. Where all formulas, even within referenced
cells, would be converted to values. Again, thanks for chiming in and
for the help.



This User Defined Function can do some of what you want. It will only convert cell references to the values within those cells. I believe it will retain the format of those cells. Multicell range references will viewed as an array of values. Perhaps it will do what you require.

The long string of .pattern is a regular expression that should be able to recognize cell references of the A1 system. Ensure it is all on one line as the NG display may word wrap inappropriately.

I can't do much tweaking, but it should work for simple formulas. Anything more complex will require a formula parser, which is beyond what I can provide for you.


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConvertFormula(A1)

in some cell.

========================================
Option Explicit
Function ConvertFormula(r As Range) As String
Dim re As Object, mc As Object, m As Object
Dim re2 As Object, sRepl As String
Dim s As String, v As Variant
Dim i As Long
Dim c As Range
s = r.Formula

Set re = CreateObject("vbscript.regexp")
Set re2 = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = "\$?\b(?:XF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?"
End With

If re.Test(s) = True Then
Set mc = re.Execute(s)
For Each m In mc
If InStr(m, ":") 0 Then
ReDim v(1 To Range(m).Count)
For i = 1 To UBound(v)
v(i) = Range(m)(i)
Next i
sRepl = "{" & Join(v, ", ") & "}"
Else
sRepl = Range(m).Text
End If
With re2
.Global = True
.Pattern = "\b" & m & "\b"
s = .Replace(s, sRepl)
End With
Next m
End If

ConvertFormula = s

End Function
=================================================