Enumeration List to appear in spreadheet

[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).

 

[xwb]

Not sure if this is what you're after

1) Put your items in a list of consecutive cells, say E1..E4
2) Select cell A1
3) Click on Data/Validation...
4) Under Allow, select List
5) Under source, type =$E$1:$E$4
6) Click OK - you will see a combo box arrow appear next to A1
7) Click on the combo box and select what you want.

 

[JTBorton]

xwb,

I'm not really looking for a validation list, I'm looking for the intellisense to appear in the formula parameters when a user types in a function.

For example, if you type =SUM(.. a list of arguments becomes visible to the user explaning what this function needs. I have found a way to register user-defined functions with excel so that this list will appear for your own functions, but I would also like the drop down intellisense to appear for an enumeration list, showing the user which options are available for that particular parameter. Just like it would if you are passing enumerated parameters to a function in the VBE.


For example:

Public Enum Compounds   'An enumerated list of compunds available for calculation
    [_First = 0]
    NHexane = 0
    NOctane = 1
    NDecane = 2
    OleicAcid = 3
    [_Last = 3]
End Enum

Public Function GetAlpha(Compound As Compounds, HKref As Compounds, TdegC As Double) As Double
Dim PaSat As Double
Dim PbSat As Double
    PaSat = PiSat(Compound, TdegC)
    PbSat = PiSat(HKref, TdegC)
    GetAlpha = PaSat / PbSat
End Function

Here, the parameters Compund and HKref are enumerated lists. If you call this function in the VBE, a drop down list of arguments will appear for those parameters telling you what you can use. I would like for this drop down list to appear in the function bar when calling this function from the spreadsheet.

Does that sound a little more clear in what I am looking for?

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