Saving Problem

[frets2011]

Problem:

Question 1 - I have a spreadsheet that has a lot of formulas in it. So, the problem is when i try to save, it keeps telling me that i have a formula that exceeds the max of 8192 characters and that i need to save a a binary excel file.

Question 2 - How do i find the problem formula ?

 

[GlennUK]

Good question. How are you at VBA? I'd think that the easiest first approach is to run some VBA to find the long formulas. Something similar to:

Sub findlongformulas()
    For Each ws In ActiveWorkbook.Worksheets
        For Each c In ws.UsedRange
            If c.HasFormula Then
                lentest = Len(c.Formula)
                If lentest > 130 Then
                    MsgBox "Sheet: " & ws.Name & vbCrLf & "Cell: " & c.Address & " formula= " & vbCrLf & c.Formula
                End If
            End If
        Next
    Next
End Sub

... change the 130 to whatever length you want to test for. I'm not sure if your problem ones with come back with exactly what you expect, so maybe test for 250 first, and see how many hits you get. ( hold down Esc key until it starts to autorollover to clear the message multiple times and eventually break into the macro if you are stuck in the loop ).