如何编写此评估公式，以使变体不返回错误? [英] How do I write this evaluate formula so that the variant doesn't return an error?

问题描述

我有一个公式在范围的With/End With语句中使用时效果很好:

I have a formula that works perfectly when used in a With / End With statement for the range:

.Formula ="= IFERROR(INDEX("& MasterDataRange.Address(External:= True)&，MATCH("& Cells(iRow，Entry1).Address(True，False)&;& Left("& Cells(iRow + 1，Entry2).Address(False，False)&，4)&""& wks4.Range("L7").Value&""，& MasterRowMatchRange.Address(External:= True)&"，0)，MATCH(""& header01&"，"& MasterColumnMatchRange.Address(External:= True)&，0))，0)"

当尝试评估以上内容并将其分配给变量时，变量会返回错误结果错误2015".

When trying to Evaluate the above and assign it to a Variant, the Variant returns an error result 'Error 2015'.

xResult = Evaluate("= IFERROR(INDEX("& MasterDataRange.Address(External:= True)&，MATCH("& Cells(iRow，Entry1).Address(True，False)&& Left("& Cells(iRow + 1，Entry2).Address(False，False)&，4)&""& wks4.Range("L7").Value&"，"& MasterRowMatchRange.Address(外部:= True)&，0)，MATCH(""& header01&"，"& MasterColumnMatchRange.Address(外部:= True)&;，0))，0)")

有人可以帮助我理解这一点吗?预先感谢.

Can someone help me understand this? Thanks in advance.

推荐答案

在某些情况下，如果不直接将公式写到单元格中，则无法绕过255个字符的限制.Excel的更新评估引擎没有旧的限制(由 Application.Evaluate 使用的限制).评估大于255个字符限制的函数的工作原型如下::

In some cases you can't get around the 255 character limit of formulae without writing the formulaes directly to cells. Excel's newer evaluation engine doesn't have the same limitations that the old one does (the one which is used by Application.Evaluate. A working prototype of a function which evaluates >255 char limit is as follows:

Function EvaluateAny(ByVal sFormula as string) as variant
    static evaluatorCell as Range: if evaluatorCell is nothing then set evaluatorCell = Range("...")
    if len(sFormula) > 255 then
        evaluatorCell.formula = sFormula
        evaluatorCell.Calculate
        EvaluateAny = evaluatorCell.value
    else
        EvaluateAny = Application.Evaluate(sFormula)
    end if
End Function

注意:此方法将比Application.Evaluate()慢得多

Note: This method will be significantly slower than Application.Evaluate()

另一种替代方法是使用 stdLambda ，因此您可以更直接地实现公式:

Another alternative would be to use stdLambda so you have a more direct implementation of your formula:

Dim func as stdLambda: set func = stdLambda.Create("$1 > 10")
Debug.Print func(100) 'true
Debug.Print func(5)   'false

如果沿着这条路线走，则需要包括stdICallable接口.

You'll need to include stdICallable interface if you go down this route.

这篇关于如何编写此评估公式，以使变体不返回错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！