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.
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.
Fantastic technique! FAN.TAS.TIC!
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
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?
Mike –
Sure, it’s not real complicated. I have a palette changing utility in this article (no posted code, though):
Using Colors in Excel Charts.
If anyone in interested I also have a pallet tool thingy that might be of use:
http://www.blog.methodsinexcel.co.uk/2009/02/21/working-with-colours-in-excel-mie-colour-manager/
And Sandy as one too:
http://excelwithmonarch.com/xlreport/better-excel-color-management
If you looking to manage colours in Excel, then there are a few options!
Not bad for a post on Enumerations!
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