excel - 运行时公式错误 1004 swaformula

Question

我收到一条公式错误消息:运行时错误 1004，代码中公式第二次出现；有问题的代码部分如下，为了快速引用，我粘贴了下面的两个公式，第一个公式运行正常，第二个公式给出了我无法弄清楚的错误。

ActiveWorkbook.Names.Add Name:="pivotsourceFGPO", RefersToR1C1:=swaFormula (no errors when running the macro)

 ActiveWorkbook.Names.Add Name:="pivotsourceorderbase", RefersToR1C1:=swaFormula (FORMULA giving error)

代码:(部分)

swaFormula = "=OFFSET(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & "C" & ActiveCell.Column & ",0,0,COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!C" & ActiveCell.Column & ") -" & swarow & ",COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & ")-" & swacol & ")"

ActiveWorkbook.Names.Add Name:="pivotsourceFGPO", RefersToR1C1:=swaFormula
Dim PTCache As PivotCache
Dim pt As PivotTable
Dim WS As Worksheet
Dim pvtfld As PivotField

' define the range which serves as the data range

Set WS = Sheets.Add ' add new sheet to hold the pivot table
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,  SourceData:="=pivotsourceFGPO")

' Create the pivot table
Set pt = PTCache.CreatePivotTable(TableDestination:=WS.Range("A1"), TableName:="FGPOPivot")

ActiveWorkbook.ShowPivotTableFieldList = True

pt.PivotFields("Key").Orientation = xlRowField
pt.PivotFields("Key").Position = 1
pt.PivotFields("MTL").Orientation = xlRowField
pt.PivotFields("MTL").Position = 2
pt.PivotFields("Size Code").Orientation = xlRowField
pt.PivotFields("Size Code").Position = 3
pt.PivotFields("Week").Orientation = xlColumnField
pt.PivotFields("Week").Position = 1
pt.AddDataField pt.PivotFields("Wip Qty"), "Sum of Wip Qty", xlSum

'Order base 1 pivot
Sheets("Order base (1)").Select
Range("A1").Select
swarow = ActiveCell.Row - 1
swacol = ActiveCell.Column - 1

' make it comma and not colon
swaFormula = "=OFFSET(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & "C" & ActiveCell.Column & ",0,0,COUNTA(" & _

ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!C" & ActiveCell.Column & ") -" & swarow & ",COUNTA(" & _
ActiveWorkbook.Worksheets(ActiveSheet.Index).Name & "!R" & ActiveCell.Row & ")-" & swacol & ")"

ActiveWorkbook.Names.Add Name:="pivotsourceorderbase", RefersToR1C1:=swaFormula (FORMULA giving error)

Answer 1

您正在依赖 ActiveCell property 。有比这更好的方法来引用单元格，但是如果可以暂时忽略这些“最佳实践”，那么 ActiveCell 知道它的.Parent工作表和工作簿是。

此外，如果未提供实际的工作表名称，则无法保证 ActiveSheet的.Name property不包含空格，可能需要刻度线(例如 ')来将其换行。要么理所当然地添加这些，要么添加 Range.Address property应用于生成单元格区域的字符串。

非 volatile INDEX function可以想象可以取代 volatile² OFFSET function但我对数据的布局了解不够，无法提出可靠的替代公式。

Dim swaFormula As String, rngName As String
Dim swaRow As Long, swaCol As Long

rngName = "pivotSourceOrderBase"
swaRow = 0: swaCol = 0

With ActiveCell
    swaFormula = "=OFFSET(" & _
                    .Cells(1).Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ", 0, 0, COUNTA(" & _
                    .Cells(1).EntireColumn.Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ")-" & swaRow & ", COUNTA(" & _
                    .Cells(1).EntireRow.Address(external:=True, ReferenceStyle:=xlR1C1) & _
                    ")-" & swaCol & ")"
    Debug.Print swaFormula  '<~~ check the formula in the Immediate window
    On Error Resume Next    'may be needed for the next line
    .Parent.Parent.Names(rngName).Delete
    On Error GoTo 0
    .Parent.Names.Add Name:=rngName, RefersToR1C1:=swaFormula
End With

仔细检查一下 VBE 的 Immediate window 中的公式在尝试将其应用于命名范围创建之前。

<小时/>

1 _{参见 How to avoid using Select in Excel VBA macros了解更多摆脱依赖 select 和 activate 来实现目标的方法。}

² _{每当整个工作簿中的任何内容发生变化时， volatile 函数都会重新计算，而不仅仅是影响其结果的内容发生变化时。 volatile 函数的示例有 INDIRECT , OFFSET , TODAY , NOW , RAND和 RANDBETWEEN 。 CELL的一些子功能和 INFO工作表函数也会使它们不稳定。}