Referring to named ranges in user defined functions
Referring to named ranges in user defined functions
(OP)
Can anyone tell me if there is a way to avoid getting an error message when using a user defined function to refer to a named range?
Say I have three named ranges:
A1:A5 named "Name1"
B1:B5 named "Name2"
C1:C5 named "Name3"
-all cells filled with numbers
Then I have a VBA worksheet function
Function testname(a, b, c)
testname = a + b + c
End Function
When typing into Cell D1:
=testname(Name1;Name2;Name3)
I get the #Value error message
This works for all the built-in functions. Is there a way to make the userdefined function understand it also?
regards
Mogens
Say I have three named ranges:
A1:A5 named "Name1"
B1:B5 named "Name2"
C1:C5 named "Name3"
-all cells filled with numbers
Then I have a VBA worksheet function
Function testname(a, b, c)
testname = a + b + c
End Function
When typing into Cell D1:
=testname(Name1;Name2;Name3)
I get the #Value error message
This works for all the built-in functions. Is there a way to make the userdefined function understand it also?
regards
Mogens





RE: Referring to named ranges in user defined functions
Depending upon exactly what it is you are trying to do, you will at least get a result by replacing the line
testname = a + b + c
with the line
testname = WorksheetFunction.Sum(a) + WorksheetFunction.Sum(b) + WorksheetFunction.Sum(c)
(Provided, of course, that you use commas rather than semi-colons to separate the arguments in your invocation of your function.)
RE: Referring to named ranges in user defined functions
I'm not trying to make the function summarize three named ranges. I'm trying to make the worksheet function use the values in the SAME row as where the function was entered.
The three columns (with named ranges) all cover the SAME rows.
If I insert any worksheet formula (using built-in functions)in a cell adjacent to one of these rows, I get the same result whether I use the actual cell references or I use the names. The Worksheet function automatically looks for the values in the SAME row (even though each name contains a range of several values/rows)
If I do a FillDown of the formula to cover all rows adjacent to the named ranges, then I get the correct results in each row.
The user defined function just won't accept that.
I know I'm passing a range to the function, but I was hoping to find out how to make the user defined function pickup the right values just as any built-in functions will do.
(NB. The argument separators was a typing error - when using "paste function" I make sure to use the correct separators)
Hope this clarifies my question.
regards
Mogens
RE: Referring to named ranges in user defined functions
I don't know how to adapt it to VBA, however, maybe you can figure out if that is a fix.
I also recommend trying to solve this problem with named cells (just one cell per name) instead of ranges until you get it working, so that potential issues with array functions can be kept separate from the name issues. If you are in a worksheet and add together named ranges, all the ranges would need to be the same dimensions (I think) and you would have to hit ctrl-shift-enter to tell Excel it is an array operation.
RE: Referring to named ranges in user defined functions
RE: Referring to named ranges in user defined functions
Some light can be shed on this issue if you read the topic "About labels and names in formulas" in the XL help.
To reproduce this behaviour in your user-defined function, you will have to do some coding, test if the argument is a range reference, and take the intersection of the row where the function call is with the column where the range is defined...
Good luck, I'd say.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Referring to named ranges in user defined functions
You hit the nail about what I was looking for - can this test be coded into the function?
I'm surprised no one else seems to be familiar with this (sloppy) feature. I've been using this for years (have a file from 1998 created in Excel 5 where I used it succesfully) Try it! (just use my example above)
It is a very convenient feature if you are making multiple similar calculations based on different variables stored in each row. It just makes the formulas look neater as if you were referencing individual cells.
I've also made lots of worksheet functions before, but somehow by pure luck I seem to have avoided using them with named ranges.
I realize that it is a "sloppy" way, but if it works on built-in functions, why not use it.
regards
Mogens
RE: Referring to named ranges in user defined functions
However, if the named ranges are columns then it does exactly what Mogens says! I tried using references such as name.column and activecell.row etc but that did not work either; using activecell means all cells give the same answer, regardless of what row the the return key is pressed on.
I haven't got time to post the formulae, code, etc as my daughter needs to get on the PC to do her homework!!!
I'd be really interested to see a resolution to this problem.
Brian
RE: Referring to named ranges in user defined functions
You really got my interest here. So I found two features in XL of which I didn't know the existence. The second one is the Caller property (see VB help) which gives you a Range object where the calling function resides.
Here is my quick & dirty solution to the problem. I am sure that you'll have to rewrite it a bit to trap errors, but this is the basic structure:
Function Total(a As Variant, b As Variant, c As Variant)
Dim MyFuncCell As Range
If VarType(a) > vbArray Then
Set MyFuncRow = Application.Caller.EntireRow
a1 = Application.Intersect(MyFuncRow, a)
b1 = Application.Intersect(MyFuncRow, b)
c1 = Application.Intersect(MyFuncRow, c)
Else
a1 = a
b1 = b
c1 = c
End If
Total = a1 + b1 + c1
End Function
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Referring to named ranges in user defined functions
With all the wisdom available in these fora, I knew someone would find a way through. I'll have a go at your code over the weekend and come back if I find anything to add. (never seen the caller property before.
best Regards
Mogens
RE: Referring to named ranges in user defined functions
It works perfectly!
I just modified your code slightly to make it work both ways (named rows or columns).
********************************************************
Function Total(a As Variant, b As Variant, c As Variant)
Dim MyFuncCell As Range
If VarType(a) > vbArray Then
Set MyFuncRow = Application.Caller.EntireRow
Set MyFuncCol = Application.Caller.EntireColumn
If Application.Intersect(MyFuncRow, a) Is Nothing Then
a1 = Application.Intersect(MyFuncCol, a)
b1 = Application.Intersect(MyFuncCol, b)
c1 = Application.Intersect(MyFuncCol, c)
Else
a1 = Application.Intersect(MyFuncRow, a)
b1 = Application.Intersect(MyFuncRow, b)
c1 = Application.Intersect(MyFuncRow, c)
End If
Else
a1 = a
b1 = b
c1 = c
End If
'can be any function with 3 variables
Total = a1 + b1 + c1
End Function
*****************************************************
best regards
Mogens
RE: Referring to named ranges in user defined functions
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.