VBA Runtime error 5

This article will explain the VBA runtime error 5.

 

VBA runtime error 5 is a somewhat obscure and not particularly common error. It is described as an ‘Invalid procedure call or argument’. It can occur when you have a value that is out of the range of an argument.

Value out of Range of an Argument

Say you record a macro to create a Pivot table.  The macro creates a new sheet as the destination for the Pivot table. The sheet number will be hard-coded into the code that the macro records.

For example:

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Combined!R5C1:R17C4", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=7
Sheets("Sheet1").Select

The function to create the Pivot Table is made up of a number of arguments, one of which for the CreatePivotTable method is Table Destination. In the code snippet provided, the table destination is Sheet 1

Now, for some reason you may wish to delete Sheet 1 and then re-run this macro.  This is when the error would occur.

The macro will run the line:

Sheets.Add

Excel will add another sheet to your workbook but as Sheet1 has already been used (even though it has been deleted), it will take the next number – so will add in Sheet2.

However, the next line of the macro refers to the destination of the Pivot table as being Sheet1 which does not exist. An Error 5 will then occur as the value in the argument is invalid.

When the error occurs, clicking on Debug in the Error message box will highlight the error.

 

Empty String Argument

The same situation could occur if you were using an object like the FileSystemObject in Excel.

Consider the following code:

Private Sub TestObjects()
 Dim strItem As String
 Dim objFSO As Object
 Dim objFolder As Object

 strItem = "C:\Data"
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFolder = objFSO.GetFolder(strItem)
 MsgBox objFolder.Name
End Sub

If we run this code, we get the following message box showing the name of the folder eg: Data.

However, let’s amend the code as shown below:

Private Sub TestObjects()
 Dim strItem As String
 Dim objFSO As Object
 Dim objFolder As Object

 strItem = ""
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFolder = objFSO.GetFolder(strItem)
 MsgBox objFolder.Name
End Sub

Now when we run the code, we will get an error.

This is due to the fact that we are sending an empty string to the GetFolder method (strItem = “”). This is invalid so the error occurs.

Note if we sent an incorrect folder location, we would get a differnt Run-time error!

Incorrect Platform of Excel

This error can also occur if you have written a macro in a Windows version of Excel, and then you try to run the macro on a Macintosh version. The error may occur as the VBA code can differ slightly between platforms.