vba - 使用 VBA 将值设置为 .Function

Question

我目前设置了一个函数，可以将选定数据透视表的所有值更改为平均值。

它工作得很好，我已经组装了一个传递值的表单，该值也工作得很好。

此时我想做的是让它决定将值转变为什么。

但是，我不断收到类型不匹配错误。这是因为它被作为字符串读取。我该如何进行调整？

Private Sub CommandButton1_Click()
  MsgBox xl & ListBox1.Value
  Dim ptf As Excel.PivotField
  With Selection.PivotTable
    .ManualUpdate = True
    For Each ptf In .DataFields
      With ptf
        .Function = "xl" & ListBox1.Value 'xlAverage works here
        .NumberFormat = "#,##0"
      End With
    Next ptf
    .ManualUpdate = False
  End With
End Sub

Private Sub ListBox1_Click()
End Sub

Private Sub UserForm_Click()
End Sub        

Private Sub UserForm_Initialize() 'Set Values Upon Opening       
  With ListBox1
    .AddItem "Sum"
    .AddItem "Count"
    .AddItem "Average"
    .AddItem "Max"
    .AddItem "Min"
    .AddItem "Product"
    .AddItem "CountNumbers"
    .AddItem "StdDev"
    .AddItem "StdDevp"
    .AddItem "Var"
    .AddItem "Varp"
  End With
End Sub

Answer 1

当然，编译时的变量名称，例如xlAverage，不一定会报告给运行时，它只是一个整数常量。编译后，常量名称消失了。

有很多解决这个问题的方法，但它们可能从复杂的类型库使用(而且并非在所有平台上可用)到我的相对简单的解决方案下面建议，它使用字典来跟踪常量名称与其值之间的映射。

Private Sub CommandButton1_Click()
    ' ...
    ptf.Function = GetEnumConsolidationFunction.item(ListBox1.Value)
    ' ...
End Sub

Private Sub UserForm_Initialize()
    Dim dict As Object, s
    Set dict = GetEnumConsolidationFunction
    For Each s In dict.Keys
        ListBox1.AddItem s
    Next
End Sub

' Our key function, fills a dictionary first time it is used
Function GetEnumConsolidationFunction() As Object
    Static dict As Object '<-- static because we want to fill it only once
    If dict Is Nothing Then
        Set dict = CreateObject("Scripting.Dictionary")
        With dict
            .Add "Sum", XlConsolidationFunction.xlSum
            .Add "Count", XlConsolidationFunction.xlCount
            .Add "Average", XlConsolidationFunction.xlAverage
            .Add "Max", XlConsolidationFunction.xlMax
            .Add "Min", XlConsolidationFunction.xlMin
            .Add "Product", XlConsolidationFunction.xlProduct
            .Add "CountNums", XlConsolidationFunction.xlCountNums
            .Add "StDev", XlConsolidationFunction.xlStDev
            .Add "StDevp", XlConsolidationFunction.xlStDevP
            .Add "Var", XlConsolidationFunction.xlVar
            .Add "Varp", XlConsolidationFunction.xlVarP
        End With
    End If
    Set GetEnumConsolidationFunction = dict
End Function

顺便说一句，在这种方法中，您不必映射与变量相同的名称。您可以自由映射任何您想要在列表框中显示的名称；即“最小”、“标准偏差”等。

附注

请注意，您的名称中有一些拼写错误:

CountNumbers --> CountNums

StdDev --> StDev

StdDevP --> StdDevP