不使用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 .

我正在使用在internet上找到的一个函数来提取切片器的值(它连接到一个表，而不是数据透视表)并将其存储在一个单元格中。功能包括添加应用程序。它工作得很好，正如问题Refresh Excel VBA函数结果中所讨论的。

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.

问题是，由于volatile函数，我的OpenSolver模型无法运行。因为每次迭代，Excel都在做计算，这使得OpenSolver认为Excel还没有准备好进行建模。

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

是否有一种方法在每次切片器更改值时更新单元格的值，而不使用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 :

我用来提取切片值的函数是从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

2 个解决方案

#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.

如果您希望切片器的值出现在单元中，您可以使用另一种不涉及VBA的方法，这可能间接地解决您的问题。

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/

#2

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:

我尝试了一种完全不同的方法，不需要函数从使volatile冗余的切片器中提取值。相反，我从表中扣除了所选的值。我是这样做的:

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:

基于一天选择的切片器为string: monday, tuesday等，我添加了一个具有天数整数表示的列(monday = 1, sunday = 7)，然后在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)

#1