Setting value to .Function using VBA

Question

I currently have a function set in place which will change all values of a selected pivot table to average.

It works fine, and I have assembled a form which passes a value which works fine as well.

What I would like to do at this point is make it so that it decides what to turn the values to.

I however, keep getting a Type-Mismatch error. This is because it is being read as a string. How could I go along of adjusting this?

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

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.

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..

p.s.

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

CountNumbers --> CountNums

StdDev --> StDev

StdDevp --> StDevP