How to count formula length not cell value length?

[drmingle]

Does anybody have a similiar code out there as the below which only counts the number of characters within formulas of a spreadsheet?

For example, cell A1 has the value of '310', but the formula to get the result of '310' is 431 characters in length.

Sub CountCodeLines()
     Dim VBCodeModule As Object
     Dim NumLines As Long, N As Long
     With ActiveWorkbook
           For N = 1 To .VBProject.VBComponents.Count
                 Set VBCodeModule = .VBProject.VBComponents(N).CodeModule
                 NumLines = NumLines + VBCodeModule.CountOfLines
           Next
     End With
     MsgBox "Total number of lines of code in the project = " & NumLines, , "Code Lines"
     Set VBCodeModule = Nothing
End Sub

I am using Excel 2010.
Thanks in advance.

 

[JoeMo]

Here's a UDF that will do what you want. Count includes the "=" at the start of the formula.

Function FormulaLen(R As Range) As Variant
Set R = R.Cells(1, 1)
If R.HasFormula Then
    FormulaLen = Len(R.Formula)
Else
    FormulaLen = "No formula in cell " & R.Address
End If
End Function

 

[drmingle]

Joe -

Like magic.

Thank you

 

[JoeMo]

Joe -

Like magic.

Thank you

You are welcome. Thanks for your reply.