User defined function - adding names
 

Hello Everyone,

I want to write a UDF which amoungst other things adds a couple of
names to the Workbook.Names collection, it then tee's up a Sub which
uses the values I've just stored in the names. So I have a couple of
questions:

1. Am I correct in saying that this is not 'ordinarily' possible from a
worksheet function (i.e.

public function someTest(ByVal l_dbIn as double) as double
Thisworkbook.names.add "aName", l_dbIn
someTest = l_dbIn + 1
end function

=someTest(20)

does not work). I've tested it and it doesn't but if someone could
confirm I'd be grateful.

2. I vaguely remember reading, possibly in a post by Laurent Longre,
that it could be done by adding an XL4 name. Is this correct? If so
how would it be done (syntax etc.)? What are the implications (ie do I
need to add an XLM Macro sheet, how do I do this)?

I need 6 names and I will be storing: a long, 4 strings and a date

I appreciate there are other ways to do this, writing to a file etc.,
but I'd like to give this a go if its feasible.

Thanks everyone,

chilli

User defined function - adding names
 

A UDF can not do what you are asking it to do. The only thing a UDF can do is
to return a value... It can not change the values of cells (other than the
one it is in), it can not change formatting, and it can not add named ranges
or such. There are a couple of obscure exceptions to this rule (IIRC it can
change the comments in a cell) but otherwise it can do no more than the built
in excel functions can.
--
HTH...

Jim Thomlinson


"chillihawk" wrote:

Hello Everyone,

I want to write a UDF which amoungst other things adds a couple of
names to the Workbook.Names collection, it then tee's up a Sub which
uses the values I've just stored in the names. So I have a couple of
questions:

1. Am I correct in saying that this is not 'ordinarily' possible from a
worksheet function (i.e.

public function someTest(ByVal l_dbIn as double) as double
Thisworkbook.names.add "aName", l_dbIn
someTest = l_dbIn + 1
end function

=someTest(20)

does not work). I've tested it and it doesn't but if someone could
confirm I'd be grateful.

2. I vaguely remember reading, possibly in a post by Laurent Longre,
that it could be done by adding an XL4 name. Is this correct? If so
how would it be done (syntax etc.)? What are the implications (ie do I
need to add an XLM Macro sheet, how do I do this)?

I need 6 names and I will be storing: a long, 4 strings and a date

I appreciate there are other ways to do this, writing to a file etc.,
but I'd like to give this a go if its feasible.

Thanks everyone,

chilli

User defined function - adding names
 

http://support.microsoft.com/kb/170787/en-us
Description of limitations of custom functions in Excel

See the 5th bullet.

http://www.cpearson.com/excel/hidden.htm
Hidden namespace

--
Regards,
Tom Ogilvy



"chillihawk" wrote in message
oups.com...
Hello Everyone,

I want to write a UDF which amoungst other things adds a couple of
names to the Workbook.Names collection, it then tee's up a Sub which
uses the values I've just stored in the names. So I have a couple of
questions:

1. Am I correct in saying that this is not 'ordinarily' possible from a
worksheet function (i.e.

public function someTest(ByVal l_dbIn as double) as double
Thisworkbook.names.add "aName", l_dbIn
someTest = l_dbIn + 1
end function

=someTest(20)

does not work). I've tested it and it doesn't but if someone could
confirm I'd be grateful.

2. I vaguely remember reading, possibly in a post by Laurent Longre,
that it could be done by adding an XL4 name. Is this correct? If so
how would it be done (syntax etc.)? What are the implications (ie do I
need to add an XLM Macro sheet, how do I do this)?

I need 6 names and I will be storing: a long, 4 strings and a date

I appreciate there are other ways to do this, writing to a file etc.,
but I'd like to give this a go if its feasible.

Thanks everyone,

chilli

User defined function - adding names
 

Thank you Jim
Thank you Tom

Tom Ogilvy wrote:
http://support.microsoft.com/kb/170787/en-us
Description of limitations of custom functions in Excel

See the 5th bullet.

http://www.cpearson.com/excel/hidden.htm
Hidden namespace

--
Regards,
Tom Ogilvy



"chillihawk" wrote in message
oups.com...
Hello Everyone,

I want to write a UDF which amoungst other things adds a couple of
names to the Workbook.Names collection, it then tee's up a Sub which
uses the values I've just stored in the names. So I have a couple of
questions:

1. Am I correct in saying that this is not 'ordinarily' possible from a
worksheet function (i.e.

public function someTest(ByVal l_dbIn as double) as double
Thisworkbook.names.add "aName", l_dbIn
someTest = l_dbIn + 1
end function

=someTest(20)

does not work). I've tested it and it doesn't but if someone could
confirm I'd be grateful.

2. I vaguely remember reading, possibly in a post by Laurent Longre,
that it could be done by adding an XL4 name. Is this correct? If so
how would it be done (syntax etc.)? What are the implications (ie do I
need to add an XLM Macro sheet, how do I do this)?

I need 6 names and I will be storing: a long, 4 strings and a date

I appreciate there are other ways to do this, writing to a file etc.,
but I'd like to give this a go if its feasible.

Thanks everyone,

chilli

User defined function - adding names
 

Thank you Jim
Thank you Tom

Tom Ogilvy wrote:
http://support.microsoft.com/kb/170787/en-us
Description of limitations of custom functions in Excel

See the 5th bullet.

http://www.cpearson.com/excel/hidden.htm
Hidden namespace

--
Regards,
Tom Ogilvy



"chillihawk" wrote in message
oups.com...
Hello Everyone,

I want to write a UDF which amoungst other things adds a couple of
names to the Workbook.Names collection, it then tee's up a Sub which
uses the values I've just stored in the names. So I have a couple of
questions:

1. Am I correct in saying that this is not 'ordinarily' possible from a
worksheet function (i.e.

public function someTest(ByVal l_dbIn as double) as double
Thisworkbook.names.add "aName", l_dbIn
someTest = l_dbIn + 1
end function

=someTest(20)

does not work). I've tested it and it doesn't but if someone could
confirm I'd be grateful.

2. I vaguely remember reading, possibly in a post by Laurent Longre,
that it could be done by adding an XL4 name. Is this correct? If so
how would it be done (syntax etc.)? What are the implications (ie do I
need to add an XLM Macro sheet, how do I do this)?

I need 6 names and I will be storing: a long, 4 strings and a date

I appreciate there are other ways to do this, writing to a file etc.,
but I'd like to give this a go if its feasible.

Thanks everyone,

chilli