Dots in Dims

Hi, I’m the dot operator. You may remember me from such VBA statements as and object.method.

Okay, enough of that. When you see a dot operator in a Dim statement, you’re seeing it in the form of library.object. For instance, if you’re automating Outlook, you may use:

Dim olMail As Outlook.MailItem

Outlook isn’t an object, it’s the name of the library to which you are early binding. MailItem is the name of an object in that library. Another common one is an ADO Recordset:

Dim rs As ADODB.Recordset

You don’t need to call out the library for your program to compile, but you may need to for it to run properly. The problem that you may run into is when you dimension a variable as an object that exists in two different libraries. Each library has a priority and if you specify an object from multiple libraries, the object from the library with the highest priority will be used. That’s not a problem if the library you intend to use has the highest priority, but that’s not something you can depend on.

Go to the VBE and Tools > References and you’ll see a Priority label with up and down buttons. You can change the priority of the libraries that are selected. On my wife’s Office 2003 installation, the default libraries in order of priority are:

  • VBA
  • Excel
  • OLE Automation
  • Office

If I exit the References dialog and open the Object Browser (F2), I also see something called stdole. I have no idea what this is.

In general, I specify the library if it’s not a default library. If you were to examine my code, you would probably find exception after exception to this rule. I should have written that I try to specify non-default libraries. I almost never specify the MSForms library and it’s not default. MSForms gets added when you add a userform. It contains a Textbox object and there’s also a Textbox object in the Excel object library (from the Drawing toolbar, I believe). That’s one that I really should specify.

Jamie recently metioned in a newsgroup post that he sometimes includes the Excel library in his Dim statements. In Excel VBA, this isn’t necessary because Excel is always the highest priority library (unless the user changes it). However, if you want to port your code to VB6 or some other VBA, you will save yourself time by including the Excel library. Unless I specifically intend to port the code, I leave it off. But I think it’s a good point, and it really costs nothing to specify the Excel library. If nothing else, I’d learn what was in the Excel library vs. the VBA library.

Posted in Uncategorized

4 thoughts on “Dots in Dims

  1. “You may remember me from such VBA statements as”

    Do you ever fully qualify those enums as well? e.g.
    rs.CursorType = ADODB.CursorTypeEnum.adOpenStatic

    “stdole. I have no idea what this is.”

    You know, when you Dim something As IUnknown … no?



  2. “Do you ever fully qualify those enums as well?”

    Yeah, right. At least enums have that two letter “unique” prefix that brings the chance of a conflict down to less that 1%. Or you could just use 3.

  3. “You can change the priority of the libraries that are selected.”

    My priority list (Excel 2000) is identical to your wife’s.

    What I have always wondered, is there a way to add to this ‘default’ list? In other words, if I wanted ADO 2.x to always be in that list – can this be done?


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 *