{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://www.manongdao.com/data/attach/logo/logo.png', '推荐 对你真心纯属浪费 的问题《Is there a way to get the enums in VBA?》','https://www.manongdao.com/q-1185422.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}
Is there a way to get the enums in VBA? Something like this example for C#, but for VBA?
using System;
class EnumsExampleZ
{
private enum SiteNames
{
SomeSample = 1,
SomeOtherSample = 2,
SomeThirdSample = 3
}
static void Main()
{
Type enumType = typeof(SiteNames);
string[] enumName = enumType.GetEnumNames();
for (int i = 0; i < enumName.Length; i++)
{
Console.WriteLine(enumName[i]);
}
}
}
Lets say we have the following:
Enum FruitType
Apple = 1
Orange = 2
Plum = 3
End Enum
How can we display on the immediate window these:
Apple
Orange
Plum
Any method which does not return a keyed collection or (preferably a scripting dictionary) will be prone to errors if the enumeration range is not a contiguous range, such as the case where you are using the enumeration to map to bits. My solution to this has been to develop a class of 'EnumerationDictionary' which allows arrays of the enumeration or the enumeration names to be returned, and name to be looked up given an enumeration and a string to be used to retrieve an enumeration. The example below is for colours in a word document and shows how to combine an internal enumeration with additional user defined values. Its a bit clunky but works very well.
Plus the following utility to reverse dictionaries.
Parsing the VBA code yourself with the VBIDE Extensibility library is going to appear nice & simple at first, and then you're going to hit edge cases and soon realize that you need to actually implement that part of the VBA spec in order to properly and successfully parse every possible way to define an enum in VBA.
I'd go with the simple solution.
That said Rubberduck is doing pretty much exactly that, and exposes an experimental COM API that allows you to enumerate all declarations (and their references) in the VBE, effectively empowering your VBA code with reflection-like capabilities; as of 2.0.11 (the latest release), the code would look something like this:
And in theory would output this:
However we (ok, I did) broke something around the 2.0.9 release, so if you try that in 2.0.11 you'll get a runtime error complaining about an invalid cast:
That
should beis an easy fix that we'll patch up by 2.0.12, but note that at that point the API is still experimental and very much subject to change (feature requests are welcome!), so I wouldn't recommend using it for anything other than toy projects.I think that the marvel CPearson's site has the answer with the [_First] and [_Last] trick. I have the need of speed up a lot of DB reading just to populate combo and list boxes with values in Office VBA application, and I just translate them to Enums. And of course, do a For Each like, with the For Next is a must, and the [_First] and [_Last] is the way to go. But, I have a lot of non-sequential Enums, each with 10 to 40 Enum itens, and code for each is too tediously. To unify all my combo and listbox feeding needs, I adapted CPearson's trick to non-sequential Enums too:
I added skip comment lines - I do a lot while developing.
I did not treat Enum that is not in Ascendant order; could be done, but I'm too OCD to allow an unordered Enum ;) and ordinarily, my Enums are coming from DB with an ORDER BY on the proper value (see at end of this answer).
Of course, it depends on [_First] and [_Last] values added properly.
And, answering your question, you can do a:
As a bonus, and for me the main reason to adapt the CPearson's procedure, it loads in a unidimensional array tuples of value/name of Enum; so, we can navigate all Enum values with:
The procedure is generating one of two different functions LoadEnumWhateverNameYouGaveItInArray() versions based if Enum is sequential or not.
You can forget about the sequential; the non-sequential enum function grab both situations; I left here because I first developed it and after adapted to the non-sequential case, and we never know when we'll need less code lines ;)
Notice that although Enum is natively Long, I used Integer in counter/EnumMin/EnumMax, just because the Enums that we need to know its names are less than hundred, like fruit names.
Hope it helps someone.
Edit: To complete the explanation, this is the procedure that I use to extract Enum from tables and write them in a static module:
Just remember to pass the strSQL like that:
Usually, I use the EliminateWhiteSpaces boolean with ToEscapeWhiteSpace = "_", but is a personal preference.
For above "John Coleman"'s example I suggest to use next functions:
Direct use of an
Arrayindexes (without LBound() and etc.) may cause different resuts, depends on value inOption Base 1Not directly an answer and might look pretty ugly, but I thought it might be useful to others.
In an old project I wanted to access columns with Enum (for example
row(, col.cType) = 1).I changed the column location, name, use, etc. pretty often, but with this lazy approach I could just rearrange the Enum and then copy paste the change in the string constant, and get the table headers:
Names starting with _ are hidden by default, so
[____]is used for padding and to avoid "cPath = 1"No - there is no native way to do this. You'd need to fully parse all of the user code and read the type libraries of any loaded projects and finally determine what scope each reference was referring to.
Enumerations can't be treated like reference types in VBA, and this due to the deep roots that VBA has in COM. Enums in VBA are more like aliases, and in fact, VBA doesn't even enforce type safety for them (again, because of COM interop - MIDL specs require that they are treated as a DWORD).
If you really need to do this in VBA, a good workaround would be to create your own enumeration class and use that instead.