使用VBA将值设置为.Function

Question

目前，我已经设置了一个函数集，它将将选定的枢轴表的所有值更改为平均值。

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

我现在要做的是让它决定如何将这些值转换为什么。

但是，我仍然会得到一个Type-Mismatch错误。这是因为它被读取为一个字符串。我怎么能继续调整这个呢？

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

顺便说一句，在这种方法中，您不必映射与变量相同的名称。您可以在列表框中显示任意名称，即"Mimimum", "Standard Deviation", etc..。

p.s.

请注意，您在姓名中有一些排印：

CountNumbers -> CountNums

StdDev -> StDev

StdDevp -> StDevP

Answer 2

您的尝试不起作用，因为您需要传递常量值，而不是字符串。例如，xlSum是-4157。您可以按以下方式计算出每一项内容：

debug.print clng(xlSum)

这应该适用于你：

Private Sub CommandButton1_Click()
'Define Constants
Const C_SUM As Long = -4157
Const C_COUNT As Long = -4112
Const C_AVERAGE As Long = -4106
Dim ptf As Excel.PivotField

  With Selection.PivotTable
    .ManualUpdate = True
    For Each ptf In .DataFields
      With ptf
        Select Case ListBox1.Value
            Case "Sum"
                .Function = C_SUM
            Case "Count"
                .Function = C_COUNT
            Case "Average"
                .Function = C_AVERAGE
        End Select
        .NumberFormat = "#,##0"
      End With
    Next ptf
    .ManualUpdate = False
  End With

End Sub

Answer 3

可以创建函数来返回名称的值，如下所示：

Private Function GetFunctionValue(ByVal FunctionName As String) As Long
    Dim value As Long

    Select Case FunctionName
    Case "Sum"
        value = xlSum
    Case "Count"
        value = xlCount
    Case "Average"
        value = xlAverage
    Case "Max"
        value = xlMax
    Case "Min"
        value = xlMin
    Case "Product"
        value = xlProduct
    Case "CountNums"
        value = xlCountNums
    Case "StDev"
        value = xlStDev
    Case "StDevp"
        value = xlStDevP
    Case "Var"
        value = xlVar
    Case "Varp"
        value = xlVarP
    End Select

    GetFunctionValue = value
End Function

变更分配：

  With ptf
    .Function = GetFunctionValue(ListBox1.Value)
    .NumberFormat = "#,##0"
  End With