Dutch Gemini's Weblog Pages

January 5, 2011

VBA Run-time error ‘Automation error Library not registered.’

Filed under: Programming,VBA — dutchgemini @ 4:49 pm
Tags: , , , ,

If you encounter the  VBA Run-time error ‘Automation error Library not registered.’ then between your VBA project’s references there is a reference to a file that is “MISSING”.

This is the case when for example a DLL, OCX or another VBA project used while developing in your application is not installed on the computer the application is being used. Obviously, you should never distribute applications without making sure that the components used are available on the target computers, but there are cases in which VBA takes care of resolving the problem automatically, one example are the Microsoft Office applications, where each version installs in a separate folder containing a number (for instance for Office 2003 (Release 11) you have C:\Program Files\Microsoft Office\Office11)

In general, each reference is correctly mapped to the right -and probably also most recent- component, since the GUID of each application does not change. But there are cases where the component does not correctly remap (because the GUID has changed or because it is not available). If this occurs, then you will see such “MISSING” reference.

To correct the problem, open the VB-IDE, go to your project references (Tools -> References), either scroll the list and put or remove a checkmark in front of the desired component or browse for a file on your disk.

Ah, if you have access to the project, otherwise you’re out of luck.

Tip: before you distribute your application, run a compile (Debug -> Compile). If there is a missing reference, VBE may make this problem evident by issuing a compile error “Can’t find project or library” on a function that you know is correct (I had that on the String() function, not to confuse with VBA.String()). When you close the compiler error, VBE will open the references dialog for you.

When the ‘Automation error Library not registered.’ triggers on registered libraries

The funny thing is that a missing reference impacts on your code when addressing the other references in the project. Suppose you want to set a reference to “Visual Basic for Applications” then you would write the following statement (“VBA” is the codename for this reference):

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References("VBA")

Using a named index with missing references triggers the error. However, if you use a numeric index to obtain a reference object, the error does not trigger.

Dim oReference As Object
Set oReference = ThisWorkbook.VBProject.References(1) ' Usually VBA is number 1

Even next code may trigger the error. Note the use of ‘may’ and not ‘will’. This is because the ‘.Name‘ property does not exist for missing references, so as long as the item’s counter is smaller than the missing reference item you are indeed able to query this property. The code stops running as soon as ‘lItem’ matches the index of the missing reference:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Name = "VBA") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

If you really want to play safe, use the ‘.Guid‘ property. This property does always exist, but is also means that you need to know it:

Dim oReference As Object, lItem As Long
For lItem = 1 To ThisWorkbook.VBProject.References.Count
    If (ThisWorkbook.VBProject.References(lItem).Guid = _
       "{000204EF-0000-0000-C000-000000000046}") _
    Then Set oReference = ThisWorkbook.VBProject.References(lItem)
Next lItem

Of course, you can always iterate through the references using For…Each, but remember to use the ‘.Guid‘ property:

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem

The Guid is needed when you want to programmatically add references (the 0,0 at the end instructs VBA to load the most recent reference for this Guid):

Dim oReference As Object
For Each oReference In ThisWorkbook.VBProject.References
    If (oReference.Guid = "{000204EF-0000-0000-C000-000000000046}") _
    Then Exit For
Next lItem
If oReference Is Nothing _
Then Set oReference = ThisWorkbook.VBProject.References.AddFromGuid("{000204EF-0000-0000-C000-000000000046}", 0, 0)

You can get a list of the Guids used by your application by iterating the collection of references.

Dutch.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: