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

[Southall_Legend]

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:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Formula[/TD]
[TD]# of cell refs[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=A1+B1[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=SUM(A1:B1)[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=1+SUM(B1:C1)[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(A1=1,B1,C1)[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

 

[ask2tsp]

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
	A	B	C	D	E	F
1	*	formula	complexity	*	12	44
2	10	=4+6	2	*	*	*
3	56	=E1+F1	2	*	*	*
4	56	=SUM(E1:F1)	1	*	*	*
5	57	=1+SUM(E1:F1)	2	*	*	*
6	44	=IF(A1=1,E1,F1)	4	*	*	*
Blad1

Codes used (install in a general module)

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]

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

 

[ask2tsp]

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

Excel Workbook
	A	B	C	D
1	*	formula	complexity	12
2	10	=4+6	2	44
3	56	=D1+D2	2	*
4	12	=SUM(D1:E1)	1	*
5	13	=1+SUM(D1:E1)	2	*
6	44	=IF(A1=1,D1,D2)	4	*
7	14,89421	=SUM(A2:A6,D1)/PI()^2	3	*
8	46	=---------A2+A3	2	*
9	10	=-+-A2	1	*
10	46	=--++-A2++A3	2	*
11	51	=1+(2+(3*(SUM(D1:E1)+4)))	5	*
Blad1

New improved 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