none
Enum statement in VBA RRS feed

  • Question

  • Hello,

    I'm only an occasional writer of macros for Excel and I have 2 questions regarding the Enum statement. Would appreciate some help.

    At the top of a standard Module I declared

    Enum aEnum

    a

    b

    c

    End Enum

    Question 1 : In some routine I have

    Dim i as aEnum

    For i = a to c

    MsgBox i

    Next

    Is there any way to make this loop display a b c rather than 0 1 2 ?

    Question 2 :

    Dim i as aEnum

    Let i = 10

    Is it normal that this does not raise an error (compilation or runtime) ?

    Similarly,

    Function aFunction (aParam as aEnum) as <some type>

    Is it normal that this function can be called with

    someEnumVariable = aFunction (10)

    without raising an error (compilation or runtime) ?

    It seems to me that in both cases the value 10 violates the definition of the type aEnum.

    Thanks in advance

    Monday, February 13, 2017 5:17 PM

Answers

  • For part 1, you could use a function:

    Enum aEnum
        a
        b
        c
    End Enum

      

    Sub test()
        Dim i As aEnum

        For i = a To c
            MsgBox EnumName(i)
        Next i
    End Sub

    Function EnumName(i As aEnum) As String
        Select Case i
            Case 0: EnumName = "a"
            Case 1: EnumName = "b"
            Case 2: EnumName = "c"
        End Select
    End Function

    For the second part, you should not be setting the value to a number, you should be using autocomplete, as shown in this screen capture:

    • Proposed as answer by Chenchen LiModerator Tuesday, February 14, 2017 5:29 AM
    • Marked as answer by nunof32 Tuesday, February 14, 2017 3:55 PM
    Monday, February 13, 2017 10:10 PM

All replies

  • For part 1, you could use a function:

    Enum aEnum
        a
        b
        c
    End Enum

      

    Sub test()
        Dim i As aEnum

        For i = a To c
            MsgBox EnumName(i)
        Next i
    End Sub

    Function EnumName(i As aEnum) As String
        Select Case i
            Case 0: EnumName = "a"
            Case 1: EnumName = "b"
            Case 2: EnumName = "c"
        End Select
    End Function

    For the second part, you should not be setting the value to a number, you should be using autocomplete, as shown in this screen capture:

    • Proposed as answer by Chenchen LiModerator Tuesday, February 14, 2017 5:29 AM
    • Marked as answer by nunof32 Tuesday, February 14, 2017 3:55 PM
    Monday, February 13, 2017 10:10 PM
  • Thanks very much for your suggestions.

    Actually I feared that those would be the only answers available.

    In the actual problem I have at hand I really need to restrict the range of (integer) values that a certain variable can get. I was hoping that, if the computation in the macro goes astray and there is somewhere an attempt to assign to that variable a value outside of its permissible range of values, some kind of 'type mismatch' runtime error would be raised. Then, an appropriate error handling routine would nicely cover every case where such an 'accident' would happen during the execution of the macro (which is rather long and complex). I hoped that Enum would help in that. Unfortunately it is not the case and I do have to write code to catch out-of-range values every time there is a possibility that the variable is assigned a value.

    As an aside comment,

    - if, when one prints or displays the value of a variable of an Enum type, what is obtained is the underlying integer value rather than its actual enumerated value

    - if, when one assigns a value to a variable of an enumerated type, there is no 'type mismatch' runtime error that is raised if the value is out of range

    I find it rather difficult to see what purpose the Enum of VBA could possibly serve.

    Again thanks very much for your answers.

    Tuesday, February 14, 2017 9:04 AM
  • Enums are not really meant for variables - they are meant to contain constants, and allow descriptive labels to be applied to those constants. So, in short, you don't assign a random value to a variable when it is an enum, you create a variable as an enum so that you can see the descriptive labels when choosing what value to assign that variable.

    I'm sure I said that badly - the bottom line is that your a, b, c are not really descriptions.  Chip Pearson has a page devoted to enums, at:

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

    So, read that.

    Tuesday, February 14, 2017 2:03 PM
  • You're right.

    Actually, after this discussion I realised that every other programming language I know (C, C++, Objective C) treats enums the same (cavalier) way. I had forgotten that. (I don't remember how Algol and Pascal, the languages of my long gone young days, defined them but it is possible that they were a bit stricter).

    The only real use of enums is increased readability of the code and use of them should not be extended beyond that. Which is why, as you point out, the enumerated values should really be descriptive (apple, banana, citrus) and not (a, b, c) as in my abstraction of the problem (especially that nothing prevents you from later declaring a variable that has the same identifier than an enumerated value !).

    Thanks again.

    Tuesday, February 14, 2017 3:55 PM