Permalink
Cannot retrieve contributors at this time
| title | keywords | f1_keywords | ms.prod | ms.assetid | ms.date | ms.localizationpriority |
|---|---|---|---|---|---|---|
|
Invalid procedure call or argument (Error 5) |
vblr6.chm1000005 |
vblr6.chm1000005 |
office |
481b8431-b4ba-b368-2c5e-ade85b99348d |
06/08/2017 |
medium |
Some part of the call can’t be completed. This error has the following causes and solutions:
-
An argument probably exceeds the range of permitted values. For example, the Sin function can only accept values within a certain range. Positive arguments less than 2,147,483,648 are accepted, while 2,147,483,648 generates this error.
Check the ranges permitted for arguments.
-
This error can also occur if an attempt is made to call a procedure that isn’t valid on the current platform. For example, some procedures may only be valid for Microsoft Windows, or for the Macintosh, and so on.
Check platform-specific information about the procedure.
For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).
[!includeSupport and feedback]
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.1k11 gold badges86 silver badges138 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,9602 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
4,98810 gold badges37 silver badges64 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
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)
Return to VBA Code Examples
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").SelectThe 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.AddExcel 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 SubIf 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 SubNow 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.
Public Sub Test()
ThisWorkbook.Worksheets(«sheet1»).ExportAsFixedFormat Type:=xlTypePDF
End Sub
This code generates error
Run time error 5
Invalid procedure call or argument
—-Please let me know if someone knows the solution.—-
Office Product: MS office professional plus 2013
Operating system: Windows 7 enterprise -Service Pack 1
I have tried all following steps, but error still persists
1) Installing PDF com addin. It just creates Acrobat tab in ribbon.
2) Repairing/Reinstalling PDF application
3) Repair/Reinstall office
4) Reinstall PDF printer Driver
Note: I do not see PDF options in my Excel (I have XPS), in the list of ‘SAVE AS’ file types when you try to save an EXCEL file.
Surprisingly the same code works on one of our vendors laptop and we can not figure out why.
Any help greatly appreciated
Thanks
NJ