A function to Turn Formula into Text?

[lawson]

Hi Ron,

i hope its not too late, but i fugured out how the $ issue, so if its not
too much to ask, please see the question #2 in my previous posting...

Second, and i understand that this could be a tall order, for sf4, which
converts ranges to read 'max(5:33)', is ti possible to show all the values
within the range such that it reads 'max(5, 22, 1, 33)' ?

thank you very much

 

[Ron Rosenfeld]

Second, and i understand that this could be a tall order, for sf4, which
converts ranges to read 'max(5:33)', is ti possible to show all the values
within the range such that it reads 'max(5, 22, 1, 33)' ?

As I wrote previously, I do not like using your #2 or #4 variants as the values
could be confused for range references (whole rows).

But here is a UDF that should resolve range references into a comma separated
list of values displayed in the cell (using the .Text property).

I ran two loops -- one recognizing and converting the single cell references;
and then a second loop which replaces the range references with a comma
separated list.

Some thing for you to be aware of that may or may not be an issue.

1. The .Text property can only return a maximum of 1024 characters from the
cell. If your cells might contain longer data, you will need to use the .Value
property.

2. If the target cell (the one reference by rg in the function) is returning
an error, changes in the references in that target cell will not be reflected
in this function unless you force a recalculation.

Let me know if this does what you need.

=================================================
Option Explicit
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
Dim c As Range, rg2 As Range
Dim t() As String
Dim i As Long

'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)).Text
str = re.Replace(str, sRepl)
Loop

'This pattern recognizes ONLY range references
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:\$?\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)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c.Text
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
ShowFV = str
End Function

========================================================
--ron

 

[lawson]

again, thank you very much, it works perfectly.

for the options that return numbers in a referenced range, i now get:

sf(d4) --> MAX(N62:N$66)+N$65
sf1(d4) --> MAX(N62:N$66)+3.14159265358979
sf2(d4) --> MAX(N62:N$66)+3.142
sf3(d4) --> MAX(0.999999, 4, 6.2831852, 3.14159265358979, 0.222222)+3.142
sf4(d4) --> MAX(1, 4, 6.283, 3.142, 0.222)+3.142

Excel should include these options in their functions...sf1 to 4 could be a
sf(d4,1) to ,4) type of function. i dare not ask you to create this option
for me, you've already done too much!

Second, and i understand that this could be a tall order, for sf4, which
converts ranges to read 'max(5:33)', is ti possible to show all the values
within the range such that it reads 'max(5, 22, 1, 33)' ?

As I wrote previously, I do not like using your #2 or #4 variants as the values
could be confused for range references (whole rows).

But here is a UDF that should resolve range references into a comma separated
list of values displayed in the cell (using the .Text property).

I ran two loops -- one recognizing and converting the single cell references;
and then a second loop which replaces the range references with a comma
separated list.

Some thing for you to be aware of that may or may not be an issue.

1. The .Text property can only return a maximum of 1024 characters from the
cell. If your cells might contain longer data, you will need to use the .Value
property.

2. If the target cell (the one reference by rg in the function) is returning
an error, changes in the references in that target cell will not be reflected
in this function unless you force a recalculation.

Let me know if this does what you need.

=================================================
Option Explicit
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
Dim c As Range, rg2 As Range
Dim t() As String
Dim i As Long

'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)).Text
str = re.Replace(str, sRepl)
Loop

'This pattern recognizes ONLY range references
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:\$?\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)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c.Text
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
ShowFV = str
End Function

========================================================
--ron

 

[Ron Rosenfeld]

again, thank you very much, it works perfectly.

for the options that return numbers in a referenced range, i now get:

sf(d4) --> MAX(N62:N$66)+N$65
sf1(d4) --> MAX(N62:N$66)+3.14159265358979
sf2(d4) --> MAX(N62:N$66)+3.142
sf3(d4) --> MAX(0.999999, 4, 6.2831852, 3.14159265358979, 0.222222)+3.142
sf4(d4) --> MAX(1, 4, 6.283, 3.142, 0.222)+3.142

Excel should include these options in their functions...sf1 to 4 could be a
sf(d4,1) to ,4) type of function. i dare not ask you to create this option
for me, you've already done too much!

Glad it works for you!

Relatively simple to set up your option.

You already have your four formulas.

Just set up something like this:

Function ShowFV(rg As Range, Optional N As Long = 1)
Select Case N
Case Is = 1
ShowFV = ShowFV1(rg)
.....

Case Else
ShowFV = CVErr(xlErrNum)
End Select

End Function
--ron

 

[lawson]

ok, i combined them all into a case by case function, reversed the order such
that my old sf4 is now case 1 and subsequently the default, and this is what
i have come up with...any suggestions for optimization or anything else?

Function SF(rg As Range, Optional N As Long = 1)

Dim str As String
Dim re As Object, mc As Object
Dim sRepl As String
Dim c As Range, rg2 As Range
Dim t() As String
Dim i As Long

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

Select Case N

Case Is = 1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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)).Text
str = re.Replace(str, sRepl)
Loop

'This pattern recognizes ONLY range references
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:\$?\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)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c.Text 'The .Text property returns the decimal places
from the cell referenced, and can only return a maximum of 1024 characters
from the cell. If your cells might contain longer data, you will need to use
the .Value property.
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
SF = str

Case Is = 2
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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)).Text
str = re.Replace(str, sRepl)
Loop

'This pattern recognizes ONLY range references
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:\$?\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)
Set rg2 = Range(mc(0))
ReDim t(rg2.Count - 1)
i = 0
For Each c In rg2
t(i) = c
i = i + 1
Next c
sRepl = Join(t, ", ")
str = re.Replace(str, sRepl)
Loop
SF = str

Case Is = 3
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

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(?!"
'should 0-2 be 0-3?
Do Until re.test(str) = False
Set mc = re.Execute(str)
sRepl = mc(0).submatches(0) & Range(mc(0).submatches(1)).Text 'The
..Text property returns the decimal places from the cell referenced, and can
only return a maximum of 1024 characters from the cell. If your cells might
contain longer data, you will need to use the .Value property.
str = re.Replace(str, sRepl)
Loop
SF = str

Case Is = 4
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


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
SF = str

Case Is = 5
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

SF = rg.Formula

Case Else

SF = CVErr(xlErrNum)

End Select

End Function