Refresh User-defined function without application.volatile

Question

I am using a function that I found on the internet to extract the value of a slicer (that is connected to a Table, not a PivotTable) and store it in a cell. The function included the addition of application.volatile which worked nicely, as discussed also in the question Refresh Excel VBA Function Results .

The problem is, because of the volatile function, my OpenSolver model is unable to run. Because with every iteration Excel is doing a calculation, which makes OpenSolver think that Excel is not ready for the modelling.

Is there a method that updates the value of the cell each time the slicer changes value, without using application.volatile?

I already tried using:

Private Sub Worksheet_Change(ByVal Target As Range)
    ActiveWorkbook.Sheets("Dashboard").Range("B7").Formula = _
        ActiveWorkbook.Sheets("Dashboard").Range("B7").Formula
End Sub

and

Private Sub Worksheet_Change(ByVal Target As Range)
        ActiveWorkbook.Sheets("Dashboard").Range("B7").Calculate
End Sub

---

The function that I am using to extract the slicer value is taken from http://www.jkp-ads.com/articles/slicers05.asp :

Public Function GetSelectedSlicerItems(SlicerName As String) As String
    Dim oSc As SlicerCache
    Dim oSi As SlicerItem
    Dim lCt As Long
    On Error Resume Next
    Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
    If Not oSc Is Nothing Then
        For Each oSi In oSc.SlicerItems
            If oSi.Selected Then
                GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
                lCt = lCt + 1
            End If
        Next
        If Len(GetSelectedSlicerItems) > 0 Then
            If lCt = oSc.SlicerItems.Count Then
                GetSelectedSlicerItems = "maandag"
            Else
                GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
            End If
        Else
            GetSelectedSlicerItems = "No items selected"
        End If
    Else
        GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
    End If
End Function

Answer 1

If you want the value of the slicer to appear in a cell, there is another approach you can use that doesn't involve VBA, which might indirectly solve your problem.

1. Create a copy of the original PivotTable with nothing in it but the field that the Slicer controls.
2. Drag that field to the Report Filters pane in the PivotTable Fields dialog.
3. Connect the Slicer to this field in this new PivotTable

Great: Now you have a PivotTable masquerading as a Data Validation Dropdown. Now, any time someone clicks on the Slicer, that PivotTable Filter will contain the name of the thing they clicked on.

I wrote a post that explains this approach sometime back at the following link:

http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/