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

score 1 · Accepted Answer · answered Apr 19 '17 at 04:00

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.

Create a copy of the original PivotTable with nothing in it but the field that the Slicer controls.
Drag that field to the Report Filters pane in the PivotTable Fields dialog.
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/

Thanks for the quick reply. I should have added that the slicer I use points to a Table, not a PivotTable. I thus cannot report a connection, unfortunately. I need the Table to stay a Table because the user must be able to make changes to the data. — Robin Kramer-ten Have, Apr 19 '17 at 06:17

score 0 · Answer 2 · answered Apr 19 '17 at 11:45

I tried a completely different approach with which I do not need the function to extract the value from the slicer making the volatile redundant. Instead, I deducted the chosen value from the table. I did that as follows:

The slicer selected based on a day as string: monday, tuesday etc. I added a column with integer representation of the days (monday = 1, sunday = 7). Then, I used the following formula in cell:

=SUBTOTAL(9;AF10:AF200)/SUBTOTAL(2;AF10:AF200)

This piece of code first sums the integer values of all the visible cells, which is divided by the amount of visible cells (i.e. I calculate the average). This should always result in the integer value of the selected day. With that number, I again find the string representation of the day.

Instead of the formula above, you can of course also use:

=SUBTOTAL(1;AF10:AF200)

Refresh User-defined function without application.volatile

2 Answers2