Late Bound Enumerations

Mike Alexander of DataPig Technologies has put together a video about using enumerations when you’re automating Excel from another application. Take a look.

You can download Excel_Enumerations.txt, which should take the drudgery our of setting those up yourself.

Excel and Access Integration Workshop

Speaking of Excel automation, Mike and I are putting on the Excel and Access Integration Workshop. (Subtle segue I know.)

This 2-day workshop is designed to give you a comprehensive review of the wide range of integration techniques that can be performed using Excel and Access. Through step-by-step instruction, you will explore the benefits of integrating Excel and Access, the differences and similarities between Excel and Access, and some of the common pitfalls of moving and analyzing data across applications.

It’s May 18-19, 2009 in Dallas. Definitely Excel and Access integration is one of my favorite topics, so I’m excited to be a part of it. Register here, if you’d like to come.

Posted in Uncategorized

6 thoughts on “Late Bound Enumerations

  1. Mike,

    I agree with you on the value of enumerated lists, but I have one that is not in your list. It isn’t an Excel list, but should be, and whilst not specifically needed for late-binding I use it all the time.

    Public Enum xlColorIndex
    xlCIBlack = 1
    xlCIWhite = 2
    xlCIRed = 3
    xlCIBrightGreen = 4
    xlCIBlue = 5
    xlCIYellow = 6
    xlCIPink = 7
    xlCITurquoise = 8
    xlCIDarkRed = 9
    xlCIGreen = 10
    xlCIDarkBlue = 11
    xlCIDarkYellow = 12
    xlCIViolet = 13
    xlCITeal = 14
    xlCIGray25 = 15
    xlCIGray50 = 16
    xlChartCIPeriwinkle = 17 ‘——————–
    xlChartCIPlum = 18 ‘ chart colours
    xlChartCIIvory = 19 ‘
    xlChartCILightTurquoise = 20 ‘
    xlChartCIDarkPurple = 21 ‘
    xlChartCICoral = 22 ‘
    xlChartCIOceanBlue = 23 ‘
    xlChartCIIceBlue = 24 ‘
    xlChartCIDarkBlue = 25 ‘
    xlChartCIPink = 26 ‘
    xlChartCIYellow = 27 ‘
    xlChartCITurquoise = 28 ‘
    xlChartCIViolet = 29 ‘
    xlChartCIDarkRed = 30 ‘
    xlChartCITeal = 31 ‘
    xlChartCIBlue = 32 ‘——————–
    xlCISkyBlue = 33
    xlCILightGreen = 35
    xlCILightYellow = 36
    xlCIPaleBlue = 37
    xlCIRose = 38
    xlCILavender = 39
    xlCITan = 40
    xlCILightBlue = 41
    xlCIAqua = 42
    xlCILime = 43
    xlCIGold = 44
    xlCILightOrange = 45
    xlCIOrange = 46
    xlCIBlueGray = 47
    xlCIGray40 = 48
    xlCIDarkTeal = 49
    xlCISeaGreen = 50
    xlCIDarkGreen = 51
    xlCIBrown = 53
    xlCIIndigo = 55
    xlCIGray80 = 56
    End Enum

  2. Very nice Bob. I’ve added these to the text file.
    Were did you find these constants?

    Thinking out loud….

    Could one ever automatically customize the color pallete using something similar? Has anyone out there customized the color pallet using Automation?

  3. Mike,

    I am a very keen user of enumerated lists, and there are other benefits even from defining your own.

    For instance, let us say that we have a list of return codes, a classic example of an enumerated list, that looks like so

    Enum appReturnCodes

    All_OK = 0
    Invalid_Input = -1
    Process_Error = -2
    Too_Few_Params = -3
    End Enum

    You can then create a variable with tha datatype, such

    Dim ReturnCode As appReturnCodes

    which is nice as a documemtary feature, but gets better because when you then lookto load that variable in the code, when you type

    ReturnCode =

    the typing of the = sign invokes intellisense for that list, so you can select it rather than type it.

    Alls erious VBA coders should be using enumerated lists IMO.
    End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *