Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

JTBorton (Chemical) (OP)
5 Nov 09 20:56
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 (Computer)
7 Nov 09 15:23
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 (Chemical) (OP)
7 Nov 09 16:24
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:

CODE

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]

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!

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