excel - 通过 VBA 函数插入 GetPivotData 公式

Question

我正在尝试将 GetPivotData 公式插入到某些单元格中。
最多有 7 个数据透视表来获取数据，所以我需要动态地进行:

Function Formula(KPI As String, FilaTD As Integer, ColumnaTD As Integer, Filtro1 As String, _
    Simple As Boolean, Optional Filtro2 As String)

    If Simple = True Then
        Formula = "IFERROR(GETPIVOTDATA(" & Comillas & KPI & Comillas & ",'TDReport Control Desk'!R" & _
        FilaTD & "C" & ColumnaTD & "," & Filtro1 & ",RC2),0)"
    Else
        Formula = "IFERROR(GETPIVOTDATA(" & Comillas & KPI & Comillas & ",'TDReport Control Desk'!R" & _
        FilaTD & "C" & ColumnaTD & "," & Filtro1 & ",RC2),GETPIVOTDATA(" & Comillas & KPI & Comillas & _
        ",'TDReport Control Desk'!R" & FilaTD & "C" & ColumnaTD & "," & Filtro2 & ",RC2))"
    End If

End Function

名为“Comillas”的字符串只是一个常量:

Public Const Comillas As String = """"""

我这样称呼这个函数:

FormulaPrevisionAtenMesCentro = Formula(KPI2, FilaTDOffCentro, ColumnaTDOffCentro, "Centro", True)

FormulaPrevisionAtenDiaCentro = Formula(KPI2, _
FilaTDOffDiaCentro, ColumnaTDOffDiaCentro, "Centro", True)

FormulaPrevisionAtenMes = Formula(KPI2, _
FilaTDOffMes, ColumnaTDOffMes, "Modo2", False, "Modo3")

FormulaPrevisionAtenDia = Formula(KPI2, _
FilaTDOffDia, ColumnaTDOffDia, "Modo2", False, "Modo3")

FormulaKPIMes = Formula(KPI, _
FilaTDOffMes, ColumnaTDOffMes, "Modo2", False, "Modo3")

FormulaKPIMesCentro = Formula(KPI, FilaTDOffCentro, ColumnaTDOffCentro, "Centro", True)

FormulaKPIDia = Formula(KPI, _
FilaTDOffDia, ColumnaTDOffDia, "Modo2", False, "Modo3")

FormulaKPIDiaCentro = Formula(KPI, FilaTDOffDiaCentro, ColumnaTDOffDiaCentro, "Centro", True)

我的问题出在 Comillas 的事情上。如果我使用它，它将引发 VBA 错误。如果我不使用它，公式将被插入但不起作用，因为我要求的字段不会介于“”之间。

Answer 1

它应该是:

Public Const Comillas As String = """"

因为您只希望公式在值的每一侧包含一个单引号。您只需在将文字引用字符串放入公式字符串时将它们加倍，而不是在将其连接时。