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.

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

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 end of a very long week... Thanks again for any help you might be able to offer.

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 end of a very long week... Thanks again for any help you might be able to offer.

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[s].

--
Garry

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

On Fri, 15 Feb 2013 11:41:46 -0800 (PST), wrote:

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.

Are you certain of your presentation? If I do as you write, I get a circular reference error entering that value into A5. The only way I can get what you write is by making the references in Name1 absolute, and not relative.

If the references are really absolute, and your typing above is mistaken, then, in order to mimic the behavior in a UDF, you have to do a bunch of things.

What kind of variable is being passed?
Where is the function you wrote located?
How do the two things above relate?
What do you want to return.

Simplistically, something like below would do it, but I've left out a bunch of steps. It works fine for the specific conditions you have in your post if the range reference is absolute. If the range reference is truly relative, I cannot reproduce what you write on a worksheet

==================================
Option Explicit
Function TestName(Nm)
Dim Colnum As Long
'Tests to decide what kind of a variable Nm is
'OK we've decided it is a variant array

'What kind of items in the array?
'OK the items are strings
'Are they also ranges -- YES

'where did the function come from
Colnum = Application.Caller.Column
'OK the function was in Column number colnum

'Now need to normalize colnum so it is relative to the
'reference in Name1; and not relative to column "A"

Colnum = Colnum - (Nm(1).Column - 1)
If Colnum <= Nm.Count Then
TestName = Nm(Colnum)
Else
TestName = CVErr(xlErrValue)
End If

End Function
==========================

On Fri, 15 Feb 2013 11:41:46 -0800 (PST), wrote:

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.

As an addendum to my previous, where you obviously have numbers in a1, b1, and c1 and want to multiply by two, something like:

================================
Option Explicit
Function TestName(Nm)
Dim Colnum As Long
'Tests to decide what kind of a variable Nm is
'OK we've decided it is a variant array

'What kind of items in the array?
'OK the items are numbers
'Are they also ranges -- YES

'where did the function come from
Colnum = Application.Caller.Column
'OK the function was in Column number colnum

'Now need to normalize colnum so it is relative to the
'reference in Name1; and not relative to column "A"

Colnum = Colnum - (Nm(1).Column - 1)
If Colnum <= Nm.Count Then
TestName = Nm(Colnum) * 2
Else
TestName = CVErr(xlErrValue)
End If

End Function
========================

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

On Sat, 16 Feb 2013 10:19:45 -0500, GS wrote:

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!

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.

Ron Rosenfeld formulated on Saturday :
On Sat, 16 Feb 2013 10:19:45 -0500, GS wrote:

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!

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

On Saturday, February 16, 2013 8:01:49 AM UTC-5, Ron Rosenfeld wrote:
On Fri, 15 Feb 2013 11:41:46 -0800 (PST), wrote: 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. As an addendum to my previous, where you obviously have numbers in a1, b1, and c1 and want to multiply by two, something like: ================================ Option Explicit Function TestName(Nm) Dim Colnum As Long 'Tests to decide what kind of a variable Nm is 'OK we've decided it is a variant array 'What kind of items in the array? 'OK the items are numbers 'Are they also ranges -- YES 'where did the function come from Colnum = Application.Caller.Column 'OK the function was in Column number colnum 'Now need to normalize colnum so it is relative to the 'reference in Name1; and not relative to column "A" Colnum = Colnum - (Nm(1).Column - 1) If Colnum <= Nm.Count Then TestName = Nm(Colnum) * 2 Else TestName = CVErr(xlErrValue) End If End Function ========================

Ron,

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

Thanks all for your time.

Regards
Michael

On Mon, 18 Feb 2013 06:00:00 -0800 (PST), Michael wrote:

Ron,

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

Thanks all for your time.

Regards
Michael

Glad to help. Thanks for the feedback.