编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

使用VBA定义数据透视缓存时,运行时错误'5'无效的过程调用或参数:Run-time error '5' Invalid procedure call or argument when defining Pivot Cache using VBA

That's Enam 数据库/缓存 2022-5-11 10:34 9人围观

腾讯云服务器
使用VBA定义数据透视缓存时,运行时错误'5'无效的过程调用或参数的处理方法

在我的代码上定义PivotCache时,我发现此问题运行时错误'5'无效的过程调用或参数".我正在使用的Excel是2016 MSO(16.0.4266.1001)32位.VBA 7.

I am finding this issue "Run-time error '5' Invalid procedure call or argument" when defining the PivotCache on my code. The Excel I am using is 2016 MSO (16.0.4266.1001) 32 bit. VBA 7.

'Declare Variables Dim PSheet As Worksheet Dim DSheet As Worksheet Dim PCache As PivotCache Dim PTable As PivotTable Dim PRange As Range Dim LastRow As Long Dim LastCol As Long 'Sort Age of Case LastRow = Cells(Rows.Count, 2).End(xlUp).Row Range("M" & LastRow).Sort Key1:=Range("M3:M" & LastRow), _ Order1:=xlAscending, Header:=xlYes 'Insert a New Blank Worksheet On Error Resume Next Application.DisplayAlerts = False Worksheets.Add(After:=Worksheets(1)).Name = "US MASTER" Application.DisplayAlerts = True Set PSheet = Worksheets("US MASTER") Set DSheet = Worksheets("US Master Macro") 'Define Data Range LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row ' 'Pivot Table 1 ' 'Define Pivot Cache Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRange) 'Insert Blank Pivot Table Set PTable = PCache.CreatePivotTable _ (TableDestination:=PSheet.Range("B3"), TableName:="Total Backlog") 

问题解答

创建 PivotCache 时,

When creating a PivotCache, MSDN recommends using a String reference to specify the workbook, worksheet and cell range for SourceData, instead of passing a Range object.

代替使用 PRange (您在这里忘记了 Set ),可以使用R1C1表示法,例如

Instead of using PRange (which you've forgotten to Set here), you can use R1C1 notation, like this answer suggests.

LastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column ... SourceData:="'US Master Macro'!R1C1:R" & LastRow & "C" & LastCol 

这篇关于使用VBA定义数据透视缓存时,运行时错误'5'无效的过程调用或参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^