Can Excel represent formula in textural format with values substi.

B

BoneR

In Excel I would like to display the values that go into a formula - in
addition to the answer itself. This is to mimic the way hand calculations
would be set out on paper. In other words I'd like a function that takes the
maths expression within a cell and returns with a string where the cell
references have been replaced with actual values.
 
B

Bernie Deitrick

To show addition of the values in A1 and A2, use the formula

= A1 & " + " & A2 & " = " & A1 + A2

Of course, you might want to have a check that A1 and A2 are filled, but you
get the idea.

HTH,
Bernie
MS Excel MVP
 
B

BoneR

Thanks Bernie, I have used string concatination in the past but when the
expressions get long and complex it becomes very tedious. What I had in mind
was a more general function that actually looked at the cell containing the
maths expression directly.
 
B

Bernie Deitrick

Then you need to use a function. Copy the code below into a module in your
workbook, and use it like

=ShowFormula(A1)

HTH,
Bernie
MS Excel MVP

Function ShowFormula(inCell As Range) As String
ShowFormula = Mid(inCell.Formula, 2, _
Len(inCell.Formula)) & " = " & inCell.Value
End Function
 
R

robert.bone

Thanks again. That was useful and in the meantime I'd been testing out
something similar - but now the tricky bit. Or maybe not so difficult - I'm
not sure! The next step would be to replace the cell references shown in
the formula with the actual values. I came across a routine to do that but
it made calls to functions that are not within VBA, so is of limited use to
most people using standard Excel

I thought one approach would be to parse the formula string and look for
cell references then use INDIRECT to return with the cell value. In pseudo
code it might look like this:

flen = len(formula_in) ; get character count of formula
expression
for I = 1 to flen
nextchar = mid(formula_in, I, 1) ; take one character at a time
for J = 1 to delim ; delim is number of delimiters
; delim is a string array holding delimiter characters such as ( ) + -
* / ^ &
if nextchar = delim(J) then
; just copy and add character on to end of output string
formula_out = formula_out & nextchar
J = delim ; ugly, but crash out of loop
else
; otherwise build up a text item that may or may not be a cell
reference
nextitem = nextitem & nextchar
J = delim
endif
next J

etc, etc
This is getting messy, but the idea is that the next cell references or
function name will be built up in nextitem.
Thereafter, nextitem can then be checked to see if it is a cell reference
ISREF() and if so INDIRECT() would be used to get the value, TEXT() would
convert it to a string and it could be added onto the end of formula_out. If
nextitem turns out not to be a cell reference eg say it is SQRT then it is
simply concatenated onto formula_out.

I'm afraid my programming skills are rather rusty and VBA was never my
strong point, by if anyone out there would like to develop something along
the above lines I (and judging from discussion grps) many others would be
delighted. In fact I think this should be a standards Excel function, but....
 
D

Dana DeLouis

This is not the perfect solution, and won't work with a formula like
=Sum(A1:A5), but maybe there are some ideas here:
This demo looks at C1, and puts an adjusted formula in D1.
Again, this is not complete, but may give you some ideas:

Sub Demo()
Dim S As String
Dim rngArea
Dim Cell

'// Set up
[A1] = 2
[A2].Formula = "=8/2"
[A4].Formula = "=Pi()"
[A5].Formula = "=Sin(Pi()/4)"

[C1].Formula = "=A1+$A$2+A$4+$A5"

S = Range("C1").Formula

For Each rngArea In Range("C1").DirectPrecedents.Areas
For Each Cell In rngArea.Cells
S = Replace(S, Cell.Address(True, True), Cell.Value)
S = Replace(S, Cell.Address(True, False), Cell.Value)
S = Replace(S, Cell.Address(False, True), Cell.Value)
S = Replace(S, Cell.Address(False, False), Cell.Value)
Next Cell
Next rngArea
Range("D1").Formula = S
End Sub
 
B

Bernie Deitrick

Robert,

Messy is right - so I think I will bow out of this thread, after making this
one observation:

When you parse the cell addresses, there is no need to use INDIRECT. Let's
say that you've found the string A11 as the address, and you've assigned it
to the variable "nextitem". When you're sure you have all that you need,
you could simply use

Range(nextitem).Value

to extract cell A11's value.

Bernie
MS Excel MVP
 
R

robert.bone

Thanks for thoughts and observations - I think I've come up with a function
that sets out the 'arithimentic' of an Excel function. Here is an example of
what I mean and I'm pleased to say that it is in nothing more exotic than
VBA. The code follows the illustrations.

Feedback would be welcome.

Some values for an expression
A = 23.6 Cats = 4
B = 1066 Mice = 56
g = 9.81 Cheese = 0.75

Test Expression 1 Test simple arithmetric and vary spaces between
variables
Expression as cell references =C5 + 10 / F5
Expression as values =23.6 + 10 / 4
Evaluated result 26.10

Test Expression 2 Test a function (COS) and locked cell reference $
Expression as cell references =C5*COS(C7) + $C$6
Expression as values =23.6*COS(9.81) + 1066
Evaluated result 1044.13

Test Expression 3 Test a longer more complex expression and nested brackets
Expression as cell references =((C5+C6) / INT(C$7)) / SQRT(F6/F5)
Expression as values =((23.6+1066) / INT(9.81)) / SQRT(56/4)
Evaluated result 32.35642769

Test Expression 4 Test with a function that has null argument eg TODAY()
Expression as cell references =TODAY() + F6
Expression as values =TODAY() + 56
Evaluated result 26/05/2005

Test Expression 5 Test very odd conditional expression with commas even
Expression as cell references =IF(F7<(LN(F6/F5)+C7), (C5*F5/C6),
(INT(C7+F6)))
Expression as values =IF(0.75<(LN(56/4)+9.81), (23.6*4/1066), (INT(9.81+56)))
Evaluated result 0.088555347

The Excel Function

Function DispEqn(Cell As Range) As String
'
' Developed by Robert Bone 31/03/2005
'
Dim DelimChar(15) As String ' Array to hold delimiter characters
Dim DelimList As String ' String to set up delimiters
Dim NextWord As String ' String to hold 'next' part of expression
Dim List_Len As Integer ' Number of characters in delimiter list
Dim Formula_Len As Integer ' Number of characters in expression
'
' Set up delimiter list array
DelimList = "() +-/*=<>^," ' Possibly not most efficient order
List_Len = Len(DelimList)
For I = 1 To List_Len
DelimChar(I) = Mid(DelimList, I, 1)
Next I
'
' Ensure a tidy start
Formula_In = Trim(Cell.Formula) & " " ' Put on an end stop!
Formula_Len = Len(Formula_In)
Formula_Out = ""
NextWord = ""
NextValue = ""
'
' Examine each character in formula string
For I = 1 To Formula_Len
NextChar = Mid(Formula_In, I, 1)
'
' Check character against delimiter set
Delim = False
For J = 1 To List_Len
If NextChar = DelimChar(J) Then
Delim = True
End If
Next J
'
' If delimiter found then check if it marks the end of a 'word'
If Delim Then
If NextWord <> "" Then
'
' Try and use NextWord as a cell reference, else just append
On Error Resume Next
NextValue = Range(NextWord).Value
If Err.Number = 0 Then
Formula_Out = Formula_Out & NextValue & NextChar
Else
Formula_Out = Formula_Out & NextWord & NextChar
End If
Err.Clear ' Clear error flags for next loop
NextWord = "" ' Clear 'word' for next cycle
'
' No NextWord to check so just append NextChar
Else
Formula_Out = Formula_Out & NextChar
End If
'
' Nothing special found so just build NextWord
Else
NextWord = NextWord & NextChar
End If
Next I

DispEqn = Formula_Out
End Function
 
Top