Count number of arguments to Excel formula in VBA

Question

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

Siddharth Rout · Accepted Answer · 2015-05-28 18:44:38Z

1

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

edited May 28, 2015 at 18:44

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Apr 12, 2010 at 8:52

Charles Williams

23.6k5 gold badges40 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Siddharth Rout Over a year ago

Reached this post from Here :)

user225626 · Accepted Answer · 2010-04-12 06:22:30Z

0

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

edited Apr 12, 2010 at 6:22

answered Apr 12, 2010 at 5:10

user225626

1,0995 gold badges16 silver badges33 bronze badges

Comments

Elias · Accepted Answer · 2013-05-23 16:36:50Z

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!

Collectives™ on Stack Overflow

Count number of arguments to Excel formula in VBA

Update:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update:

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related