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

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