VBA Automation Error

This tutorial will explain what a VBA Automation Error means and how it occurs.

Excel is made up of objects – the Workbook object, Worksheet object, Range object and Cell object to name but a few. Each object has multiple properties and methods whose behavior can be controlled with VBA code. If the VBA code is not correctly programmed, then an automation error can occur. It is one of the more frustrating errors in VBA as it can often pop up for no apparent reason when your code looks perfectly fine!

(See our Error Handling Guide for more information about VBA Errors)

Referring to a Variable no Longer Active

An Automation Error could occur when you are referring to a workbook or worksheet via a variable, but the variable is no longer active.

Sub TestAutomation()
  Dim strFile As String
  Dim wb As Workbook
'open file and set workbook variable
  strFile = Application.GetOpenFilename
  Set wb = Workbooks.Open(strFile)
'Close the workbook
  wb.Close
'try to activate the workbook
  wb.Activate
End Sub

When we run the code above, we will get an automation error.  This is due to the fact that we have opened a workbook and assigned a variable to that workbook. We have then closed the workbook but in the next line of code we try to activate the closed workbook.  This will cause the error as the variable is no longer active.

If we want to activate a workbook, we first need to have the workbook open!

Memory Overload

This error can also sometimes occur if you have a loop and you forget to clear an object during the course of the loop. However, it might only occur sometimes, and not others-  which is one of the reasons why this error is can be so annoying.

Take for example this code below:

Sub InsertPicture()
  Dim i As Integer
  Dim shp As Object
  For i = 1 To 100
    With Worksheets("Sheet1")
'set the object variable
       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
'load the picture
     .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 Object, 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 with some formatting taking place at the same time.  We then add 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 solution to this problem is to clear the object variable within the loop by setting the object to NOTHING – this will free the memory and prevent the error.

 Sub InsertPicture()
  Dim i As Integer
  Dim shp As Object
  For i = 1 To 100
    With Worksheets("Sheet1")
'set the object variable
     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
'load the picture
     .Object.Picture = LoadPicture("C:\data\image.jpg")
     .Object.BorderStyle = 0
     .Object.BackStyle = 0
    End With
'clear the object variable
    Set shp = Nothing
  Next i
End Sub

DLL Errors and Updating Windows

Sometimes the error occurs and there is nothing that can be done within VBA code.  Re-registering DLL’s that are being used, making sure that our Windows is up to date and as a last resort, running a Registry Check as sometimes the only things that may work to clear this error.

A good way of avoiding this error is to make sure that error traps are in place using the On Error Go To or On Error Resume Next routines.