Formula longer than 8192 characters. Identify cell?

[jakobt]

Receive an error:
"One or more formulas in this workbook are longer than allowed limit of 8192 characters. To avoid this limitation, save the workbook in excel binary workbook format."

Workbook contains thousands of used cells. How can I identify the cell that is longer than 8192 characters?
Would prefer not to save as binary format!

 

[Fluff]

How about

Sub jakobt()
   Dim Ws As Worksheet
   Dim Cl As Range
   
   For Each Ws In Worksheets
      For Each Cl In Ws.UsedRange.SpecialCells(xlFormulas)
         If Len(Cl.Formula) > 8192 Then Cl.Interior.Color = 45678
      Next Cl
   Next Ws
End Sub

 

[RoryA]

That must be a truly hideous formula. You could find it using code like this:

Sub findGodawfulFormulas()
    Const TOO_LONG As Long = 8192
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Dim rgFormulas As Range
        On Error Resume Next
        Set rgFormulas = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rgFormulas Is Nothing Then
            Dim cell As Range
            For Each cell In rgFormulas.Cells
                If Len(cell.Formula) > TOO_LONG Then
                    Application.Goto cell, True
                    Exit Sub
                End If
            Next cell
            Set rgFormulas = Nothing
        End If
    Next ws
    MsgBox "All sheets checked"
End Sub

 

[biffbolt]

What worked for me to diagnose the issue was to save the file as an Excel 97-2003 Workbook. Rather than the BS statement about being unable to do so because of the 8192 character formula limit (which also popped up when I tried saving as an Excel Binary Workbook as the message suggested), it provided actual meaty information about the problem.

(In my case it was an outdated-and-no-longer used entry in the Data Consolidation references. It looks like every time the workbook was edited and saved with a different name, the reference there got a little longer until it exceeded the 8192 character limit. Deleting that reference - which was to a file that was no longer present in any event - enabled me to save the file.)

Cheers,
Biff