CODEWITHSUNDEEP
vbaexcelpivot-table
Brad
Brad

Reputation: 1480

Setting value to .Function using VBA

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

Upvotes: 9

Views: 1138

Answers (3)

A.S.H
A.S.H

Reputation: 29332

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

Upvotes: 4

Gordon Bell
Gordon Bell

Reputation: 13633

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

Upvotes: 0

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

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:

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

Upvotes: 0

Related Questions