A function to Turn Formula into Text?

On Thu, 20 Dec 2007 10:02:03 -0800, lawson
wrote:

i was hoping it would be a simple modification to the VBA you already posted
for me.

i am in fact using excel 2003. could you please show me the code that would
execute this task?

thank you very much.



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)



==========================================
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 only return the contents
'of the First and Last cells in the range.
'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
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

thank you very much for taking the time to do this for me, it works
beautifully. there is jsut one problem in that if the formula has a cell
reference with a '$' in it (ie. $D$4 or D$4), it does not recognize the cell
reference. is there a quick fix to this?

"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 10:02:03 -0800, lawson
wrote:

i was hoping it would be a simple modification to the VBA you already posted
for me.

I'm not aware of any "simple" solution to this problem.


i am in fact using excel 2003. could you please show me the code that would
execute this task?

thank you very much.

The attached is a partial solution. Read the comments in the text to review
its limitations. See if it will do what you need:

======================================
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 only return the contents
'of the First and Last cells in the range.
'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
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 = Range(mc(0))
str = re.Replace(str, sRepl)
Loop
ShowFV = str
End Function
=========================================
--ron

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

On Fri, 21 Dec 2007 05:59:00 -0800, lawson
wrote:

thank you very much for taking the time to do this for me, it works
beautifully. there is jsut one problem in that if the formula has a cell
reference with a '$' in it (ie. $D$4 or D$4), it does not recognize the cell
reference. is there a quick fix to this?

There should be but it'll have to wait.

For now, use this for "pattern" and it should at least allow you to match the
form D$4

It will not match absolute column references, but that should be doable once I
have a chance to sit down and work it out.

([^:$]|^)\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(?!


--ron

On Fri, 21 Dec 2007 05:59:00 -0800, lawson
wrote:

thank you very much for taking the time to do this for me, it works
beautifully. there is jsut one problem in that if the formula has a cell
reference with a '$' in it (ie. $D$4 or D$4), it does not recognize the cell
reference. is there a quick fix to this?


I think this "pattern" should take care of the issue with absolute and mixed
references.

Let me know how it works for you:

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

again, works beautifully, thank you very much.

one more thing (and i know im getting picky)...

is there a way to limit the number of decimal places it shows? currently, if
a cell referenced was not rounded and has decimals like 1/3 would, the
ShowFV(blah) will show it as 0.333333333333333*whatever

i limit of 2 decimal places would be perfect, without rounding the values
beforehand.

"Ron Rosenfeld" wrote:

On Fri, 21 Dec 2007 05:59:00 -0800, lawson
wrote:

thank you very much for taking the time to do this for me, it works
beautifully. there is jsut one problem in that if the formula has a cell
reference with a '$' in it (ie. $D$4 or D$4), it does not recognize the cell
reference. is there a quick fix to this?


I think this "pattern" should take care of the issue with absolute and mixed
references.

Let me know how it works for you:

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

On Fri, 21 Dec 2007 10:51:03 -0800, lawson
wrote:

again, works beautifully, thank you very much.

one more thing (and i know im getting picky)...

is there a way to limit the number of decimal places it shows? currently, if
a cell referenced was not rounded and has decimals like 1/3 would, the
ShowFV(blah) will show it as 0.333333333333333*whatever

i limit of 2 decimal places would be perfect, without rounding the values
beforehand.

I mentioned this earlier. And the fix is relatively simple.

Right now, the routine returns the Value of the cell. As a suggestion, how
about returning the Text property -- this should result in a display in the
function that matches whatever format you have in the original cell. (The
value will not be the same, but the display will be the same).

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

i really appreciate your help, thank you very much.

from the several types of functions you have provided, i have created 3
options:
ShowFV1 - Leaves the format in ranges as 'max(d3:d8)'
ShowFV2 - Converts the format in ranges to 'max(5:33)'
ShowFV3 - Removes Decimals (converts to text) and Leaves the format in
ranges as 'max(d3:d8)'
ShowFV4 Removes Decimals (converts to text) and Converts the format in
ranges to 'max(5:33)'

all this would not be possible without your help, and again i thank you very
much.

Gordon Lawson


"Ron Rosenfeld" wrote:

On Fri, 21 Dec 2007 10:51:03 -0800, lawson
wrote:

again, works beautifully, thank you very much.

one more thing (and i know im getting picky)...

is there a way to limit the number of decimal places it shows? currently, if
a cell referenced was not rounded and has decimals like 1/3 would, the
ShowFV(blah) will show it as 0.333333333333333*whatever

i limit of 2 decimal places would be perfect, without rounding the values
beforehand.

I mentioned this earlier. And the fix is relatively simple.

Right now, the routine returns the Value of the cell. As a suggestion, how
about returning the Text property -- this should result in a display in the
function that matches whatever format you have in the original cell. (The
value will not be the same, but the display will be the same).

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

On Fri, 21 Dec 2007 14:27:01 -0800, lawson
wrote:

i really appreciate your help, thank you very much.

from the several types of functions you have provided, i have created 3
options:
ShowFV1 - Leaves the format in ranges as 'max(d3:d8)'
ShowFV2 - Converts the format in ranges to 'max(5:33)'
ShowFV3 - Removes Decimals (converts to text) and Leaves the format in
ranges as 'max(d3:d8)'
ShowFV4 Removes Decimals (converts to text) and Converts the format in
ranges to 'max(5:33)'

all this would not be possible without your help, and again i thank you very
much.

Gordon Lawson

Gordon,

You're most welcome. Thanks for the feedback.

However, I would suggest that you NOT use the FV2 and FV4 variations without
also having some kind of "warning". The reason being that 5:33 is a valid
reference (it is the same as A5:IV33), and could be confusing.


--ron

Hi Ron,

Happy New Year!

i have 2 new requests for you

First, the sf4 that i have currently is not able to convert cell references
with a $ in them to a number value, whether it is in a range of cells or a
simple single cell reference

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 th erange such that it reads 'max(5, 22, 1, 33)' ?

my sf4 is as follows, and below is the code you gave me that converted the $
to values in non-range references:

--------------
Function SF4(rg As Range) 'ShowFormula - MAX(0.999:0.222)+0.777
'substitutes the contents of cell references for the references when doing
ShowFormula
'does NOT recognize RANGES, so will only return the contents of the First
and Last cells in the range.
'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
SF4 = 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
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 = Range(mc(0)).Text '(the .text will cause it to read the
referenced cell as a text value, which will remove the decimal places)
str = re.Replace(str, sRepl)
Loop
SF4 = str
End Function


------- here is the one that converts d$44 to read 31.123
Function SF2(rg As Range) 'ShowFormula - MAX(L41:L43)+0.777 This one returns
the format (decimal places) from the original cell
'substitutes the contents of cell references for the references when doing
ShowFormula
'does NOT recognize RANGES, so will leave range references unchanged
'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
SF2 = 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 '(the
..text will cause it to read the referenced cell as a text value, which will
remove the decimal places)
str = re.Replace(str, sRepl)
Loop
SF2 = str
End Function