Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Enumeration List to appear in spreadheet

Enumeration List to appear in spreadheet

Enumeration List to appear in spreadheet

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

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

RE: Enumeration List to appear in spreadheet

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.

RE: Enumeration List to appear in spreadheet


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?

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close