Enumeration List to appear in spreadheet 1

[JTBorton]

Quick question: (Excel 2003)
Is it possible to make an enumeration list appear in the spreadsheet when a user calls a user-defined function?

Example:
=RelativeVolatility(Compound, HKref, TdegC)
where the compound of interest and the reference heavy key are enumeration lists
NHexane = 0
NOctane = 1
NDecane = 2
OleicAcid = 3

When calling the function from the VBE the enum list will appear. Is it possible to make this list appear from the worksheet? The only options I can see are either A) make the user provide the integer equivalents, or B) make the Compound and HKref arguments a string and use a look-up function to assign the correct enum (which defeats the purpose of an enum).

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Hi,

A UDF used on a sheet can ONLY return data to the CELL in which it is called. It cannot affect any other cell in any way.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

might be possible by aking the function output an array and array entering it into relevant number of cells.

no experience of doing this so just guessing!

http://www.cpearson.com/excel/ReturningArraysFromVBA.aspx

might help?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[JTBorton]

Thanks for the comments guys, but I guess I was not clear in what I am asking.

When I type a formula into a cell, such as =SUM(... a list of arguments appears telling the user what that formula needs. I am looking for a way to provide the user with this list of arguments when they use my formula. I would especially like for some sort of intellisense to appear (as they are typing in the formula) to show the user what arguments are available for the enumeration variables.

I am in no way trying to change other cells or print the enumeration list to the sheet. I apologize for the lack of clarity.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[SkipVought]

Just use the Insert > Function feature!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Loomah]

quick google search took me here

http://www.jkp-ads.com/articles/RegisterUDF00.asp

might be more out there

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​