使用VBA将值设置为.Function-Settingvalueto.FunctionusingVBA

3  

Of course, the name of a variable at compile time, such as xlAverage, is not necessarily reported to the run-time, where it is simply an integer constant. After compilation, the constants names are gone.

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

There are many ways around this, but they might go from the complicated use of type libraries which moreover are not available on all platforms, to the relatively simple solution I suggest below, which uses a dictionary to track the mappings between the names of the constants and their values.

有很多方法可以解决这个问题，但是它们可能会从复杂的类型库中使用，而这些类型库在所有平台上都不可用，而是我在下面建议的相对简单的解决方案，它使用字典来跟踪常量名称之间的映射。和他们的价值观。

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

By the way, in this method you are not obliged to map the same names as of the variables. You are free map any names you want to display in the list box; i.e. "Mimimum", "Standard Deviation", etc..

顺便说一句，在这种方法中，您没有义务映射与变量相同的名称。您可以自由映射要在列表框中显示的任何名称;即“Mimimum”，“Standard Deviation”等。

p.s.

附：

Please be aware that you had some typos in the names:

请注意，您的名字中有一些拼写错误：

CountNumbers --> CountNums

CountNumbers - > CountNums

StdDev --> StDev

StdDev - > StDev

StdDevp --> StDevP

StdDevp - > StDevP

0  

Your attempt doesn't work because you need to pass the constant values, not strings. xlSum, for example, is -4157. You can work out what each is as follows:

您的尝试不起作用，因为您需要传递常量值，而不是字符串。例如，xlSum是-4157。你可以弄清楚每个是如下：

debug.print clng(xlSum)

This should work for you:

这应该适合你：

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

0  

You could create function to return the value of the names, like:

您可以创建函数来返回名称的值，例如：

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

The change assignment:

变更分配：

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