I need help with this macro. Every time I run it, I get the error below. I thought it was a simple macro that I could have anybody on my team use to make it take a less time than they were taking to manually create this PivotTable every time they ran the report. However, it’s not working. Please see error below and advise. I emboldened and italicized the error.
Sub LEDOTTR()
'
' LEDOTTR Macro
'
'
Range("A87").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
***ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R87C1:R8214C25", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="LED OTTR!R1C1", TableName:="PivotTable6", _
DefaultVersion:=xlPivotTableVersion14***
Sheets("LED OTTR").Select
Cells(1, 1).Select
With ActiveSheet.PivotTables("PivotTable6").PivotFields("LED")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable6").PivotFields("Hierarchy name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable6").PivotFields("LED").CurrentPage = "(All)"
With ActiveSheet.PivotTables("PivotTable6").PivotFields("LED")
.PivotItems("LED Marine").Visible = False
.PivotItems("LL48 Linear LED").Visible = False
.PivotItems("Other").Visible = False
End With
ActiveSheet.PivotTables("PivotTable6").PivotFields("LED"). _
EnableMultiplePageItems = True
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields(" Late " & Chr(10) & "Indicator"), "Sum of Late " & Chr(10) & "Indicator", _
xlSum
ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
"PivotTable6").PivotFields("Early /Ontime" & Chr(10) & " Indicator"), _
"Sum of Early /Ontime" & Chr(10) & " Indicator", xlSum
End Sub
pnuts
58.4k11 gold badges87 silver badges140 bronze badges
asked May 29, 2015 at 20:01
1
The answer to your problem is located here.
Your sheet name in TableDestination:="LED OTTR!R1C1" needs to be surrounded with single quotes in order for it to work TableDestination:="'LED OTTR'!R1C1"
You will also have problems with the duplicated name if you do not delete this PivotTable before rerunning the code.
answered May 29, 2015 at 22:27
Byron WallByron Wall
3,9802 gold badges13 silver badges29 bronze badges
2
In my case the trouble was related to the region settings in Windows. I downloaded a (protected) xlsm file from the internet and always got the «Run Time Error 5 — Invalid Procedure Call or Argument» error when opening it. The other answer hinted to me that it may have to do with the language settings. The scripts in the file were obviously programmed in German while my Windows was set to an English region.
In Windows 10 Settings > Time & Language > Region I changed Region as well as Regional format to German. After restarting Excel, the file worked.
answered Feb 2, 2020 at 11:38
n1000n1000
5,07410 gold badges37 silver badges65 bronze badges
I’ve run into this, and the error was caused by calling the Worksheet.ExportAsFixedFormat method on a hidden worksheet.
I just added the if worksheet.Visible Then condition to my code to prevent getting the Invalid Procedure Call or Argument error.
answered Aug 18, 2020 at 20:55
While installing macro file to excel 2016 ribbon window getting below error when I debug the error. Below section was highlighed in yellow collor when clicked on debug
Set one = Application.CommandBars(«Worksheet Menu Bar»).Controls.Add(Type:= _
msoControlPopup, before:=onecount)
answered Apr 26, 2022 at 13:30
2
Error handling refers to the way runtime errors are handled. Error handling in VBA is done using the On Error statement and the Err object. Runtime errors can be generated by attempting to execute error-causing code or they can be raised explicitly using the Err.Raise method. There are a number of built-in types of runtime errors, and custom errors can be defined as well. Each type of runtime error has a unique number which can be used to determine at runtime which type of error has occurred and respond accordingly.
On Error
The On Error statement is used to control what happens when a runtime error occurs. The On Error statement sets or removes the current error handling scope. When a runtime error occurs in VBA the error trap is triggered and if an On Error statement has been set, VBA will respond according to which type of On Error statement was used.
| Statement | Description |
|---|---|
| On Error Resume Next | Skips lines of code that cause errors. Use with caution. |
| On Error GoTo Line | When an error occurs execution will jump to a specified label or line number. |
| On Error GoTo 0 | Clears the current error and disables error handling. |
| On Error GoTo -1 | Clears the current error and resets the error trap. |
| Resume | When used after the On Error GoTo statement, Resume will continue execution from the line of code that caused the error. |
| Resume Next | When used after the On Error GoTo statement, Resume Next will continue execution from the line of code directly after the line that caused the error. |
| Resume Line | When used after the On Error GoTo statement, Resume Line will jump to a specified label or line number and continue execution. |
On Error Resume Next
Public Sub Example()
On Error Resume Next
Err.Raise 1004
Debug.Print "Error was skipped"
End SubOn Error GoTo Line
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Exit Sub
HandleError:
Debug.Print "Error Handled"
End SubNote: It is not recommended to use line numbers.
Public Sub Example()
10
20 On Error GoTo 80
30
40 Err.Raise 1004
50
60 Exit Sub
70
80 Debug.Print "Error Handled"
90
End SubOn Error GoTo…Resume
Public Sub Example()
Dim N As Long
N = 0
On Error GoTo HandleError
'Divide by zero error will be fixed by error handler
Debug.Print 1 / N
Debug.Print "Error Handled."
Exit Sub
HandleError:
If Err.Number = 11 Then
Debug.Print "Handling 'Division by zero' Error..."
N = 1
Resume
Else
Err.Raise Err.Number
End If
End SubOn Error GoTo…Resume Next
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Debug.Print "Error Handled. Resuming Next..."
Exit Sub
HandleError:
Debug.Print "Handling Error..."
Resume Next
End SubOn Error GoTo…Resume Line
Public Sub Example()
On Error GoTo HandleError
Err.Raise 1004
Continue:
Debug.Print "Resuming..."
Exit Sub
HandleError:
Debug.Print "Error Handled"
Resume Continue
End SubNote: It is not recommended to use line numbers.
Public Sub Example()
10
20 On Error GoTo 100
30
40 Err.Raise 1004
50
60 Debug.Print "Resuming..."
70
80 Exit Sub
90
100 Debug.Print "Error Handled"
110 Resume 60
120
End SubOn Error GoTo 0
Public Sub Example()
On Error Resume Next
Err.Raise 1004 'Error will be skipped
On Error GoTo 0
Err.Raise 1004 'Error will be raised
End SubOn Error GoTo -1
Public Sub Example()
On Error GoTo HandleError1
Err.Raise 1004
Exit Sub
HandleError1:
Debug.Print "HandleError1"
On Error GoTo -1
On Error GoTo HandleError2
Err.Raise 1004
Exit Sub
HandleError2:
Debug.Print "HandleError2"
Exit Sub
End SubThe Err Object
The Err Object is used to access information about a runtime error that has occurred. When a runtime error occurs, the Err object’s properties are filled with information about the error. The Err object can also be used to raise errors explicitly.
| Member | Description |
|---|---|
| Clear | Clears properties of the Err object. Does NOT reset error trap or clear error handling scope. |
| Description | A text description of the error. |
| HelpContext | The context ID for a topic in a help file. |
| HelpFile | The path to help file. |
| LastDllError | Returns a system error code produced by a call to a dynamic-link library (DLL). Read-only. Always returns zero on Mac. |
| Number | The error number, 0 through 65535. |
| Raise | Raises a specified runtime error. |
| Source | The name of the object or application that originated the error. |
The Err Object is a Singleton
The Err object has a single default global instance and cannot be instantiated. The Err object can be accessed anywhere in VBA just by typing Err.
Public Sub Example()
Err.Raise 1004 'No need to instantiate Err object
End SubPublic Sub Example()
Dim E As ErrObject
Set E = New ErrObject 'Causes error
End SubProperties
The Err object’s properties are filled with data when a runtime error occurs. The Number property is especially important because it can be used to dynamically respond to different types of errors. If no runtime error has occurred, the Number property will be 0. Therefore, to determine if an error has occurred in a particular line, the Number property can be checked to see if it is 0 or not.
Public Sub Example()
On Error Resume Next
Err.Raise 1004
If Err.Number <> 0 Then
With Err
Debug.Print "Number: " & .Number
Debug.Print "Description: " & .Description
Debug.Print "Source: " & .Source
Debug.Print "HelpFile: " & .HelpFile
Debug.Print "HelpContext: " & .HelpContext
Debug.Print "LastDllError: " & .LastDllError
End With
Err.Raise Err.Number
Else
Debug.Print "No Error"
End If
End SubClearing The Err Object
The Err object can be reset in a few different ways. The Err object is reset when Err.Clear is called, a Resume or Resume Next statement is executed for the error, or another On Error statement is executed. Exiting an error-causing procedure does not reset the Err object.
Note: Exiting an error-causing procedure does not reset the Err object.
Public Sub Example()
Call ErrorCausingProcedure1
Debug.Print Err.Number 'Prints 1004
Call ErrorCausingProcedure2
Debug.Print Err.Number 'Prints 0
Call ErrorCausingProcedure3
Debug.Print Err.Number 'Prints 0
Call ErrorCausingProcedure4
Debug.Print Err.Number 'Prints 0
End Sub
Public Sub ErrorCausingProcedure1()
'Exiting procedure doe NOT reset the Err object
On Error Resume Next
Err.Raise 1004
End Sub
Public Sub ErrorCausingProcedure2()
'Calling Err.Clear resets the Err object
On Error Resume Next
Err.Raise 1004
Err.Clear
End Sub
Public Sub ErrorCausingProcedure3()
'Resume Next resets the Err object
On Error GoTo HandleError
Err.Raise 1004
Exit Sub
HandleError:
Resume Next
End Sub
Public Sub ErrorCausingProcedure4()
'On Error statement resets the Err object
On Error Resume Next
Err.Raise 1004
On Error GoTo 0
End SubCalling Err.Clear only clears the Err object. Err.Clear does not reset the error handling trap or end the error handling scope. On Error GoTo -1 will clear the Err object and reset error trapping, allowing another error to be raised. On Error GoTo 0 will clear the Err object and clear the error handling scope.
Public Sub Example()
On Error Resume Next
'Error is skipped
Err.Raise 1004
'Prints 1004
Debug.Print Err.Number
'Err object is reset
Err.Clear
'Prints 0
Debug.Print Err.Number
'Error is skipped because error handling scope was not reset
Err.Raise 1004
'Prints 1004
Debug.Print Err.Number
'Clears Err object and error handling scope
On Error GoTo 0
'Prints 0
Debug.Print Err.Number
'Raises a runtime error
Err.Raise 1004
End SubError Trapping Options
Error Trapping options can be selected which can override error handling code in VBA. To change error trapping options navigate to Tools → Options → General in the Visual Basic Editor.
| Option | Description |
|---|---|
| Break on All Errors | Will enter break mode when any error is encountered regardless of error handling code. |
| Break in Class Module | Will enter break mode and show errors inside class modules. |
| Break on Unhandled Errors | This is the default setting. Will enter break mode when an error is encountered and it is not handled by code. |
CVErr Function
The CVErr function can be used to return an error from a function. CVErr returns a value of type Variant with subtype Error. Only variables of type Variant can be assigned a value using the CVErr function. CVErr can take any error number as an argument. CVErr can be used to return a cell error from a user-defined function that is intended for use in spreadsheets.
Option Explicit
Public Function ReturnValueError() As Variant
'Shows #VALUE Error in cell
ReturnValueError = CVErr(xlErrValue)
End Function| xlErr Constant | Cell Error |
|---|---|
| xlErrBlocked | #BLOCKED! |
| xlErrCalc | #CALC! |
| xlErrConnect | #CONNECT! |
| xlErrDiv0 | #DIV/0! |
| xlErrField | #FIELD! |
| xlErrGettingData | #GETTING_DATA |
| xlErrNA | #N/A |
| xlErrName | #NAME? |
| xlErrNull | #NULL! |
| xlErrNum | #NUM! |
| xlErrRef | #REF! |
| xlErrSpill | #SPILL! |
| xlErrUnknown | #UNKNOWN! |
| xlErrValue | #VALUE! |
IsError Function
The IsError function returns True if the argument expression evaluates to an error. IsError can be used to test if a cell value contains an error or a user-defined function returns an error. To return an error from a function use the CVErr function.
Option Explicit
Public Sub Example()
If IsError(Range("A1").Value) Then
Debug.Print "Range A1 contains an error."
End If
Dim E As Variant
E = ReturnError()
If IsError(E) Then
Debug.Print "E is Error: " & CStr(E)
End If
End Sub
Public Function ReturnError() As Variant
ReturnError = CVErr(xlErrValue)
End FunctionError Function
The Error/Error$ function is used to return the description text of an error. The Error function can be used to return a specific error description based on an error number or it can return the description text of the last error to occur.
Specific Error Description
Pass the optional ErrorNumber argument to the Error function to return a specific error description. Although the Error function can take a number between -2147483648 and 65535, it should be intended for use with the range 0 through 65535. If the error number is outside the valid range an Overflow runtime error will occur. If the error number is within the valid range but is not defined, the message «Application-defined or object-defined error» will be returned.
Public Sub Example()
Debug.Print Error(5) 'Prints: Invalid procedure call or argument
End SubMost Recent Error Description
Call the Error function with no ErrorNumber argument to return the description text for the last error to occur. If no error has occurred, a zero-length string will be returned. The Err.Description property can be used to get the text description of the most recent runtime error instead of using the Error function.
Public Sub Example()
On Error Resume Next
Err.Raise 5
Debug.Print Error() 'Prints: Invalid procedure call or argument
End SubRaising Errors
Use the Err.Raise method to raise a runtime error. Errors should be raised when an unacceptable state has been reached in a program. Existing VBA error numbers can be used to raise errors or custom error numbers can be created.
Raising Existing Errors
An appropriate VBA error number can be selected which describes the error.
Public Function RandomLong(MinValue As Long, MaxValue As Long) As Long
If MinValue > MaxValue Then
Err.Raise 5
End If
Randomize
RandomLong = Int((MaxValue - MinValue + 1) * Rnd + MinValue)
End FunctionUser-Defined Errors
To raise a user-defined error, create an error number by using the vbObjectError constant and adding a number between 513 and 65535. The range 0 through 512 is reserved for system errors. The vbObjectError constant has the value -2147221504 so user-defined errors will be negative. To derive the positive portion of a user-defined error simply subtract the vbObjectError constant from the error number.
Public Sub Example()
On Error GoTo HandleError
Err.Raise Number:=vbObjectError + 513, Description:="Custom Error"
Exit Sub
HandleError:
Debug.Print Err.Number - vbObjectError, Err.Description 'Prints: 513 Custom Error
Resume Next
End SubError Statement
The Error statement raises a runtime error for a given error number. The Error statement is included for backward compatibility with older versions of VBA and Err.Raise should be used instead for new code.
Public Sub Example()
'Backward compatible
Error 5
'Use this for new code
Err.Raise 5
End SubError Numbers
Runtime errors each have a number used to identify what type of error it is. Error numbers can be used with the Err.Raise method, the Error statement, and the Error function. When a runtime error occurs, the Err.Number property will be set to the number associated with the type of error. Any positive error number not listed in the table below returns «Application-defined or object-defined error».
| Error Number | Error Text |
|---|---|
| 3 | Return without GoSub |
| 5 | Invalid procedure call or argument |
| 6 | Overflow |
| 7 | Out of memory |
| 9 | Subscript out of range |
| 10 | This array is fixed or temporarily locked |
| 11 | Division by zero |
| 13 | Type mismatch |
| 14 | Out of string space |
| 16 | Expression too complex |
| 17 | Can’t perform requested operation |
| 18 | User interrupt occurred |
| 20 | Resume without error |
| 28 | Out of stack space |
| 35 | Sub or Function not defined |
| 47 | Too many DLL application clients |
| 48 | Error in loading DLL |
| 49 | Bad DLL calling convention |
| 51 | Internal error |
| 52 | Bad file name or number |
| 53 | File not found |
| 54 | Bad file mode |
| 55 | File already open |
| 57 | Device I/O error |
| 58 | File already exists |
| 59 | Bad record length |
| 61 | Disk full |
| 62 | Input past end of file |
| 63 | Bad record number |
| 67 | Too many files |
| 68 | Device unavailable |
| 70 | Permission denied |
| 71 | Disk not ready |
| 74 | Can’t rename with different drive |
| 75 | Path/File access error |
| 76 | Path not found |
| 91 | Object variable or With block variable not set |
| 92 | For loop not initialized |
| 93 | Invalid pattern string |
| 94 | Invalid use of Null |
| 96 | Unable to sink events of object because the object is already firing events to the maximum number of event receivers that it supports |
| 97 | Can not call friend function on object which is not an instance of defining class |
| 98 | A property or method call cannot include a reference to a private object, either as an argument or as a return value |
| 321 (1 — 2) | Invalid file format |
| 322 | Can’t create necessary temporary file |
| 325 | Invalid format in resource file |
| 380 (1 — 2) | Invalid property value |
| 381 | Invalid property array index |
| 382 | Set not supported at runtime |
| 383 | Set not supported (read-only property) |
| 385 | Need property array index |
| 387 | Set not permitted |
| 393 | Get not supported at runtime |
| 394 | Get not supported (write-only property) |
| 422 | Property not found |
| 423 | Property or method not found |
| 424 | Object required |
| 429 | ActiveX component can’t create object |
| 430 | Class does not support Automation or does not support expected interface |
| 432 | File name or class name not found during Automation operation |
| 438 | Object doesn’t support this property or method |
| 440 | Automation error |
| 442 | Connection to type library or object library for remote process has been lost. Press OK for dialog to remove reference. |
| 443 | Automation object does not have a default value |
| 445 | Object doesn’t support this action |
| 446 | Object doesn’t support named arguments |
| 447 | Object doesn’t support current locale setting |
| 448 | Named argument not found |
| 449 | Argument not optional |
| 450 | Wrong number of arguments or invalid property assignment |
| 451 | Property let procedure not defined and property get procedure did not return an object |
| 452 | Invalid ordinal |
| 453 | Specified DLL function not found |
| 454 | Code resource not found |
| 455 | Code resource lock error |
| 457 | This key is already associated with an element of this collection |
| 458 | Variable uses an Automation type not supported in Visual Basic |
| 459 | Object or class does not support the set of events |
| 460 (1 — 2) | Invalid clipboard format |
| 461 | Method or data member not found |
| 462 | The remote server machine does not exist or is unavailable |
| 463 | Class not registered on local machine |
| 481 (1 — 2) | Invalid picture |
| 482 (1 — 2) | Printer error |
| 735 | Can’t save file to TEMP |
| 744 | Search text not found |
| 746 | Replacements too long |
| 1004 | Application-defined or object-defined error |
| 31001 | Application-defined or object-defined error. *Out of memory |
| 31004 | Application-defined or object-defined error. *No object |
| 31018 | Application-defined or object-defined error. *Class is not set |
| 31027 | Application-defined or object-defined error. *Unable to activate object |
| 31032 | Application-defined or object-defined error. *Unable to create embedded object |
| 31036 | Application-defined or object-defined error. *Error saving to file |
| 31037 | Application-defined or object-defined error. *Error loading from file |
Home > VBA > VBA Invalid Procedure Call Or Argument Error (Error 5)
As Microsoft says, an Invalid Procedure Call Or Argument (Error 5) occurs when you have a specified value that is out of the range of the argument or you are using a procedure that is not available in the current Excel platform. The error could be one of these problems that can cause by different reasons.
For example, you are using a VBA function SIN in the code. Now while specifying the value for the argument you go out of the range of numbers then VBA generates this error.
Or lets you have written a code in the Window version of Excel and when you try to run the same code in the Macintosh version you get the Error 5 Invalid Procedure Call or Argument because the procedure you are trying to run is not valid.
What is VBA
- VBA ERROR Handling
- VBA Automation Error (Error 440)
- VBA Error 400
- VBA Object Doesn’t Support this Property or Method Error (Error 438)
- VBA Object Required Error (Error 424)
- VBA Out of Memory Error (Error 7)
- VBA Overflow Error (Error 6)
- VBA Runtime Error (Error 1004)
- VBA Subscript Out of Range Runtime Error (Error 9)
- VBA Type Mismatch Error (Error 13)
Search code, repositories, users, issues, pull requests…
Provide feedback
Saved searches
Use saved searches to filter your results more quickly
Sign up
Change settings to Break on Unhandled errors to troubleshoot
by Henderson Jayden Harper
Passionate about technology, Crypto, software, Windows, and everything computer-related, he spends most of his time developing new skills and learning more about the tech world. He also enjoys… read more
Updated on
- The Runtime error 5 is a VBA error and it usually appears in Excel.
- Wrongfully disabling the error handler is one of the major causes of this error.
- Try changing settings to Break on Unhandled errors in VBA to solve this problem.
XINSTALL BY CLICKING THE DOWNLOAD FILE
To fix Windows PC system issues, you will need a dedicated tool
SPONSORED
Fortect is a tool that does not simply clean up your PC, but has a repository with several millions of Windows System files stored in their initial version. When your PC encounters a problem, Fortect will fix it for you, by replacing bad files with fresh versions. To fix your current PC issue, here are the steps you need to take:
- Download Fortect and install it on your PC.
- Start the tool’s scanning process to look for corrupt files that are the source of your problem
- Right-click on Start Repair so the tool could start the fixing algorythm
- Fortect has been downloaded by 0 readers this month, rated 4.4 on TrustPilot
The Runtime error 5: Invalid procedure call or argument error appears when changing the location of the sysdata directory to a shared directory on the network. It occurs when the change is from the Change SYSDATA Directory dialog box of Microsoft FRx. However, this guide will discuss how to fix it.
Alternatively, you may be interested in our guide about resolving the Runtime error: could not call proc on Windows PCs.
What causes Runtime error 5?
- Insufficient permissions with Microsoft FRx can cause issues with access or write commands
- An invalid network path in the SYSDATA Directory dialog box can cause this issue.
- Network connectivity issues are a common culprit for this problem.
- Wrongfully disabling the error handler will sometimes cause problems.
How do I fix Runtime error 5?
Before anything else, apply the following checks:
- Use DateDiff when working with loops.
- Prevent the user from seeing the sheet during a time by setting
Application.ScreenUpdating = Falserather than useExportAsFixedFormat
1. Change settings to Break on Unhandled errors
- Launch Excel on your PC and click the Developer tab.
- Then, click on Visual Basic. Alternatively, you can press Alt + F11.
- Click on the Tools tab and click on Options.
- Now, select the General tab, and under Error trapping, select Break on Unhandled Errors.
If the wrongful setting of the error handler is the issue, this should troubleshoot the runtime error and fix it permanently.
- Open your code.
- Locate the line where you call the destination sheet.
- If a destination sheet has a space in its name, surround it with single quotes, like this:
TableDestination:="'" & myDestinationWorksheet.Name & "'!" & myDestinationRange
Users have also confirmed this as a viable solution for this error.
- Fix: Touchpad Clicks but Doesn’t Move on Windows 11
- Fix: No Internet, Secured Error on Windows 11
Further, should you encounter a similar issue, such as the Runtime error 3709, do not hesitate to peruse our guide to troubleshoot it immediately.
Likewise, our guide on fixing Python Runtime error can also be useful if you encounter such a problem.
Should you have further questions or suggestions, kindly drop them in the comments section.
