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
==========================
