ExcelBanter - View Single Post

Ron Rosenfeld[_2_]

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