using defined name in UDF

[therealmichaelnull]

I have a sheet with some defined names on it:

Name1 = A1:C1

when I type =Name1 in A5 it returns the value in A1. When I type =Name1 in C5 it returns the value in C1.

Is there a way to replicate this behavior when passing these names to a UDF?

My function work if the defined name is one cell but returns #value when the name is defined as above... I assume this is because my code is not able to except an array.

Any help would be appreciated.

 

[GS]

First off.., I don't see anything in your post that verifies "Name1" is
"on the sheet". I suspect it *refers to* a range on a specific sheet,
but is global in scope (workbook level). If the name was attached to
the sheet it would contain the sheetname in its definition, giving it
local scope (sheet level).

Secondly, the example you gave for the range ref is fully relative. If
it's your intention that formulas ref row1 of whatever column the
formula using the name is in then it should be defined like this...

Name: 'Sheet1'!Name1
RefersTo: A$1

...while the active cell is in column "A" when you define the name.

Note that this name is column-relative, row-absolute. Now formulas
using the name will ref row1 of their column because the column is
relative but the row is not.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[therealmichaelnull]

Thanks for the help Garry.

Sorry my post wasn't as clear as it could have been. My defined names are as you suspected global in scope but refer to a specific range in a worksheet. But using the names within a worksheet is not the issue. It works fine how I have it (I think)... continueing the exmaple above... when I put the formula in =Name1 in column A, B, or C it returns the values in A1,B1, and C1... If I put =Name1 in column D then it gives #Value.

My issue is that I don't know how pass Name1 to a UDF such that it will return the value in the same column. So I could put a formula like =myfunction(Name1) and it would pass the value of Name1 that is in the same column to the function...

For instance:

Function names_test1(N As Double) As Variant

names_test1 = N * 2

End Function

I hope I've made what I'm trying to accomplish clearer but as it is the endof a very long week... Thanks again for any help you might be able to offer.

 

[therealmichaelnull]

Thanks for the help Garry.

Sorry my post wasn't as clear as it could have been. My defined names are as you suspected global in scope but refer to a specific range in a worksheet. But using the names within a worksheet is not the issue. It works fine how I have it (I think)... continueing the exmaple above... when I put the formula in =Name1 in column A, B, or C it returns the values in A1,B1, and C1... If I put =Name1 in column D then it gives #Value.

My issue is that I don't know how pass Name1 to a UDF such that it will return the value in the same column. So I could put a formula like =myfunction(Name1) and it would pass the value of Name1 that is in the same column to the function...

For instance:

Function names_test1(N As Double) As Variant

names_test1 = N * 2

End Function

Name1 is a defined name in excel A1:C1

If I put =names_test1(A1) in A2 it returns A1*2 - just as expected.
If I put =names_test1(Name1) in A2 it gives me #value because I suspect my function isn't expecting an array.

What I want is to put in =names_test1(Name1) in A2, B2, and C2 and have it return A1*2, B1*2, and C1*2 respectively

I hope I've made what I'm trying to accomplish clearer but as it is the endof a very long week... Thanks again for any help you might be able to offer.

 

[GS]

If you follow my suggestion to the "T" it will result in what you want
because the definition I gave you is column-relative, and so will ref
row1 of whatever column you use the function in. Sorry if I did not
make this clear!

You *will* have to delete the existing global scope name.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I arrived at the same conclusion regarding the ref being fully absolute
after my 2nd post. I implemented my suggestion and I got the results
the OP was looking for in any column where the defined name is used in
a formula. The problem is that the OP wants to use it beyond colC but
have it work the same as in A:C. IMO, changing the RefersTo so it's
'col-relative,row-absolute' is the best approach! Making the name local
in scope should always be the 1st choice unless absolutely necessary to
have it global, IMO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Ron Rosenfeld formulated on Saturday :

In interpreted his request differently. I thought he wanted it to behave the
same as his worksheet, whereby it would return #VALUE if entered in Column D.
Obviously, he can choose. And learn about the different behaviours of
Names, passing arrays to UDF's, and so forth.

Absolutely! I agree...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Michael]

Ron,

Thanks for the help, the code you provided did exactly what I wanted.

Thanks all for your time.

Regards
Michael