Exposing an Enum type to Excel

[Becks]

Hi all

I have the following code in a standard code Module:

Code: Select all

Public Enum bxAnalysis
    bxMINERALOGY = 1
    bxALUMINA = 2
    bxIRON = 4
    bxSILICA = 8
    bxCOMPLETE = 15
    bxLABELS = 16
    bxIntermediate = 32
    bxTranspose = 64
End Enum

Function bauxiteAnalysis(ta As Double, _
    aa As Double, _
:
    Optional resultType As bxAnalysis = bxCOMPLETE)

Dim resultCol As integer

    If resultType And bxLABELS Then resultCol = 2 Else resultCol = 0
:
End Function

and I successfully test on resultType in the VBA code. However, there is no Intellisense with respect to the function parameters when it is entered into an Excel cell - the user doesn't see the allowable values for resultType and can only guess at which integer to use.
Can the enumeration type be exposed so that the user can select ?

Regards
Kevin

[HansV]

As far as I know, that is not possible. The Excel interface doesn't see VBA Enums.
You could define names bxMINERALOGY etc. in your workbook, using Formulas > Name Manager. You can then press F3 when entering a function to insert a defined name.
But (a) this wouldn't be specific to your UDF, you could insert the names anywhere, and (b) the names are listed in alphabetic order, not by their value, so it's probably not an attractive option:

S0663.png

[Becks]

Thanks Hans

I began to suspect as much, but was wondering about CELL(info_type[, reference]) where the list of info_type values is selectable (may be a leftover from Excel 4 ?)

Regards
Becks

[HansV]

There are more built-in functions that do that, for example SUBTOTAL and MATCH, so it's not a relic of Excel 4.
You can add a description to a UDF and to its arguments; in Excel 2010 and later it is relatively easy: see User-Defined Function Argument Descriptions In Excel 2010. But there is no way to display a dropdown list of possible values for an argument.

[Becks]

An inelegant work-around is to create a series of functions that will return the appropriate value i.e change the eEnum and have

Code: Select all

Public Enum bxAnalysis
    bxMINERALOGY_ = 1
    bxALUMINA_ = 2
    bxIRON_ = 4
    bxSILICA_ = 8
    bxLABELS_ = 16
    bxIntermediate_ = 32
    bxTranspose_ = 64
End Enum

Function bxALL() As bxAnalysis
    bxALL = bxMINERALOGY_ + bxALUMINA_ + bxIRON_ + bxSILICA_
End Function

Function bxMINERALOGY() As bxAnalysis
    bxMINERALOGY= bxMINERALOGY_ 
End Function

Function bxALUMINA() As bxAnalysis
    bxALUMINA= bxALUMINA_ 
End Function

At the appropriate place, typing "bx" would bring up the list of functions and you could enter
=bauxiteAnalysis(ta, aa, ... , bxALL()) or
=bauxiteAnalysis(ta, aa, ... , bxMINERALOGY()+bxALUMINA())

Regards
Becks