Count number of arguments to Excel formula in VBA

I need to use VBA to determine the number of arguments passed to an Excel formula. For instance, suppose a cell contains the formula =MyFunc($A$1, "xyz", SUM(1,2,COUNT(C1:C12)), IF(B1>2,1,0)). Then the counter function should return 4. Does VBA contain any built-in functions for this, or does someone have an example of a regular expression that could calculate this?

Update:

Thank you both user225626 and Charles. One problem I found was when there were quoted string arguments that contained commas; these commas were causing the argument count to increase. I've modified Charles' code to account for this.

Public Function CountFormulaArguments(sStr As String) As Integer
    Dim strChar As String
    Dim nArgs As Integer
    Dim n, nLParen, nCommas As Integer
    Dim blArray, bQuote As Boolean
    
    nLParen = 0
    nArgs = 0
    For n = 1 To Len(sStr)
        strChar = Mid(sStr, n, 1)
        If strChar = "(" Then
            nLParen = nLParen + 1
            If nLParen = 1 Then nArgs = nArgs + 1
        ElseIf strChar = ")" Then nLParen = nLParen - 1
        ElseIf nLParen = 1 And strChar = "{" Then blArray = True
        ElseIf blArray And strChar = "}" Then blArray = False
        ElseIf Not bQuote And strChar = """" Then bQuote = True
        ElseIf bQuote And strChar = """" Then bQuote = False
        ElseIf nLParen = 1 And Mid(sStr, n, 开发者_如何学编程1) = "," And Not blArray And Not bQuote Then nCommas = nCommas + 1
        End If
    Next
    nArgs = nArgs + nCommas
    
    CountFormulaArguments = nArgs
End Function

---

To extend Test01 to allow for array constants and multiple function calls in one statement:

=SUM({1,2,3,4,5},{1,2})+SUM({1,2,3,4,5})<br/><br/>

Code:

Sub Test02()
    sStr = Sheets("Sheet1").Range("A2").Formula

    For n = 1 To Len(sStr)
        strChar = Mid(sStr, n, 1)
        If strChar = "(" Then
            nLParen = nLParen + 1
            If nLParen = 1 Then nArgs = nArgs + 1
        End If
        If strChar = ")" Then nLParen = nLParen - 1

        If nLParen = 1 And strChar = "{" Then blArray = True
        If blArray And strChar = "}" Then blArray = False
        If nLParen = 1 And Mid(sStr, n, 1) = "," And Not blArray Then nCommas = nCommas + 1
    Next
    nArgs = nArgs + nCommas

    MsgBox nArgs
End Sub

---

Sub Test01()
 sStr = Sheets("Sheet1").Range("A1").Formula

 For n = 1 To Len(sStr)
  If Mid(sStr, n, 1) = "(" Then nLParen = nLParen + 1
  If Mid(sStr, n, 1) = ")" Then nLParen = nLParen - 1

  If nLParen = 1 And Mid(sStr, n, 1) = "," Then nCommas = nCommas + 1
 Next
 nArgs = nCommas + 1

 MsgBox nArgs
End Sub

---

UBOUND(array)

Why not just use the built-in UBOUND() function on the arguments in order to figure out how many have been fed to your user-defined function?

It is simple, but it will require you set at least one of your variables to have an unlimited count. Here is what I wrote for my purposes:

    Function My_Func(ParamArray others() as variable)

    n = UBound(others) + 1

    'insert code here

    howmanyargumentsinmyfunc = n

    End Function

Note:

• 'others' represents an unlimited number of arguments being fed into my function
• 'n' represents the total number of arguments (you have to add one because of how UBound deals with arrays)

UBound and ParamArray are the key built-in commands for figuring out how many variables get passed into the function. I suppose, if you needed to exclude certain values from being counted you could easily do so with some sort of countif.

I hope this helps somebody!