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)
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 SubWhen 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!
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 SubThe 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
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.
Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!
Easily access all of the code examples found on our site.
Simply navigate to the menu, click, and the code will be inserted directly into your module. .xlam add-in.
(No installation required!)
