One of the more frustrating errors to occur in VBA is the Automation Error.  It can often pop up for no apparent reason despite your code looking perfect.

Reasons for This Error

There are a variety of reasons that this can occur.

Microsoft Office is made up of Objects – the Workbook object, Worksheet Object, Range object and Cell object to name just a few in Excel; and the Document Object in Word.  Each object has multiple properties and methods that are able to be programmed to control how the object behaves. If you do not program these methods correctly, or do not set a property correctly, then an automation error can occur.

It can be highly annoying as the code will run perfectly for a while, and then suddenly you’ll see this!

You may get a number of different messages for this error.

The Object Has Disconnected from Its Client

An automation error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.  Make sure any object variables that you are referring to in your code are still valid when you call the property and methods that you are controlling them with.

Excel Error Load Form

An automation error could occur within Excel if you load a form while another object (like the worksheet or range object) are still referenced.  Remember to set your object references to NOTHING after you have finished writing the code to control them with.

Error Hiding/Unhiding Sheets In Excel

There are 3 ways to control the visible property in an Excel sheet in VBA – xlSheetVisible, xlSheetHidden or xlSheetVeryHidden.   An automation error could occur if you are trying to write information to a sheet which has the xlVeryHidden property set – make sure you set the sheet to visible in the code before you try to write anything to the sheet using Visual Basic Code.

Error 429 and 440

If you receive either of these errors, it can mean that your DLL files or ActiveX controls that you might be using are not correctly registered on your PC, or if you are using an API incorrectly.  It can also occur if you are running using 32-bit files on a 64-bit machine, or vice versa. 

Automation Error When Running Macro Enabled Workbook Over A Network

If you have a workbook open over a network, and you get an automation error when you run a macro, check your network connections – it could be that the network path is no longer valid.   Check you network paths and connections and make sure that they are all still working.

ADODB Automation Error

This error can occur when you are programming within Microsoft Access and are using the ADODB.Connection object.  It can be caused by any number of reasons from conflicting DLL files to a registry corruption, or simply that you did not set the recordlist object to nothing when you were finished using it!

Common Causes and Things to Check

Here are some reasons you might be getting the error. Perhaps you are…

• Trying to write information to hidden sheets or cells.
• Trying to write information to other documents or workbooks.
• Referring to objects that do not exist.
• Needing to install an update to Office, or the correct .Net framework.
• Loading objects (like pictures) into memory using a loop, and failing to clear the memory between each load (set Pic = Nothing).
• A Corrupt Registry – this is a hard one, as it often means that you need to remove Office entirely from your machine and then re-install it.  

Ways to Solve It

Error Trapping

Error trapping is one of the best ways to solve this problem.  You can use On Error Resume Next – or On Error GoTo Label – depending on your needs at the time.  If you want the code to carry on running and ignore the error, use On Error Resume Next.  If you want the code to stop running, create an error label and direct the code to that error label by using On Error GoTo label.

Clear the Memory

Another way to solve the problem is to make sure you clear any referred objects either in a loop or at the end of your code.  For example, if you have referred to a picture object, set the picture object to NOTHING, or if you have referred to a Worksheet object, set the Worksheet object to NOTHING once you have run the code for that picture of worksheet.

The code example below will insert a picture into a worksheet.  It may or may not give you an automation error!

Sub InsertPicture()
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:\data\image" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Next i
End Sub

The variable is declared as an OLEObject, and then the SET keyword is used to assign an image to the object.  The object is then populated with an image and inserted into the Excel sheet – some formatting taking place at the same time. I have then added a loop to the code to insert 100 images into the Excel sheet.  Occasionally this causes an automation error, but sometimes it doesn’t – frustrating, right?

The error often occurs when Excel runs out of memory – assigning an object over and over again without clearing the object could mean that Excel is struggling with memory and could lead to an automation error.

We can solve this problem 2 ways:

1. Set the object to NOTHING after it is inserted each time
2. Add an error trap to the code.

Sub InsertPicture()
On Error Resume Next
	Dim i As Integer
	For i = 1 To 100
		With Worksheets("Sheet1")
 		Set shp = .OLEObjects.Add(ClassType:="Forms.Image.1", _
 		Link:=False, DisplayAsIcon:=False, Left:=.Cells(i, "A").Left
		, Top:=.Cells(i, "A").Top, Width:=264, Height:=124)
 		End With
		 With shp
			.Object.PictureSizeMode = 3
			.Object.Picture = LoadPicture("C:\data\image" & i & ".jpg")
			.Object.BorderStyle = 0
			.Object.BackStyle = 0
	 End With
	Set shp = Nothing
	Next i
End Sub

Make sure your PC is up to date

A third way to solve the problem is to install the required update to Office or Windows, or the latest version of the .Net framework.  You can check to see if there are any updates available for your PC in your ‘Check for updates’ setting on your PC.

In Windows 10, you can type ‘updates’ in the search bar, and it will enable you to click on “Check for updates“.

It is always a good idea to keep your machine up to date.

Run a Registry Check

If all else fails, there are external software programs that you can download to run a check on the registry of your PC.

As you can see from above, this error often has a mind of its own – and can be very frustrating to solve.  A lot of the times trial and error is required, however, writing clean code with good error traps and referring to methods, properties and objects correctly often can solve the problem.