2

I'm trying to add validation to a Cell but I would like to use an Enum as Source. 

 Public Enum account

      AA
      BB
      PP
      ZZ

  End Enum

  Sub Main()

    With Range("C9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:= ...

  End With
End Sub

Normally I would put Join(account, ",") if account would be an array, but for an Enum it doesn't work. And if direct usage of Enum is not possible how can I add enum values to an array?

M.Kadyrov
  • 53
  • 10

2 Answers2

3

It's not as straightforward as you would wish, but there is a way to do this.

The underlying value of an Enum is of type Long. So it's a number, not a string. Only the VBA compiler knows about AA; this gets translated into 0 everywhere else (and BB is 1, PP is 2, etc.). So the first thing to do is to explicitly associate a string to each enum option. I've done this using functions like this, which are easy to make but do require a little bit of manual maintenance each time you add, remove, or modify an enum option:

Function AccountEnumString(a As account) As String
    Select Case a
        Case AA: AccountEnumString = "AA"
        Case BB: AccountEnumString = "BB"
        Case PP: AccountEnumString = "PP"
        Case ZZ: AccountEnumString = "ZZ"
        Case Else: Err.Raise 9999, , "Unexpected enum value."
    End Select
End Function

Then you want to loop through all enum options, making a list of their associated string. You could in principle say

Dim a As account
For a = AA To ZZ
'...

But that will be very difficult to maintain: what if you add another enum option such that ZZ isn't the last anymore? Then you'll have to change your code. A better option is to put a little more work in the enum declaration itself:

Public Enum account
    [_First] = 1
    AA = 1
    BB = 2
    PP = 3
    ZZ = 4
    [_Last] = 4
End Enum

Here you explicitly assign values to each option (rather than letting the compiler assign defaults) and you also add a First and a Last indicating the bounds of the enum. These require manual maintenance, but this is trivial in comparison to the trouble of maintaining the rest of the code as would be required without this trick.

Note on syntax: the _ underscore makes the _First and _Last elements hidden (they will not be displayed in the VB Editor's autocompletion) and the [ square brackets ] make the underscore character valid for use as the first character of a variable name. (source of this trick)

Now you can loop through your enum options, collect the associated strings in an array, join it with , delimiters, and use that as validation formula:

Dim a As account
Dim arrValidationList() As String

ReDim arrValidationList(account.[_First] To account.[_Last])
For a = account.[_First] To account.[_Last]
    arrValidationList(a) = AccountEnumString(a)
Next

With Range("C9").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(arrValidationList, ",")
End With

Note that each time you add or remove enum options, the following things will require manual maintenance:

  • The account enum definition, making sure that the numbering is sequential and that the First and Last elements actually represent the bounds, and
  • The AccountEnumString function, making sure the strings are representative of the enum options to your satisfaction

Also see these somewhat related questions:

Community
  • 1
  • 1
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • Thank you for the detailed answer. I was afraid it is that complicated. It seems that using a `Collection` or similar would be the easiest and much cleaner... – M.Kadyrov Mar 12 '15 at 09:33
  • How would a collection help? – Jean-François Corbett Mar 12 '15 at 09:40
  • Or a dictionary - It seems easier to maintain hard coded values in a Sub as a dictionary then using a separately defined enum with a function and then having to covert it all to an array in the sub. Or would this be considered as bad design? – M.Kadyrov Mar 12 '15 at 09:55
  • Not sure, I'd have to see it before commenting on it. But that would be the stuff of a new question rather than a long comment ping-pong. – Jean-François Corbett Mar 12 '15 at 09:59
0

I found it easier to maintain the accounts hard coded in a function

Function getAccounts() As Variant

Dim AccDict As Object

Set AccDict = CreateObject("Scripting.Dictionary")

AccDict.Add 1, "AA"
AccDict.Add 2, "BB"

AccDict.Add 10, "ZZ"
AccDict.Add 11, "PP"

getAccounts = AccDict.Items()

End Function

Then I can access the values in the sub

  Sub Main()
  Dim accountList As Variant
  accountList = getAccounts()

    With Range("C9").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:=Join(accountList, ",")

  End With
End Sub

The Cell in the excel sheet displays the required list. However I don't know if this is a good design...

M.Kadyrov
  • 53
  • 10