Display excel formula values

[upquark1]

how can I display Excel 2007 cell formulas showing input values as they would
be written on paper or solved on a hand calculator (for educational
purposes), not final value and not cell references which are straightforward?
I found VBA code from 2005 which may solve it but I don't know how to get
this code to work as a macro in Excel and Excel doesn't appear to have any
built-in functions to display input values.

 

[ryguy7272]

http://www.anthony-vba.kefra.com/vba/vbabasic1.htm

http://www.rondebruin.nl/code.htm

http://www.contextures.com/xlvba01.html

 

[trip_to_tokyo]

First suggestion (in EXCEL 2007):-

1. In cell A 1 I have:-

1

2. In cell B 1 I have:-

1

3. In cell C 1 I have:-

=(A1+B1)

The result of the above is, of course, showing 3.

4. Click in cell E 4 (for example) then:-

- hit the F 5 key

- click on Special . . . in the lower left hand corner

- Go To Special should launch

- select Formulas then OK

5. The cursor should now move to cell C 1 (the one with the formula in it).

You can apply the above to any Workbook and it will highlight all the
formulas that are in that Workbook.

I think that this is not quite what you want but see my second suggestion
below.

If my comments have helped please hit Yes.

Thanks.

 

[trip_to_tokyo]

Second suggestion (in EXCEL 2007):-

1. In cell A 1 I have:-

1

2. In cell B 1 I have:-

1

3. In cell C 1 I have:-

=(A1+B1)

The result of the above is, of course, showing 3.

4. Click in cell C 1 then:-

- Ctrl-C to copy

- Home group / Paste / Paste Values

5. The formula in cell C 1 will now be replaced with the value:-

3

If my comments have helped please hit Yes.

Thanks.

 

[Gord Dibben]

Copy/paste this UDF to a general module in your workbook.

Ryan has posted a few sites explaining how to install macros and code.

Function EvalCell(RefCell As String)
Application.Volatile
EvalCell = Evaluate(RefCell)
End Function

You could enter your calculations in a cell without the = sign as text
only.

e.g. 1 + 1 in A1 or 2 * 3 in A2

In an adjacent cell enter this =evalcell(A1) to return the actual value
of 1 + 1


Gord Dibben MS Excel MVP