Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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 "Roy Harrill" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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" wrote in message ... 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 "Roy Harrill" wrote in message ... 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" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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" wrote in message ... 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" wrote in message ... 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 "Roy Harrill" wrote in message ... 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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
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(C25:D30))} 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 "Roy Harrill" wrote in message ... 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong data type returned from user-defined function
=SUM(unround(C25:D30)) 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 "hooroy63" wrote in message ... 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(C25:D30))} 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 "Roy Harrill" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dictionary object: Error assigning user defined data type to item | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
User defined data type | Excel Programming | |||
Passing an Array of User-Defined Type to an Argument of a Function | Excel Programming |