Navigation

More options

Style variation

Close Menu

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to display formulas in the worksheet dynamically

Engineering spreadsheets

How to display formulas in the worksheet dynamically

by yakpol Posted Jun 27, 2001 (Edited Jan 14, 2008)

Task 1:
You want the formula behind a certain cell value to be constantly displayed in the other cell.
To do so, write a custom function in VBA as follows:

Code:

Public Function DisplayFormula(rng As Range) As String
    Displayformula = rng.Formula
End Function

Place this function in the worksheet using the interested cell as an argument

Code:

=DisplayFormula(A1)

.

Task 2:
You want to write a formula as a label, being visible and editable, and to display the result of this formula in the other cell.

Go back to VBA and write another short function:

Code:

Public Function EvaluateFormula(rng As Range) As Double
   EvaluateFormula = Application.Evaluate("=" & rng.FORMULA)
End Function

Insert this function into the spreadsheet using cell with the formula as an argument. Note that the function above requires no '=' sign before or after the formula. To relax the syntax modify function in the following way:

Code:

Public Function EvaluateFormula(rng As Range) As Double
Dim str As String
str = Trim(rng.FORMULA)
If Left(str, 1) <> "=" Then str = "=" & str
If Right(str, 1) = "=" Then str = Left(str, Len(str) - 1)
    EvaluateFormula = Application.Evaluate(str)
End Function

Now you can type the formula you want to evaluate with heading and trailing '=' sign, like:

Code:

'= A1+A2 =

Display values behind formula.
Download excellent VBA function disfor() from http://www.geocities.com/SiliconValley/Hills/1829/VBA/index.html The formula will look like actual handwriting, e.g. (5+6.3)/3.

Displaying cell formulae as two-dimensional mathematical equations.
Download another excellent free excel add-in from http://www.excelcalcs.com/index.php

Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back

Top