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



"Bernie Deitrick" wrote:

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



"BoneR" wrote in message
...
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.

"Bernie Deitrick" wrote:

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

"BoneR" wrote in message
...
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.