Convert string to function

[jnvm90]

Hi everyone

I am trying to write a userform where I can change the data field functions for all fields in my pitvot table but I have a problem because I want to pass a function to another function. However I do not know how to convert a string to the function name passed to the second function.

You might understand my problem by studying the two codes below.

The following function changes all data fields in the pivot table.

Sub ChangeAllFields(func As Variant)
  Dim pt As PivotTable
  Dim pf As PivotField
  Set pt = ActiveCell.PivotTable
  Application.ScreenUpdating = False
    pt.ManualUpdate = True
    For Each pf In pt.DataFields
      pf.Function = func
    Next pf
    pt.ManualUpdate = False
  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
End Sub

The following function should pass the pivot function to the above function and execute the command

Private Sub CommandButton1_Click()
    Dim text As String
    text = "xl" & ListBox2.value
    ChangeAllFields (text)
    Unload Me
End Sub

Where Listbox2 contain string values such as sum or average.


I am probably looking for something like the following Matlab function:

Construct function handle from function name string - MATLAB str2func - MathWorks Nordic

 

[Norie]

What does the first code look like with a hard-coded function?

 

[jnvm90]

A hard coded version, which would only be able to turn the data fields into averages it would look like this:

Sub ChangeAllFields()  

Dim pt As PivotTable  
Dim pf As PivotField  
Set pt = ActiveCell.PivotTable  
Application.ScreenUpdating = False   
pt.ManualUpdate = True    
For Each pf In pt.DataFields      
       pf.Function = [COLOR=#ff0000]xlAverage[/COLOR]    
Next pf    
pt.ManualUpdate = False  
Application.ScreenUpdating = True  
Set pf = Nothing  
Set pt = Nothing
End Sub

 

[Norie]

xlAverage is a VBA (long integer ) constant, it's not a string or a function.

What you need to do is use code to get the relevant constant based on the selection in the combobox.

Something like this perhaps.

Select Case ListBox2.Value 
    Case "Average"
         func = xlAverage
    Case "Sum"
         func = xlSum
     ' cases for other functions 
End Select

PS That code is totally untested but hopefully should give you an idea what I mean.

 

[dmt32]

However I do not know how to convert a string to the function name passed to the second function.

Hi,
The xlConstants you want to pass are not Strings - they may look like strings but all have an Integer value. For example
xlAverage returns -4106 which is what you need to pass to your procedures argument.

See if this update to your two codes helps:

Sub ChangeAllFields(ByVal func As Integer)
  Dim pt As PivotTable
  Dim pf As PivotField
  Set pt = ActiveCell.PivotTable
  Application.ScreenUpdating = False
    pt.ManualUpdate = True
    
    For Each pf In pt.DataFields
      pf.Function = func
    Next pf
    pt.ManualUpdate = False
  
  Application.ScreenUpdating = True
  Set pf = Nothing
  Set pt = Nothing
End Sub

Private Sub CommandButton1_Click()
    Dim i As Integer
    Dim xlconst As Variant

    'add all required xl constants in correct order to match your listbox
    'remember, these are all numeric values, do not place quotes ("") around them.
    xlconst = Array(xlAverage, xlCount, xlMax)
    
    i = (Me.ListBox1.ListIndex)

    ChangeAllFields xlconst(i)
    Unload Me
End Sub

Hope helpful

Dave