Hi Roy,
yup, forgot to add the minus sign back in.
Functions cannot return Formulae, they can only return values.
You can add comments from within a function so try this version which adds
the formula as a comment
This function works OK for me with array formulae because Evaluate treats
formulae as array formulae where appropriate.
Option Explicit
Public Function UnRound(theCell As Range) As Variant
Dim strFormula As String
Dim strStart As String
If IsEmpty(theCell) Then Exit Function
strFormula = theCell.Formula
UnRound = theCell
If Len(strFormula) = 7 Then
If Left(strFormula, 6) = "=ROUND" _
Or Left(strFormula, 7) = "=-ROUND" Then
strStart = "="
strFormula = Right(strFormula, Len(strFormula) - 1)
If Left(strFormula, 1) = "-" Then
strFormula = Right(strFormula, Len(strFormula) - 1)
strStart = "=-"
End If
strFormula = Right(strFormula, Len(strFormula) - 6)
strFormula = strStart & Left(strFormula, Len(strFormula) - 3)
UnRound = theCell.Parent.Evaluate(strFormula)
If theCell.HasArray Then
strFormula = "{" & strFormula & "}"
End If
On Error Resume Next
Application.Caller.Comment.Delete
Application.Caller.AddComment strFormula
End If
End If
End Function
regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com
"Roy Harrill" wrote in message
...
Charles, thanks for your reply, and particularly for educating me on
object.Parent.Evaluate(arg)" Your concise code works well except
that it doesn't pick up the minus signs (that s/b easy to fix), and it
won't work if "theCell" contains an array formula.
Also, and maybe I was unclear on this, your function evaluates
strFormula and returns the result, which isn't exactly what I'm
looking for. For instance, in my second example below, where
the source cell (A2) shows "=-ROUND($E$8,0)", I want B2
to contain the same formula as A2 except without the rounding
function. Thus, B2 should end up with "=-$E$8" (which should
show in the formula box at the top) and display (in the cell)
whatever number E8 shows. Same with my Ex. 3; B3 should
contain an unrounded formula, "=-SUM(Sales)", and display
the total of the range named Sales. In every case, in col. B,
"UnRound()" goes away and is replaced by whatever is in its
corresponding col. A cell, except sans the rounding wrapper.
That's where I'm having the problem -- my code properly
replaces the "UnRound()" in col. B, but the replacement comes
through as plain "dead" text rather than an active formula.
As to the array formula (my Ex. 4), it's quite a challenge because
I'm told that, whereas a sub proc. can write to a sheet (such as:
"If theCell.HasArray Then theCell.FormulaArray = strFormula")",
a function cannot do that.
Sorry to be long-winded, but I wanted to be sure to explain my plight
more clearly this time. Again, thanks for your help.
Roy
"Charles Williams" wrote in message
...
Try something like this:
Option Explicit
Public Function UnRound(theCell As Variant) As Variant
Dim strFormula As String
If IsEmpty(theCell) Then Exit Function
strFormula = theCell.Formula
If Left(strFormula, 6) = "=ROUND" Or Left(strFormula, 7) = "=-ROUND"
Then
If Left(strFormula, 2) = "=-" Then strFormula = "=" & _
Right(strFormula, Len(strFormula) - 2)
strFormula = Right(strFormula, Len(strFormula) - 7)
strFormula = "=" & Left(strFormula, Len(strFormula) - 3)
UnRound = theCell.Parent.Evaluate(strFormula)
Else
UnRound = theCell
End If
End Function
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com
"hooroy63" wrote in message
...
Hi All -
I'd appreciate help in writing a function that strips a ROUND "wrapper"
from a formula in another cell. For example, assume cell A3 has a formula
such as =-ROUND(SUM(sales),0) that displays 66.55. I want the put a
function in, say, B3 that is =UnRoundCell(A3), which should display in B3
the unrounded sum of the range named Sales. Below is a simulated sheet
with several other troublesome examples. The problem is that I don't know
how to get the function to return a formula to the calling cell. In every
case it returns a "dead" text data type in column B rather than an active
formula that produces the proper result.
A B
1 =ROUND(66.55,0) =66.55
text, but s/b
anumber
2 =-ROUND($E$8,0) =-$E$8
text -- s/b a
formula that returns
the contents of
cell E8
3 =-ROUND(SUM(Sales),0) =-SUM(Sales)
text -- s/b a
formula that returns
a number (sum of
Sales range)
4 {=ROUND(SUM($B$6:$B$7),0)} =SUM($B$6:$B$7)
array formula in
A, but B is
plain non-array
text -- B s/b an
array formula that
returns a sum
Below is the code I've written so far. What have I done wrong? TIA for
your help.
Function UnRoundCell(Cell) As Variant
Dim CellContents As Variant, neg As Boolean
Application.Volatile
CellContents = Cell.Formula
'if formula in cell isn't wrapped by a rounding formula,
' then use existing cell contents unchanged
If Not Left(CellContents, 6) = "=ROUND" And _
Not Left(CellContents, 7) = "=-ROUND" Then
UnRoundCell = CellContents
Exit Function
End If
'determine negativity
neg = False
If Left(CellContents, 1) = "-" Or Left(CellContents, 2) = "=-" Then neg
= True
'peel rounding prefix and suffix
If CellContents Like "=-*" Then
CellContents = Right(CellContents, Len(CellContents) - 2 -
Len("Round("))
ElseIf CellContents Like "=*" Then
CellContents = Right(CellContents, Len(CellContents) - 1 -
Len("Round("))
Else
CellContents = Right(CellContents, Len(CellContents) - 0 -
Len("Round("))
End If
CellContents = Left(CellContents, Len(CellContents) - 3)
'add leading sign
If neg Then
CellContents = "=-" & CellContents
Else
CellContents = "=" & CellContents
End If
UnRoundCell = CellContents
End Function