Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass named range to user defined function returns #value!
Ron is referring to this line
MyFunction = qytIn * priceIN where qytIn is misspelled. But as you said, it was not copied from the VBE. I think your issue is you are passing arrays to your function and expecting it to return a single value. You have not programmed it to return an array. When you enter =Qty*Price in XL, the result is actually an array of 100 values. When entered normally, the specific element returned from the array depends on the cell in which you entered it, which is why the results appear normal when you enter the formula and copy it down. For example 2 3 3 6 =Qty*Price 5 7 The formula will return 18, as expected, which is the second element in the array {6, 18, 35} because you entered it on the second row. If you key in the formula and hit Cntrl+Shift+Enter, you'll get 6 (the first element of the array-because you are instructing it to return the entire array, XL is not making assumptions about which particular element you want returned). In order to see the entire array, select 100 cells, type =Qty*Price in your formula bar, and hit Cntrl+Shift+Enter. I tried rewriting your function just to illustrate what I mean, but it has to be array entered whenever it is used (unless using single cell ranges) - it doesn't make assumptions about which element to return when entered normally the way XL does. And it only deals with single dimension arrays. So far, I don't see any advantage to using this or any other UDF for your computation as it is easily done with native XL functions or expressions. Option Explicit Public Function MyFunction(qtyIn As Range, _ priceIn As Range) As Variant Dim arrTemp() As Double Dim i As Long If qtyIn.Rows.Count < priceIn.Rows.Count Or _ qtyIn.Columns.Count < priceIn.Columns.Count Or _ qtyIn.Cells.Count < priceIn.Cells.Count Then MyFunction = CVErr(xlErrValue) Exit Function End If ReDim arrTemp(1 To qtyIn.Cells.Count) For i = 1 To qtyIn.Cells.Count arrTemp(i) = qtyIn.Cells(i) * priceIn.Cells(i) Next i If qtyIn.Rows.Count 1 Then MyFunction = Application.Transpose(arrTemp) Else: MyFunction = arrTemp End If End Function "IneedHelp" wrote: I always use option explicit, so typo's are not the issue...the example was not copied from the VBE. There must be something else that I am missing on refering to named ranges. BTW I am using 2003 "Ron Rosenfeld" wrote: On Fri, 9 Nov 2007 19:11:00 -0800, IneedHelp wrote: If I pass a named range as a parameter to a user defined function, it returns #value! I created several named ranges. For Example A1:A100 is qty B1:B100 is price If I enter qty * price in colum C, the results are as I expect. However, when I attemp to pass the named range to a user defined function, it returns #value! and debug.print inside by UDF yields no value. For example: MyFunction(qtyIn, priceIn) returns #Value! Public Function MyFunction(qtyIn, priceIn) debug.print qtyIn MyFunction = qytIn * priceIN End function I have experience using VBA but am new to using VBA in Excel. The problem is NOT using named ranges, rather it is that you have and undeclared variable probably due to a typo. One of the advantages of requiring that your variables be explicitly Dim'd is that you will pick up typos like you have in your UDF. Tools/Options/Editor and select "Require variable declaration" If you do that, you will see the Option Explicit statement at the beginning of each module. --ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass named range to user defined function returns #value!
Thanks JMB.
Your example got me pointed in the right direction. I suspected it was related to passing in a range, but did not know how to specify the element of the array that I wanted. The UDF was just a sample to illustrate the basic problem. I am trying to automate an estimating worksheet for some less Excel-literate co-workers. The desire is to insert subtotals in several columns when the value in specified column changes, then copy only the subtotal rows to a budget approval sheet. My plan is to use a UDF to determine if the row is a detail row or a subtotal row, and if it is a subtotal row, deterime the number of rows to include in the subtotal. So, the formula in the cell is conditional on the value in colum D (which I made a named range) and the parameters (i.e range) that have to be passed to the subtotal function must be dynamically. Thanks again for your help. "JMB" wrote: Ron is referring to this line MyFunction = qytIn * priceIN where qytIn is misspelled. But as you said, it was not copied from the VBE. I think your issue is you are passing arrays to your function and expecting it to return a single value. You have not programmed it to return an array. When you enter =Qty*Price in XL, the result is actually an array of 100 values. When entered normally, the specific element returned from the array depends on the cell in which you entered it, which is why the results appear normal when you enter the formula and copy it down. For example 2 3 3 6 =Qty*Price 5 7 The formula will return 18, as expected, which is the second element in the array {6, 18, 35} because you entered it on the second row. If you key in the formula and hit Cntrl+Shift+Enter, you'll get 6 (the first element of the array-because you are instructing it to return the entire array, XL is not making assumptions about which particular element you want returned). In order to see the entire array, select 100 cells, type =Qty*Price in your formula bar, and hit Cntrl+Shift+Enter. I tried rewriting your function just to illustrate what I mean, but it has to be array entered whenever it is used (unless using single cell ranges) - it doesn't make assumptions about which element to return when entered normally the way XL does. And it only deals with single dimension arrays. So far, I don't see any advantage to using this or any other UDF for your computation as it is easily done with native XL functions or expressions. Option Explicit Public Function MyFunction(qtyIn As Range, _ priceIn As Range) As Variant Dim arrTemp() As Double Dim i As Long If qtyIn.Rows.Count < priceIn.Rows.Count Or _ qtyIn.Columns.Count < priceIn.Columns.Count Or _ qtyIn.Cells.Count < priceIn.Cells.Count Then MyFunction = CVErr(xlErrValue) Exit Function End If ReDim arrTemp(1 To qtyIn.Cells.Count) For i = 1 To qtyIn.Cells.Count arrTemp(i) = qtyIn.Cells(i) * priceIn.Cells(i) Next i If qtyIn.Rows.Count 1 Then MyFunction = Application.Transpose(arrTemp) Else: MyFunction = arrTemp End If End Function "IneedHelp" wrote: I always use option explicit, so typo's are not the issue...the example was not copied from the VBE. There must be something else that I am missing on refering to named ranges. BTW I am using 2003 "Ron Rosenfeld" wrote: On Fri, 9 Nov 2007 19:11:00 -0800, IneedHelp wrote: If I pass a named range as a parameter to a user defined function, it returns #value! I created several named ranges. For Example A1:A100 is qty B1:B100 is price If I enter qty * price in colum C, the results are as I expect. However, when I attemp to pass the named range to a user defined function, it returns #value! and debug.print inside by UDF yields no value. For example: MyFunction(qtyIn, priceIn) returns #Value! Public Function MyFunction(qtyIn, priceIn) debug.print qtyIn MyFunction = qytIn * priceIN End function I have experience using VBA but am new to using VBA in Excel. The problem is NOT using named ranges, rather it is that you have and undeclared variable probably due to a typo. One of the advantages of requiring that your variables be explicitly Dim'd is that you will pick up typos like you have in your UDF. Tools/Options/Editor and select "Require variable declaration" If you do that, you will see the Option Explicit statement at the beginning of each module. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User defined function returns imprecise value when used in worksheet | Excel Discussion (Misc queries) | |||
User Defined Function - Using Cell Range | Excel Programming | |||
Passing a named range into a user defined function | Excel Programming | |||
User Defined Function used in Macro returns #VALUE | Excel Programming | |||
passing named range to a UDF user defined function | Excel Programming |