Use a builtin Enum in a cell formula with a UDF

问题:

I have a user-defined function that takes a parameter which has an associated builtin enum:

Public Function bgrcolor_cells(rng As Range, xlcl As Long) As Integer

I want to write a formula like this in a cell

=bgrcolor_cells($A2:$B2,vbRed)

instead of

=bgrcolor_cells($A2:$B2,255)

Is that possible? How?

PS: I have found How can I use enum identifiers in Excel UDF, but it refers to user-defined Enums. I do not know if the fact that I want to use a builtin Enum makes a difference.

EDIT 1 Perhaps some automated reading of the code where the builtin Enum is defined may help in defining either the Class by Jean-François Corbett or named ranges, and then one would avoid typing from scratch. I have seen such automated parsing, possible pointers are:

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

http://www.excelforum.com/excel-programming-vba-macros/356892-programmatic-generation-of-enum-to-string-functions.html

EDIT 2 (as per this) A comment has been posted stating that "there is no difference for built-in and user-defined enums", and that this is a dupe. I think it is not, and that if the quoted comment is correct, then it may be part of an answer (perhaps worth posting as such) for the present specific and different question.

回答1:

The question you link to already covers this topic and the accepted answer should work just fine. There is no particular shortcut for VBA built-in enums.

Otherwise you can try something like the following.

---

For entertainment purposes only

Before I get lynched for this, I'd like to say that I did this just for fun as a proof-of-principle and would probably never use this myself!

Create a class called ColorEnums:

Option Explicit

Public vbRed As Long
Public vbGreen As Long
'etc.

Private Sub Class_Initialize()
    vbRed = VBA.vbRed
    vbGreen = VBA.vbGreen
    'etc.
End Sub

Make a user-defined function like this:

Function GetValueOfColorEnumByName(colorEnumName As String)
    GetValueOfColorEnumByName = CallByName(New ColorEnums, colorEnumName, VbGet)
End Function

Where CallByName allows us (and this is very ugly) to evaluate the value of a member of a class from its name in a string.

Example usage:

Adapt to your own requirements, at your own risk.