为其参数解析函数

Question

我有一个用VBA写成的UDF，我从我的工作表中调用它。该函数采用3个参数：Function CONCATIF(arg1 As Range, arg2 As Boolean, Optional arg3 As Range) As Variant

UDF需要知道arg2的公式，即在将arg2计算为TRUE或FALSE之前拦截它。为此，我使用了Application.Caller.Formula，它给了我(最简单的形式) "=CONCATIF(arg1, arg2, arg3)" (或者代替,arg3)，)或,))，然后我可以使用Split(Application.Caller.Formula, ",")获得arg 2。

不过，有几个问题我想解决

• 在逗号处分裂意味着我的论点中没有一个可以包含逗号，而它们可能必须包含逗号。
• 公式可能是嵌套的，例如=SUM(1,IF(CONCATIF(arg1, arg2, arg3)="a",1,0))，所以我不知道拆分数组的哪个项是arg2。(我认为这应该是很容易修复的:在字符串中找到CONCATIF并砍掉开始，数一下后面的开/尾括号，直到打开=关闭，然后切掉结尾。
• 参数本身可能是公式；arg1可以是对范围的引用，而不是实际范围。
• 棘手：CONCATIF可能在一个公式中多次出现，但是在标准字符串搜索中，我总是会选择第一个字符串(如果一个公式中有多个公式，我可能只需要返回一个错误，因为我根本想不出如何绕过这个问题)。

因此，我想要的是:从调用方单元格中获得正确的公式，然后将三个参数解析为数组中的3个字符串的一般方法。作为参考，这是我的代码(抱歉，命名与问题有点不同)

Public Function CONCATIF(checkRange As Range, testFunction As Boolean, Optional concatRange As Range) As Variant

Dim concatArray() As Variant
Dim formulaText As String, formulaParts() As String, formulaTest As String
Dim topLeft As Range, subCell As Range
Dim newTest As String
Dim results() As Boolean, result As Boolean
Dim loopval As Long
'''
'input checking
'''
If concatRange Is Nothing Then
    concatArray = checkRange
ElseIf Not (checkRange.Cells.Count = concatRange.Cells.Count And checkRange.Rows.Count = concatRange.Rows.Count And checkRange.Rows.Count = 1) Then
    CONCATIF = CVErr(xlErrValue)
    Exit Function
Else
    concatArray = concatRange.Value2
End If
'''
'Extract test function
'''
formulaText = Application.Caller.Formula
formulaParts = Split(formulaText, ",") 'Assumes 1)no commas 2) formula isn't nested 3) formula doesn't contain nested functions
formulaTest = formulaParts(1) 'get the test function as a string to be evaluated
Set topLeft = checkRange.Cells(1, 1) 'This is the 'reference' cell - substitute each of the values in the check range for this to test each one
ReDim results(0)
On Error GoTo Err
'''
'Run test on each of the cells in checkRange
'''
For Each subCell In checkRange
    newTest = Replace(formulaTest, topLeft.Address(0, 0), subCell.Address)
    If Count(newTest, "(") < Count(newTest, ")") Then 'when optional parameter is missed out, sometimes you get a ,) and sometimes a ) after formulaTest, so we must check
        newTest = Left(newTest, Len(newTest) - 1)
    End If
    result = (Evaluate(newTest))
skip:
    results(UBound(results)) = result
    ReDim Preserve results(UBound(results) + 1)
Next subCell
'''
'Then use array of Booleans for UDF function
'''
CONCATIF = "test"
Exit Function

Err:
result = False 'if the evaluate results in an error, it means the input was invalid, so probably won't meet the criteria, therefore can be ignored
loopval = loopval + 1
If loopval > checkRange.Cells.Count Then CONCATIF = CVErr(xlErrNA): Exit Function 'exit error loop gracefully if I've missed some edge case
Resume skip

End Function

然后在我的UDF中引用如下：

Function Count(str As String, chr As String) As Long 'counts the number of instances of a character in a string
     Count = Len(str) - Len(Replace(str, chr, ""))
End Function

Answer 1

如果您有一个正确的公式解析器，那么除了在一个公式中处理对CONCATIF的多个调用之外，您可以解决所有这些问题:我不知道有什么方法可以找到当前调用CONCATIF实例的100%。

您周围有各种各样的公式解析器，您也许可以适应:从这里开始，p.html