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

Southall_Legend

New Member
Joined
Mar 31, 2017
Messages
2
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+B12
=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
 

ask2tsp

Active Member
Joined
Feb 18, 2015
Messages
484
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***
Blad1


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
Joined
Mar 31, 2017
Messages
2
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

Active Member
Joined
Feb 18, 2015
Messages
484
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*
Blad1


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
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top