# Count cell references and ranges (as one) in a formula

#### Southall_Legend

##### New Member
Hi everybody!

First time poster, and VBA newbie here. I've been trawling the internet and racking my brains to try to solve my problem but haven't got anywhere, so thought I'd register on here to see if anyone can help...

The issue I have is that I'm trying to analyse a formula and I want to be able to count the number of cell references and fixed values in that formula, whilst counting ranges as one. FWIW I'm doing this to analyse the complexity of a formula.

I did think that counting precedents would do this for me, but this count's the number of direct precedents in a range (aka A1:C1 gives "three", whereas I want "one"), and also doesn't work with precedents on a different sheet

So a quick few examples:
 Formula # of cell refs =A1+B1 2 =SUM(A1:B1) 1 =1+SUM(B1:C1) 2 =IF(A1=1,B1,C1) 4

<tbody>
</tbody>

It's more than possible that there's some obvious way of doing this, staring me in the face; or that some of the information above is incorrect - as I am a newbie. Any help would be greatly appreciated though.

Alternatively, if anyone has a different method of measuring formula complexity, I'd be happy to consider completely different methods.

Thanks

##### Active Member
Hi ...

What you want cannot be done by looking at the precedents.

A possible approach can be: count the number of arguments in a formula. I made a User Defined Function to do that. It parses the formula text. Every text chunk before/between/after one of the characters "+-*/^,=" is counted as an argument. It also handles parenthesis.

An example of the UDF in action:

Excel Workbook
ABCDEF
1*formulacomplexity*1244
210=4+6 ***
356=E1+F1 ***
456=SUM(E1:F1) ***
557=1+SUM(E1:F1)2***
644=IF(A1=1,E1,F1)4***

Codes used (install in a general module)

Code:
``````Function complexity(target As Range) As Integer
'parse formula in target cell; skip = at the start

complexity = parse(Mid(target.Formula, 2))
End Function

Function parse(ByVal expression As String) As Integer
'parse expression and return number of arguments
Dim position   As Integer
Dim nestLevel  As Integer
Dim charAtPos  As String
Dim startLowerLevel  As Integer
Dim lengthLowerLevel As Integer
Dim parsed           As Integer
Dim argumentCount    As Integer

position = 1
nestLevel = 0
argumentCount = 0

Do
charAtPos = Mid(expression, position, 1)

If charAtPos = "(" Then
'parse substring up to matching )
position = position + 1
startLowerLevel = position
nestLevel = nestLevel + 1
While nestLevel > 0
charAtPos = Mid(expression, position, 1)
If charAtPos = "(" Then
nestLevel = nestLevel + 1
ElseIf charAtPos = ")" Then
nestLevel = nestLevel - 1
End If
position = position + 1
Wend
lengthLowerLevel = position - startLowerLevel
parsed = parse(Mid(expression, startLowerLevel, lengthLowerLevel))
argumentCount = argumentCount + parsed
position = position + 1 'move after )
charAtPos = Mid(expression, position, 1)
Else
'count number of arguments in expression
Do
position = position + 1
charAtPos = Mid(expression, position, 1)
Loop Until InStr(1, "+-*/^,=(", charAtPos) > 0 _
Or position > Len(expression)

If charAtPos <> "(" Then argumentCount = argumentCount + 1
End If
Loop Until position > Len(expression)

parse = argumentCount
End Function``````
In your excel workbook start the VBE (Visual Basic Environment) by pressing ALT+F11.
In the VBE execute the menu insert --> module
In the blank window that pops up paste the above code.

Save the workbook as type .xlsm (workbook with macro's)

#### Southall_Legend

##### New Member
Wow, thanks! Haven't had the chance to put this to the test here, but given your description - it looks perfect!

Will have a go tomorrow and congratulate you properly, or admonish accordingly hahaha - only kidding

##### Active Member
Discovered minor bug and added code to handle sequences of sign (+-) characters

Excel Workbook
ABCD
1*formulacomplexity12
210=4+6 44
356=D1+D2 *
412=SUM(D1:E1) *
513=1+SUM(D1:E1)2*
644=IF(A1=1,D1,D2)4*
714,89421=SUM(A2:A6,D1)/PI()^23*
846=---------A2+A32*
910=-+-A21*
1046=--++-A2++A32*
1151=1+(2+(3*(SUM(D1:E1)+4)))5*

New improved code
Code:
``````Function complexity(target As Range) As Integer
'parse formula in target cell; skip = at the start

complexity = parse(Mid(target.Formula, 2))
End Function

Function parse(ByVal expression As String) As Integer
'Application.Volatile
'parse expression and return number of arguments

Dim expressionLength As Integer
Dim position         As Integer
Dim nestLevel        As Integer
Dim charAtPos        As String
Dim charIsSign       As Boolean
Dim startLowerLevel  As Integer
Dim lengthLowerLevel As Integer
Dim parsed           As Integer
Dim argumentCount    As Integer

position = 1
nestLevel = 0
argumentCount = 0
expressionLength = Len(expression)

Do
charAtPos = Mid(expression, position, 1)

If charAtPos = "(" Then
'parse substring up to matching )
position = position + 1
startLowerLevel = position
nestLevel = nestLevel + 1
While nestLevel > 0
charAtPos = Mid(expression, position, 1)
If charAtPos = "(" Then
nestLevel = nestLevel + 1
ElseIf charAtPos = ")" Then
nestLevel = nestLevel - 1
End If
position = position + 1
Wend
lengthLowerLevel = position - startLowerLevel - 1
parsed = parse(Mid(expression, startLowerLevel, lengthLowerLevel))
argumentCount = argumentCount + parsed
position = position + 1 'move after )

Else 'count number of arguments in expression

'skip constant/name/range
While InStr(1, "+-*/^,=(", charAtPos) = 0
position = position + 1
charAtPos = Mid(expression, position, 1)
Wend

If charAtPos <> "(" And position > 1 Then 'at begin don't count
argumentCount = argumentCount + 1
position = position + 1 'skip seperator character
End If

charAtPos = Mid(expression, position, 1)
charIsSign = (position <= expressionLength) _
And InStr(1, "+-", charAtPos)

While charIsSign  'skip possible +- sequence

'since a +- sequence at the end is wrong syntax
'assume this is not the case
position = position + 1

charAtPos = Mid(expression, position, 1)
charIsSign = InStr(1, "+-", charAtPos)
Wend

End If
Loop Until position > Len(expression)

parse = argumentCount
End Function``````

1,081,730
Messages
5,360,934
Members
400,602
Latest member
newaqua

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...