Sleeper: Cell Annotation - Formula to viewable updating text

[MrRhodes2004]

In creating engineering calculations, it is very difficult to check a printed spreadsheet. It appears as a little black box. I am trying to figure out a way to show the formula of the cell to help the checker but cell references are of not much help. I would like to create a function that a general excel user can use. For example:

Given:

A1=10
A2=22.5
A3=(A1+A2)/(A2*pi())

The function or sub would ask the user to select a cell to convert, A3. Then it would ask them for the destination cell, A4. Once placed, A4 would update as the referring cells are changed.


The PRINTED result in A4 would be:

"=(10 + 22.5)/(22.5 * pi()) = 0.45978"

If Cell A1 was changed to equal 12, the updated result would be:

"=(12 + 22.5)/(22.5 * pi()) = 0.48807"


This will help the checker be able to see the formula of the referenced cell, in its entire structure. The cell A4 would show the the formula in written format. I have done this by hand by is like this:
="( "&A1&" + "&A2&" )/( "&A2&" * pi()) = " &A3

Thanks

[Bob Phillips]

In creating engineering calculations, it is very difficult to check a printed spreadsheet. It appears as a little black box. I am trying to figure out a way to show the formula of the cell to help the checker but cell references are of not much help. I would like to create a function that a general excel user can use. For example:

Given:

A1=10
A2=22.5
A3=(A1+A2)/(A2*pi())

The function or sub would ask the user to select a cell to convert, A3. Then it would ask them for the destination cell, A4. Once placed, A4 would update as the referring cells are changed.

The result in A4 would be:

=(10 + 22.5)/(22.5 * pi()) = 0.45978

If Cell A1 was changed to equal 12, the updated result would be:

=(12 + 22.5)/(22.5 * pi()) = 0.4880

Select the formula in the formula bar, and hit F9.

[avkb03]

This should do the trick...

Sub ShowCalc()
    'Get values from A1 & A2 & A3
    valA1 = Range("A1").Value
    valA2 = Range("A2").Value
    valA3 = Range("A3").Value
'Get Formula from A3
    frmA3 = Range("A3").Formula
'Replace values in the formula with actual values
    frmA3 = Replace(frmA3, "A1", valA1)
    frmA3 = Replace(frmA3, "A2", valA2)
'Write A4
    Range("A4").Value = "'" & frmA3 & "=" & valA3
End Sub