Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as parameter
If I set up a simple spreadsheet with Column A being a defined range named
TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as parameter
In column B you are using Implicit Intersection of the Named Range and the
Row. This does not work for UDFs unless you explicitly code for it. Public Function Test2(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long jThisRow = Application.Caller.Row vv = var(jThisRow, 1).Value vv = vv * 2 Test2 = vv End Function When you pass a range to a UDF as a variant parameter, the variant parameter starts off as a range object contained in a variant, but you can get its values by assigning it to a variant. This conversion from variant containing a range to Variant containing the values from the range may be done by VBA under the covers as an implicit conversion if VBA thinks thats required. If you pass the UDF a calculated parameter INT(TestRange) then its a multi-stage operation: first the INT function does the Implicit Intersection, gets the resulting value and converts it to whole number, then the whole number is converted to a double, then the double is passed to the UDF as a variant containing a double. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as parameter
Calling the function using N(DefinedName) seems to be the best solution I
have come up with. ie. fxVal(N(TestRange)) or fxTimesTwo(N(TestRange))... I'm sure there is a hidden index in the passed DefinedName, but I can not find reference to it. I say this because Microsoft designed functions ie SQRT(TestRange) work fine. Any other help appreciated... "Harold" wrote: If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as paramete
Charles,
For some reason my earlier response to you never got posted... The issue with using Application.Caller.Row or .Address is that it refers to the location of the calling function and not the index of the range. In the example, TestRange's first element is in cell A2. The first function call would be in row 2 which means the call that is suppose to use the first element is using the second element... Now obviously I could fix this by using row-1, but then if I inserted a couple of lines my function would no longer work. Which means I have got to pass another parameter indicating the index of the element... Thanks for responding and any other ideas appreciated. "Charles Williams" wrote: In column B you are using Implicit Intersection of the Named Range and the Row. This does not work for UDFs unless you explicitly code for it. Public Function Test2(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long jThisRow = Application.Caller.Row vv = var(jThisRow, 1).Value vv = vv * 2 Test2 = vv End Function When you pass a range to a UDF as a variant parameter, the variant parameter starts off as a range object contained in a variant, but you can get its values by assigning it to a variant. This conversion from variant containing a range to Variant containing the values from the range may be done by VBA under the covers as an implicit conversion if VBA thinks thats required. If you pass the UDF a calculated parameter INT(TestRange) then its a multi-stage operation: first the INT function does the Implicit Intersection, gets the resulting value and converts it to whole number, then the whole number is converted to a double, then the double is passed to the UDF as a variant containing a double. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as paramete
Harold,
You are right: try this way (in real life you would need error handling etc.) Public Function Test3(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long Dim jStartRow As Long jStartRow = var.Row jThisRow = Application.Caller.Row vv = var(jThisRow - jStartRow + 1, 1).Value vv = vv * 2 Test3 = vv End Function "Harold" wrote in message ... Charles, For some reason my earlier response to you never got posted... The issue with using Application.Caller.Row or .Address is that it refers to the location of the calling function and not the index of the range. In the example, TestRange's first element is in cell A2. The first function call would be in row 2 which means the call that is suppose to use the first element is using the second element... Now obviously I could fix this by using row-1, but then if I inserted a couple of lines my function would no longer work. Which means I have got to pass another parameter indicating the index of the element... Thanks for responding and any other ideas appreciated. "Charles Williams" wrote: In column B you are using Implicit Intersection of the Named Range and the Row. This does not work for UDFs unless you explicitly code for it. Public Function Test2(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long jThisRow = Application.Caller.Row vv = var(jThisRow, 1).Value vv = vv * 2 Test2 = vv End Function When you pass a range to a UDF as a variant parameter, the variant parameter starts off as a range object contained in a variant, but you can get its values by assigning it to a variant. This conversion from variant containing a range to Variant containing the values from the range may be done by VBA under the covers as an implicit conversion if VBA thinks thats required. If you pass the UDF a calculated parameter INT(TestRange) then its a multi-stage operation: first the INT function does the Implicit Intersection, gets the resulting value and converts it to whole number, then the whole number is converted to a double, then the double is passed to the UDF as a variant containing a double. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as paramete
Excellet!!!!
I did not pick up that I could get the address of the passed range. You have made my day Charles, thanks! "Charles Williams" wrote: Harold, You are right: try this way (in real life you would need error handling etc.) Public Function Test3(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long Dim jStartRow As Long jStartRow = var.Row jThisRow = Application.Caller.Row vv = var(jThisRow - jStartRow + 1, 1).Value vv = vv * 2 Test3 = vv End Function "Harold" wrote in message ... Charles, For some reason my earlier response to you never got posted... The issue with using Application.Caller.Row or .Address is that it refers to the location of the calling function and not the index of the range. In the example, TestRange's first element is in cell A2. The first function call would be in row 2 which means the call that is suppose to use the first element is using the second element... Now obviously I could fix this by using row-1, but then if I inserted a couple of lines my function would no longer work. Which means I have got to pass another parameter indicating the index of the element... Thanks for responding and any other ideas appreciated. "Charles Williams" wrote: In column B you are using Implicit Intersection of the Named Range and the Row. This does not work for UDFs unless you explicitly code for it. Public Function Test2(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long jThisRow = Application.Caller.Row vv = var(jThisRow, 1).Value vv = vv * 2 Test2 = vv End Function When you pass a range to a UDF as a variant parameter, the variant parameter starts off as a range object contained in a variant, but you can get its values by assigning it to a variant. This conversion from variant containing a range to Variant containing the values from the range may be done by VBA under the covers as an implicit conversion if VBA thinks thats required. If you pass the UDF a calculated parameter INT(TestRange) then its a multi-stage operation: first the INT function does the Implicit Intersection, gets the resulting value and converts it to whole number, then the whole number is converted to a double, then the double is passed to the UDF as a variant containing a double. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with VBA Functions that use a DefinedRange as paramete
This extended version works the same way as Excel functions with array
formulae, but only handles ranges and Named ranges as input parameters. For true generality it should also handle a wider variety of input parameters such as scalars, array constants and calculated inputs such as your N(TestRange) Public Function Test4(ByRef Var As Variant) As Variant Dim vv As Variant Dim va() As Variant Dim jThisRow As Long Dim jStartRow As Long Dim j As Long Dim nCallerRows As Long If Not Application.Caller.HasArray Then ' ' Implicit Intersect with Row ' jThisRow = Application.Caller.Row jStartRow = Var.Row vv = Var(jThisRow - jStartRow + 1, 1).Value2 vv = vv * 2 Test4 = vv Else ' ' array formula: Implicit Intersect with the Array Formula ' nCallerRows = Application.Caller.Rows.Count ReDim va(1 To nCallerRows, 1 To 1) As Variant vv = Var.Value2 For j = 1 To nCallerRows va(j, 1) = vv(j, 1) * 2 Next j Test4 = va End If End Function regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... Excellet!!!! I did not pick up that I could get the address of the passed range. You have made my day Charles, thanks! "Charles Williams" wrote: Harold, You are right: try this way (in real life you would need error handling etc.) Public Function Test3(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long Dim jStartRow As Long jStartRow = var.Row jThisRow = Application.Caller.Row vv = var(jThisRow - jStartRow + 1, 1).Value vv = vv * 2 Test3 = vv End Function "Harold" wrote in message ... Charles, For some reason my earlier response to you never got posted... The issue with using Application.Caller.Row or .Address is that it refers to the location of the calling function and not the index of the range. In the example, TestRange's first element is in cell A2. The first function call would be in row 2 which means the call that is suppose to use the first element is using the second element... Now obviously I could fix this by using row-1, but then if I inserted a couple of lines my function would no longer work. Which means I have got to pass another parameter indicating the index of the element... Thanks for responding and any other ideas appreciated. "Charles Williams" wrote: In column B you are using Implicit Intersection of the Named Range and the Row. This does not work for UDFs unless you explicitly code for it. Public Function Test2(ByRef var As Variant) As Variant Dim vv As Variant Dim jThisRow As Long jThisRow = Application.Caller.Row vv = var(jThisRow, 1).Value vv = vv * 2 Test2 = vv End Function When you pass a range to a UDF as a variant parameter, the variant parameter starts off as a range object contained in a variant, but you can get its values by assigning it to a variant. This conversion from variant containing a range to Variant containing the values from the range may be done by VBA under the covers as an implicit conversion if VBA thinks thats required. If you pass the UDF a calculated parameter INT(TestRange) then its a multi-stage operation: first the INT function does the Implicit Intersection, gets the resulting value and converts it to whole number, then the whole number is converted to a double, then the double is passed to the UDF as a variant containing a double. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Harold" wrote in message ... If I set up a simple spreadsheet with Column A being a defined range named TestRange. I can then in column B use the formula =TestRange for each cell and get the value that is in A. But if I create a function Public Function fxVal(ByVal a As Variant) fxVal = a End Function and copy it down column C I get the value of the first item in the range only A B C TestRange =TestRange =fxVal(TestRange) 1 1 1 2 2 1 3 3 1 4 4 1 5 5 1 Further if I make a column D that is =2*TestRange I get the expected values, but if I use a function Public Function fxTimesTwo(ByVal a As Variant) fxTimesTwo = 2 * a End Function I get #Value! for each cell... D E =2*TestRange =fxTimesTwo(TestRange) 2 #VALUE! 4 #VALUE! 6 #VALUE! 8 #VALUE! 10 #VALUE! In dealing with this issue, I have determined that the DefinedRange is passed to the function as a VarType=8204 vbArray+vbVariant. I have tried using CInt to the passed variable and countless other things but cannot resolve from within the Function. I can obviously resolve the issue from within the spreadsheet by calling the function with cell addresses ie fxVal(A3). Also, I can use fxVal(Int(TestRange)) and the functions will work. From researching this it appears that the Int(TestRange) will convert the parameter to an individual element double... Obviously, since Excel has countless internal functions, ie SQRT(TestRange) that work fine, there should be someway that my man made function should be able to handle the named range from within the function. Any help appreciated... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem setting each parameter in an Array = Nothing | Excel Programming | |||
Does the COUNTIF criteria parameter accepts functions? | Excel Worksheet Functions | |||
parameter problem | Excel Programming | |||
User defined functions - parameter descriptions | Excel Programming | |||
Problem with ADO with Parameter Object | Excel Programming |