Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User Defined Function | Excel Worksheet Functions | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
Help within user defined function | Excel Programming | |||
Adding a pop-up help to a user-defined function | Excel Programming | |||
User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming |