Excel VBA解读（140）： 从调用单元格中获取先前计算的值

发布于

学习Excel技术，关注微信公众号：

excelperfect

如果有一个依赖于一些计算慢的资源的用户定义函数，可能希望该用户定义函数在大多数情况下只返回其占用的单元格中最后一次计算得到的值，并且只偶尔使用计算慢的资源。

假设要给用户定义函数传递一个计算慢的资源的参数，并让一个开关告诉它何时使用计算慢的资源。可以设置开关（这里使用称为“RefreshSlow”的已定义名称）并在VBA过程中刷新用户定义函数，如下所示：

代码语言：javascript
复制
Sub RefreshUDFs()
   Dim lCalcMode As Long
   lCalcMode = Application.Calculation
   Application.Calculation = xlCalculationManual
   Names("RefreshSlow").RefersTo = True
   Calculate
   Names("RefreshSlow").RefersTo = False
   Application.Calculation = lCalcMode
End Sub

下面将使用虚拟函数来模拟获取计算慢的资源：

代码语言：javascript
复制
Function GetSlowResource(vParam AsVariant) As Variant
   Dim j As Long
   For j = 1 To 10000000
   Next j
   GetSlowResource = Rnd()
End Function

此函数（忽略参数）并且只是（较慢地）返回一个随机数。

有几种方法可以获得先前为用户定义函数计算的值，它们各有优缺点。

Application.Caller.Value

可以使用Application.Caller.Value，但这会导致循环引用，必须切换迭代才能解决。这很慢并且会掩盖其他无意的循环引用，因此不建议使用它。

代码语言：javascript
复制
Function UDF1(vParam, Refresh)
   If Not Refresh Then
        UDF1 = Val(Application.Caller.Value2)
   Else
        UDF1 = GetSlowResource(vParam)
   End If
End Function

Application.Caller.Text

如果使用Application.Caller.Text，则不会获得循环引用，但会检索单元格中显示为字符串的格式化值。因此，如果单元格被格式化为带有2个小数位的数字，则检索到的值将被截断为2个小数位。

代码语言：javascript
复制
Function UDF2(vParam, Refresh)
   If Not Refresh Then
        UDF2 = Val(Application.Caller.Text)
   Else
        UDF2 = GetSlowResource(vParam)
   End If
End Function

如果可以控制格式或函数返回字符串，则此解决方案将工作正常。

Application.Caller.ID

可以使用Range.ID属性在用户定义函数中存储和检索字符串值。

代码语言：javascript
复制
Function UDF3(vParam, Refresh)
   Dim var As Variant
   If Not Refresh Then
        UDF3 = Val(Application.Caller.ID)
   Else
        var = GetSlowResource(vParam)
        UDF3 = var
        Application.Caller.ID = var
   End If
End Function

这种方法很有效，但Range.ID属性未存储在“已保存”工作簿中，因此下次打开工作簿时，检索到的值将为“空白/零”。

使用XLM或XLL函数传递先前的值到用户定义函数

使用XLM或XLL技术，可以创建非多线程命令等效函数来检索先前的值。下面是名为PREVIOUS的XLL+函数的代码，该函数具有使其成为易失性或非易失性的参数。（命令等效函数默认为易失性，但在使用它将前一个值传递给VBA用户定义函数时，通常希望它是非易失性的）。此函数也适用于多单元格数组公式。

代码语言：javascript
复制
CXlOper* PREVIOUS_Impl(CXlOper&;xloResult, const CXlOper* Volatile_op)
{
// Input buffers
bool Volatile;
// Validate and translate inputs
static CScalarConvertParamsVolatile__params(L"Volatile",
XLA_DEFAULT_ZERO|XLA_DEFAULT_EMPTY|XLA_DEFAULT_NONNUMERIC|
XLA_DEFAULT_BLANK, 0, -1, true);
XlReadScalar(*Volatile_op,Volatile, Volatile__params);
// End of generated code
//}}XLP_SRC
// defined as a macro functiondefer recalc so that the func gets previous results
CXlOper xloCaller,xlo;
CXlOper arg;
arg=true;
if (!Volatile) arg=false;
// set volatility of thisfunction: 237 is the function number for volatile
xlo.Excel(237,1,&arg);
// Get caller. Fail if it is not arange of cells
if ( ( xloCaller.GetCaller() != 0) || !xloCaller.IsRef() ) return CXlOper::RetError(xlerrNA);
//coerce the caller ref
xloResult.Coerce(xloCaller);
return xloResult.Ret();
}

上述代码没有搞懂，有兴趣的朋友研究后可以分享一下！

然后，可以使用它将先前的值传递给用户定义函数。

代码语言：javascript
复制
Function UDF4(vParam, Refresh,Previous)
   Dim var As Variant
   If Not Refresh Then
        UDF4 = Previous
   Else
        var = GetSlowResource(vParam)
        UDF4 = var
   End If
End Function

在公式中调用此函数如下：

=UDF4(“AAPL”,RefreshSlow,PREVIOUS(False))

这很好用，但需要访问XLL PREVIOUS函数。

小结

有几种方法可以从VBA用户定义函数的最后一次计算中获取先前的值，但最好的解决方案需要使用C++ XLL。

#vba#编程算法