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




"BoneR" wrote:

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.