Pass named range to user defined function returns #value!

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

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

 

[Guest]

Ron is referring to this linewhere 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

 

[Bob Phillips]

Are you sure =qty*price gives you what you expect. In my system it just
multiplies the first items of each range, that is A1*B1, which cannot be
what you expect.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

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.

 

[Ron Rosenfeld]

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

Well, if that's the case, then most likely your named ranges are multicell
ranges. So you have to treat them as such. You can't just refer to the named
range as if it were a single cell range, if it is not. For example, modifying
your routine a bit:

---------------------------------
Option Explicit
Public Function MyFunction(qtyIn, priceIn)
Dim c As Range
For Each c In qtyIn
Debug.Print c
Next c
MyFunction = Application.WorksheetFunction.SumProduct(qtyIn, priceIn)
End Function