防止在命名范围内使用的用户定义函数的重新计算
我有三个UDF：
Private Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function这个函数检查数组中是否有什么东西。
Private Function data_to_array(data As Range)
Dim arrArray As Variant
Dim cell As Range
Dim z As Integer
z = 0
ReDim arrArray(1 To data.Cells.Count)
For Each cell In data
z = z + 1
arrArray(z) = cell.Value
Next cell
data_to_array = arrArray
End Function此函数提取选定的范围值并将其放入数组中。
Private Function plot_vals(data As Variant, custom_arr As Variant)
Dim arrPlot As Variant
ReDim arrPlot(1 To UBound(data)) As Variant
Dim c As Integer
Dim cl As Integer
cl = 0
For c = 1 To UBound(data)
cl = cl + 1
If IsInArray(cl, custom_arr) Then
arrPlot(cl) = data(cl)
Else
arrPlot(cl) = CVErr(xlErrNA)
End If
Next c
plot_vals = arrPlot
End Function最后一个UDF循环第二个UDF中的数据数组，如果data_array中的索引/值位置在custom_array中，则返回它的值。否则，它会将一个错误放入数组中。
数据如下：
这些函数在Excel中的用法如下：
data_to_array(A1:A5) -这个UDF创建一个数组(1到5)，数组的值来自单元格A1:A5。
plot_vals(data_to_array(A1:A5), {1,5}) -这个UDF创建一个数组(1到5)，并使用第二个参数检索第一个和第五个值，同时在其他索引中放置错误。结果是数组，例如：{5,error,error,error,1}
如果我像这样对上面的数据使用这个函数：plot_vals(data_to_array(A1:A5), {1,2})，那么结果将是一个数组{5,4,error,error,error}
plot_vals UDF用于命名范围，命名范围用于绘制图表上的值。数据存储在命名范围myData中，第二个命名范围中的函数使用如下：plot_vals(myData,{1,5})。
一切正常工作，我可以在图表上绘制它，一切都很好，但是当在图表上使用命名范围时，每次我更改工作簿中的内容时，所有函数都会被重新计算，就像.每一个 10次，而不是一次。如果多次使用这些函数，则会导致Excel减速/冻结。我已经尝试过关于函数波动以及如何关闭它(默认情况下应该关闭它？)，但是似乎没有什么起作用的，我不知道如何阻止它的发生。我尝试使用命名范围内的标准Excel函数在Excel中重新创建此函数，但是我无法找到一个正确的函数来完成我想做的事情。UDF正是我所需要的。
当这些命名范围没有在图表中使用时，什么都不会发生，但是一旦我在图表上使用命名范围，它就会重新计算工作簿的所有内容。小改动的意思-复制/粘贴/添加行等。
我怎么才能阻止这一切的发生？如何重新计算UDF的一次？
关于进一步研究的编辑
我已经尝试过由Charles：https://fastexcel.wordpress.com/2011/11/25/writing-efficient-vba-udfs-part-7-udfs-calculated-multiple-times/提供的潜在解决方案。
他的潜在解决方案并没有改变任何事情。
我还尝试使用Sheet_Change事件，将计算更改为手动，然后再改为自动计算。它有助于清除剪贴板(不可接受)，它会引起我的其他宏的问题，因此它是一个“禁止”的解决方案。
值得注意的是，一旦删除了图表，并且UDF的名称保持在指定范围内，一切都进行得很顺利。但是，当那些命名的范围是在图表系列公式，一切都是重新计算100次。
Stack Overflow用户
发布于 2020-05-30 18:53:20
除了尝试修复函数被调用多少次这一基本问题之外，您还可以通过优化基本性能来部分解决慢速问题：
Application.Match的速度相对较慢，除非在worksheet- Reading上搜索到的数据--范围为数组--比使用其
.Value(假设范围是单个区域)的
同时读取整个范围要慢。
所以：
Sub PerfTester()
Const ARR_SZ As Long = 10
Dim arr(1 To ARR_SZ), i, n, t, v, m
'populate a test array
For i = 1 To ARR_SZ
arr(i) = i
Next i
t = Timer
For n = 1 To 100000
v = Round(Rnd * ARR_SZ, 0)
m = IsInArray(v, arr) 'using match
Next n
Debug.Print Timer - t '~ 1.7 sec
t = Timer
For n = 1 To 100000
v = Round(Rnd * ARR_SZ, 0)
m = IsInArray2(v, arr) 'using a loop
Next n
Debug.Print Timer - t '~0.11 sec
t = Timer
For n = 1 To 100000
v = data_to_array(Range("A1:A50")) 'using cell-by-cell
Next n
Debug.Print Timer - t '~ 11.5 sec
t = Timer
For n = 1 To 100000
v = data_to_array2(Range("A1:A50")) 'using single read from range
Next n
Debug.Print Timer - t '~ 2.8 sec
End Sub
Private Function IsInArray(stringToBeFound As Variant, arr As Variant) As Boolean
IsInArray = Not IsError(Application.Match(stringToBeFound, arr, 0))
End Function
Private Function IsInArray2(stringToBeFound As Variant, arr As Variant) As Boolean
Dim i
For i = LBound(arr) To UBound(arr)
If arr(i) = stringToBeFound Then
IsInArray2 = True
Exit For
End If
Next i
End Function
Private Function data_to_array(data As Range)
Dim arrArray As Variant, cell As Range, z As Integer
z = 0
ReDim arrArray(1 To data.Cells.Count)
For Each cell In data
z = z + 1
arrArray(z) = cell.Value
Next cell
data_to_array = arrArray
End Function
Private Function data_to_array2(data As Range)
Dim arrArray As Variant, cell As Range, z As Long, v
v = data.Value
ReDim arrArray(1 To UBound(v, 1))
For z = 1 To UBound(v, 1)
arrArray = v(z, 1)
Next z
data_to_array2 = arrArray
End Functionhttps://stackoverflow.com/questions/62099812
复制相似问题
腾讯云开发者
