Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

You might like to try this user-defined function:

Function eval(func As String)
Application.Volatile
eval = Evaluate(func)
End Function

It takes a text string which represents an Excel formula (without the
equals sign) and evaluates it as if it were a formula. Use it like:

=eval(A1)

if A1 contains your string.

Hope this helps.

Pete


On Dec 20, 3:01 pm, lawson wrote:
Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)

If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

On Thu, 20 Dec 2007 07:01:00 -0800, lawson
wrote:

Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

A simple VBA UDF is needed:


============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
===============================
--ron

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?

"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:01:00 -0800, lawson
wrote:

Hello, as an engineer, I often have to show a formula from a book in one
cell, show the formula with my values in it in the next cell, then show the
calculated value in the third. Creating the second cell is a real pain and
very time consuming. Is there an excel function that will read the formula in
a cell and turn it to text? see example below:

vc = (2+y)*SQRT(f'c)
(2+1.82)*SQRT(5*1000)
270.04

Typed: (2+y)*SQRT(f'c)
Typed: ="(2+"&FIXED(D31)&")*SQRT("&D8&"*1000)"
Typed: =(2+D31)*SQRT(D8*1000)


If the third cell were able to read the text in the second, and calculate
the result without the need to retype the formula (or visa versa), a lot of
time could be saved, especially for a more complicated calculation.

A simple VBA UDF is needed:


============================================
Function ShowFormula(rg As Range) As String
ShowFormula = rg.Formula
End Function
===============================
--ron

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

fantastic, thank you very much. this tool should be a standard funtion of
Excel!

"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

is there a way to have the result show the value in the referencd cells
rather than the reference to them? ie:

rather than showing:
=ROUNDDOWN((D31-M83*2-I18*2-I16)/I19,0)+1

it would show:
=ROUNDDOWN((500-33*2-2.4*2-22)/666,0)+1


"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 07:47:02 -0800, lawson
wrote:

can either of you show me an example of how to use these functions and what
it will produce? do i out this VBA in the workbook section and type
=showformula(d34) or =eval(d34) in a cell or what?


To enter the UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the Project Explorer Window, then Insert/Module and paste the
code into the window that opens.

To use the formula, enter =showformula(d34) into some cell (e.g. E34)


Results (taken from a current worksheet of mine:

A1 contains and displays:
C:\Documents and Settings\Local Settings\My Local
Data\Graduation\campusmap.pdf


D34 contains:
=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),255)

D34 displays: campusmap.pdf

E34 contains: =showformula(D34)

E34 displays:

=MID(A1,1+FIND(CHAR(1),SUBSTITUTE(A1,""\"",CHAR(1) ,
LEN(A1)-LEN(SUBSTITUTE(A1,""\"","""")))),255)

--ron

On Thu, 20 Dec 2007 09:23:00 -0800, lawson
wrote:

is there a way to have the result show the value in the referencd cells
rather than the reference to them? ie:

rather than showing:
=ROUNDDOWN((D31-M83*2-I18*2-I16)/I19,0)+1

it would show:
=ROUNDDOWN((500-33*2-2.4*2-22)/666,0)+1



Yes, it can be done.

In the UDF, after extracting the formula, you would step through the formula
and, with anything that looks like a cell reference, substitute the
cell_ref.value (or cell_ref.text, if you want it formatted), for that
reference.

In addition to looking for cell references, you would also have to look for
Names that refer to values or cell references, and resolve them.

You would also have to take into account the differences between XL2003 and
2007.

Do you really need it?
--ron

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.



"Ron Rosenfeld" wrote:

On Thu, 20 Dec 2007 09:23:00 -0800, lawson
wrote:

is there a way to have the result show the value in the referencd cells
rather than the reference to them? ie:

rather than showing:
=ROUNDDOWN((D31-M83*2-I18*2-I16)/I19,0)+1

it would show:
=ROUNDDOWN((500-33*2-2.4*2-22)/666,0)+1



Yes, it can be done.

In the UDF, after extracting the formula, you would step through the formula
and, with anything that looks like a cell reference, substitute the
cell_ref.value (or cell_ref.text, if you want it formatted), for that
reference.

In addition to looking for cell references, you would also have to look for
Names that refer to values or cell references, and resolve them.

You would also have to take into account the differences between XL2003 and
2007.

Do you really need it?
--ron

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

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" wrote:

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

On Thu, 3 Jan 2008 08:57:00 -0800, lawson
wrote:

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

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!

"Ron Rosenfeld" wrote:

On Thu, 3 Jan 2008 08:57:00 -0800, lawson
wrote:

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

On Thu, 3 Jan 2008 12:36:00 -0800, lawson
wrote:

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

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