A function to Turn Formula into Text?

[lawson]

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.

 

[Pete_UK]

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

 

[Ron Rosenfeld]

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

 

[lawson]

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]

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

 

[lawson]

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

 

[lawson]

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]

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

 

[lawson]

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]

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

 

[Ron Rosenfeld]

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

 

[lawson]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[Ron Rosenfeld]

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

 

[lawson]

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.

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

 

[Ron Rosenfeld]

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

 

[lawson]

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

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

 

[Ron Rosenfeld]

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

 

[lawson]

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