3

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.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
  • 1
    @pnuts - Yes, that works. But the I won't be able to take advantage of builtins, I would have to define one named range for each available item in the Enum (or at least those that I foresee that may be used). That is quite inconvenient. – sancho.s ReinstateMonicaCellio Dec 06 '13 at 13:39
  • possible duplicate of [How can I use enum identifiers in Excel UDF](http://stackoverflow.com/questions/7188017/how-can-i-use-enum-identifiers-in-excel-udf). (I know you already looked there, but in fact there is no difference for built-in and user-defined enums.) – Jean-François Corbett Dec 06 '13 at 14:20
  • @Jean-FrançoisCorbett - I think it is not a duplicate, but this deserves a specific answer (that's why I was not sure, and that's why asked). If the answer is something like "built-in and user-defined enums are exactly the same, for this purpose", then I guess it is good to have that answer posted here, and a link to the other thread. I guess "This question may already have an answer here" and "Possible duplicate" might be misleading as such, for other readers finding this thread. I have actually seen many other cases like this around (which misled myself). – sancho.s ReinstateMonicaCellio Dec 06 '13 at 14:40
  • @sancho.s: that's just the default text when the question gets marked as duplicate. I didn't write it. I only wrote the remark in parentheses after that. But I think it's a duplicate because the exact same answers are equally valid for both questions. – Jean-François Corbett Dec 06 '13 at 15:36
  • @Jean-FrançoisCorbett - I know it's the default. I was pointing out, generically, that the defaults may be misleading sometimes (I had experienced that). This is irrespective of the particular present case (which anyway you think it is a duplicate, and I think it is not... obviously, there is some leeway for opinion). – sancho.s ReinstateMonicaCellio Dec 12 '13 at 16:19
  • 1
    http://stackoverflow.com/questions/10036711/converting-a-string-representation-of-a-constant-into-a-constant/10037332?s=1|2.2553#10037332 ...but don't do that. – Tim Williams Nov 18 '14 at 22:04
  • @TimWilliams - Interesting... But I could not make it work (even if it is not recommended). – sancho.s ReinstateMonicaCellio Nov 20 '14 at 04:19

1 Answers1

6

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:

enter image description here

Adapt to your own requirements, at your own risk.

Community
  • 1
  • 1
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • 2
    1) [As confirmed here](http://stackoverflow.com/users/2707864/sancho-s), the answer linked works, but it half kills the purpose of saving work (it still retains other advantages of the Enum). 2) The other solution you propose is well described by "... would probably never use this myself for anything else than ..." :-), but it is a leap ahead of empty hands as I had. Still waiting to hear about possible alternatives. – sancho.s ReinstateMonicaCellio Dec 06 '13 at 14:26