Referring to named ranges in user defined functions 1

[mgp]

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

 

[Denial]

Your problem is not with the use of named ranges, as you will easily confirm by passing some UNNAMED ranges into your function. It is what you are doing to those ranges inside the function: you cannot apply the "+" operator to a range in the way you have attempted.

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.)

 

[mgp]

Just to clarify:

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

 

[bltseattle]

I have had to use the Excel function "INDIRECT" on worksheets before when I want a formula to return the values from a named range, instead of Excel treating the name as a text string.

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.

 

[Denial]

In my experience, user-defined functions behave EXACTLY the same as system-provided functions when it comes to cutting, copying, pasting, dragging, filldown-ing, etcetera-ing.

 

[joerd]

This looks like an interesting hidden (as far as my knowledge goes) feature of Excel, that lets you be a bit sloppy in referencing, and nicely takes the values on the same line. You couldn't do this in early versions of XL, that is for sure!
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.

 

[mgp]

Joerd

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

 

[briand2]

I had a go myself, using Mogens setup. If the named ranges are just single cells, it works fine.

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

 

[joerd]

Mogens,

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.

 

[mgp]

Thanks Joerd

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

 

[mgp]

Hi again Joerd

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

 

[joerd]

Looks good! I'll save it for later

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.