CODEWITHSUNDEEP
vbaexcelfunctionslicers
Robin Kramer-ten Have
Robin Kramer-ten Have

Reputation: 888

Refresh User-defined function without application.volatile

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

Upvotes: 1

Views: 397

Answers (2)

Robin Kramer-ten Have
Robin Kramer-ten Have

Reputation: 888

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)

Upvotes: 0

jeffreyweir
jeffreyweir

Reputation: 4834

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/

Upvotes: 1

Related Questions