vba 运行时错误 5 - 无效的过程调用或参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30538131/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run Time Error 5 - Invalid Procedure Call or Argument
提问by user3067028
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
回答by Byron Wall
The answer to your problem is located here.
您的问题的答案位于此处。
Your sheet name in TableDestination:="LED OTTR!R1C1"needs to be surrounded with single quotesin order for it to work TableDestination:="'LED OTTR'!R1C1"
您的工作表名称TableDestination:="LED OTTR!R1C1"需要用单引号括起来才能工作TableDestination:="'LED OTTR'!R1C1"
You will also have problems with the duplicated name if you do not delete this PivotTable before rerunning the code.
如果在重新运行代码之前不删除此数据透视表，您也会遇到重复名称的问题。
回答by n1000
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.
就我而言，问题与 Windows 中的区域设置有关。我从互联网下载了一个（受保护的）xlsm 文件，打开它时总是出现“运行时错误 5 - 无效的过程调用或参数”错误。另一个答案向我暗示它可能与语言设置有关。文件中的脚本显然是用德语编写的，而我的 Windows 设置为英语区域。
In Windows 10 Settings > Time & Language > RegionI changed Regionas well as Regional formatto German. After restarting Excel, the file worked.
在 Windows 10 中，Settings > Time & Language > Region我更改Region为Regional format德语。重新启动 Excel 后，该文件有效。
