Wrong data type returned from user-defined function

[hooroy63]

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
Comments:

1 =ROUND(66.55,0) =66.55
text -- s/b a number
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 -- 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

 

[Charles Williams]

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

 

[Roy Harrill]

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


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

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

 

[Charles Williams]

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]

Hi Charles,

I'm finally getting back to this. Thanks very much for all your help. I was
aware that functions could return only values, but I was hoping a function
could return to the cell a string value that was a formula that would
execute just as if I'd typed it in the cell myself. Since that can't be
done, I'll just have to live with what we've done already. It was
interesting to learn that a comment could be passed back from the function
to the calling cell. However, I decided to de-activate that feature as it
really doesn't accomplish what I wanted.

The latest code is below. I made a few changes, hopefully improvements. Some
are editorial. The main substantive change is to add code that handles
ROUNDDOWN and ROUNDUP as well as ROUND. I also added "Application.Volatile
True" in order to force a recalc after the function executes, but I'm not
entirely certain it's needed in this case. And I changed the "IsEmpty" code
to return a blank instead of a zero when the source cell is blank. In the
main If-then wrapper, I changed your 7 to, in essence, 11 because that's the
minimum length any rounding formula can be, i.e., "=ROUND(#,#)". Your 7
presumably was based on "=ROUND(".

I'd be interested in any further comments you may have, especially if I've
managed to foul anything up. Then, what would you say to my posting the
final version as a new post to share with others (perhaps titled "UnRound
Function"), with due attribution to you for your valuable contribution?

Roy


Option Explicit

Public Function UnRound(rngCell As Range) As Variant

Dim strFormula As String
Dim strStart As String
Dim strRoundType As String

Application.Volatile True

If IsEmpty(rngCell) Then UnRound = "": Exit Function

UnRound = rngCell.Value

strFormula = rngCell.Formula
If Len(strFormula) >= Len("=ROUND(#,#)") 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

strRoundType = "ROUND"
If Left(strFormula, 7) = "ROUNDUP" Then _
strRoundType = "ROUNDUP"
If Left(strFormula, 9) = "ROUNDDOWN" Then _
strRoundType = "ROUNDDOWN"

strFormula = Right(strFormula, _
Len(strFormula) - (Len(strRoundType) + 1))
strFormula = strStart & Left(strFormula, _
Len(strFormula) - InStr(StrReverse(strFormula), ","))

UnRound = rngCell.Parent.Evaluate(strFormula)

'NOTE: Un-REM next 6 code lines if cell comment desired
'If rngCell.HasArray Then
' strFormula = "{" & strFormula & "}"
'End If

'On Error Resume Next
'Application.Caller.Comment.Delete
'Application.Caller.AddComment strFormula

End If

End If

End Function

--------------------------------------------------------------------

 

[Charles Williams]

Hi Roy,

I have not tested your revised function, but it looks fine.

I would remove the Application.Volatile since it is not needed.


regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

 

[hooroy63]

Charles,
UnRound now seems to work OK for everything except when it is used IN an
array formula, such as {=SUM(UnRound(C4:C5))}, which should produce the sum
of unrounded C4 plus unrounded C5. I really don't understand why this
doesn't work because the non-array equivalent,
=SUM(UnRound(C4),UnRound(C5)), works fine. Do I need another Evaluate
somewhere?
Roy

 

[Charles Williams]

If you want it to function as an array function then it needs to be
rewritten so that it iterates over the input cells and returns an array:

Public Function UnRound(theCell As Range) As Variant
Dim strFormula As String
Dim strStart As String
Dim vAnsa() As Variant
Dim j As Long
Dim k As Long

If IsEmpty(theCell) Then Exit Function
On Error GoTo FuncFail

ReDim vAnsa(1 To theCell.Rows.Count, 1 To theCell.Columns.Count)

For k = 1 To theCell.Columns.Count
For j = 1 To theCell.Rows.Count

strFormula = theCell.Cells(j, k).Formula
vAnsa(j, k) = theCell.Cells(j, k).Value2

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)
vAnsa(j, k) = theCell.Parent.Evaluate(strFormula)

End If
End If
Next j
Next k

UnRound = vAnsa
Exit Function
FuncFail:
UnRound = CVErr(xlErrNA)
End Function

Note that this version is designed to handle small input arrays and would
need some changes to handle large arrays efficiently.

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

 

[hooroy63]

Charles,

That works as long as the input cells are in the same column, but not if
they're not, such as in:
{=SUM(unround(C2530))} or {=SUM(unround(C25:C27, D30))}
Would a "For Each IndivCell in theCell" approach work any better?
Also, for my education, what in your code accumulates the totalthat the
function returns? I'm sure it has to do w/ the vAnsa at the end, but I'm
confused by that.

If youre getting tired of this, just let me know and we'll call it a day.

Regards,
Roy

 

[Charles Williams]

=SUM(unround(C2530)) Works fine for me (you dont need to make this an
array formula, but it still works if you do make it an array formula).

{=SUM(unround(C25:C27, D30))} This will not work since Unround only takes
one argument, not two.

Nothing in my code accumulates the answer, it returns an array of results
the same shape as the input range so that you can use it inside pretty much
any function that can handle a range.

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com