使用常量值更新VBA中的数据透视表周数

Question

我们试图让Excel基于两个单元格中的用户输入来更新数据透视表的值。它应该更新代码中写着2016014和2017014的部分。(014是周数)。我们指示用户在单元格AD和AE中使用YYYYWWW格式输入周数(2016年和2017年分别输入一个)。如何让Excel从这些输入中读取值？

我们会申报这样的东西吗？唯一的问题是我得到了

子或函数中的无效属性

        Public Const weekNumber1 As String = ThisWorkbook.Sheets(Charts).Cells(30, 4).Value
        Public Const weekNumber2 As String = ThisWorkbook.Sheets(Charts).Cells(31, 4).Value

以下是实际代码：

Sub WeekUpdate(Optional ByVal ws As Worksheet)
                        If ws Is Nothing Then Set ws = ActiveSheet
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Year]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Qtr]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Period]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[2016014]")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Date]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Year]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Qtr]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Period]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[2017014]")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Date]").VisibleItemsList = Array("")

下面是代码的第二部分(它称为第一部分)：

End Sub

Sub DoSheets()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Charts" Then
        WeekUpdate ws
        ws.Cells(1, 1) = 1
    End If
Next

starting_ws.Activate

End Sub

更新代码

Public Function weekNumber1() As String
    weekNumber1 = ThisWorkbook.Sheets("Charts").Cells(4, 30).Value
End Function
Public Function weekNumber2() As String
    weekNumber2 = ThisWorkbook.Sheets("Charts").Cells(5, 30).Value
End Function


Sub WeekUpdate(Optional ByVal ws As Worksheet)
            If ws Is Nothing Then Set ws = ActiveSheet
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Year]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Qtr]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Period]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[weekNumber1()]")
            ws.PivotTables("PivotTable6").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Date]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Year]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Qtr]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Period]").VisibleItemsList = Array("")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
                "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[weekNumber2()]")
            ws.PivotTables("PivotTable5").PivotFields( _
                "[Date].[Fiscal Date Hierarchy].[Date]").VisibleItemsList = Array("")
End Sub


Sub DoSheets()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = ActiveSheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Charts" Then
        WeekUpdate ws
        ws.Cells(1, 1) = 1
    End If
Next

starting_ws.Activate

End Sub

Answer 1

由于不可能用可变值声明常数，一种解决方案是有一个公共函数--没有参数--提供所需的值，例如：

Public Function weekNumber1() As String
    weekNumber1 = ThisWorkbook.Sheets("Charts").Cells(30, 4).Value
End Function

Public Function weekNumber2() As String
    weekNumber2 = ThisWorkbook.Sheets("Charts").Cells(31, 4).Value
End Function

您可以在代码中调用/使用它，就像使用常量一样。

由于您使用的是字符串中的值，因此应该更改以下内容：

ws.PivotTables("PivotTable5").PivotFields( _
    "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
    "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[weekNumber2()]")

对此：

ws.PivotTables("PivotTable5").PivotFields( _
    "[Date].[Fiscal Date Hierarchy].[Fiscal Week]").VisibleItemsList = Array( _
    "[Date].[Fiscal Date Hierarchy].[Fiscal Week].&[" & weekNumber2() & "]")

您还应该更新引用weekNumber1()的字符串

编辑解释

你需要提供它想要的格式的刺。但是，要调用字符串中的函数(或使用变量‘值)，需要将字符串的各个部分附加到一起。如果将函数(或变量)名称作为字符串的一部分，则VBA将使用包含的文字文本，而不是替换正确的值。

编辑的

将Charts更改为"Charts"，因为Charts是工作表名称