How to find formula longer than 8192 chars in this workbook?

Hi,
a simple brute force search can do the trick (but it can be long):

Public sub SearchBadFormula()
    Dim Wb as Excel.workbook
    Set Wb = ThisWorkbook

    Dim Ws As Excel.Worksheet
    For Each Ws in Wb.Worksheets
        Dim Rng As Excel.range
        For Each Rng in Ws.UsedRange
            If(Len(Rng.Formula) >= 8192) Then
                Debug.Print Ws.Name & "!" & Rng.address
            End If
        Next
    Next
End Sub

Select allOpen in new window

Hi Fabrice
I did Alt-F11 Insert Module, Pasted in your snippet, returned to Excel and used Macro Run

Nothing happened - I guess meaning nothing was found.
I should mention none of my formulas are 8,192 chars in their standard state, max would be 250 chars.

I am suspicious an array is being read in and treated as part of a formula, for example the contents of a whole column of text maybe. Something dynamic not apparent on a usual search.

Anthony

as a quick check without vba do the following
in any cell type =REPT("?",255)
copy the resulting string onto clipboard
Ctrl F then paste it as follows.  You will see any formulas that are over 255 characters.

If there are none can you post the workbook.  I am sure wed can figure out what weirdness is causing the error.

Why was your workbook  a .xlsb?  If it was only because of the weird 8192 error message, I suggest you try doing the following with a "normal" .xlsx or .xlsm file.  But these steps will also work with a .xlsb file.

And I presume you get the error message every time the workbook is opened.

Often large formulas hide  in sheet references, but they can hide lots of places. Here are a few steps that might help you find the problem.  

 Carefully save the original bad file in a place where it won't accidentally get zapped.
 Then save another  copy of the bad workbook as V1.xl** (meaning .xlsx, .xlsm, or .xlsb).  This V1 version is a temporary copy which you will be
"pruning".  You will methodically, but ruthlessly, delete stuff until you finally get a file that does not have the error.  Then you will undo the last thing deleted and try to figure out what was wrong with it.


Do the remaining testing on V.xl** versions.

Often you can fix the problem by saving the file as v1.xls in Excel 97 to 2003 format  If you can reopen it without the error you are done.
Otherwise, save v1.xls as v2.xl**  in the more recent Excel format.

Delete one or more worksheets saving, closing and reopening after each deletion.  To delete the last worksheet you will need to add a new "Empty" worksheet.

Use ribbon > formulas > name manager to remove one or more references. after each deletion, close and reopen v2.xl**. Repeat until the error disappears or the last reference is gone.

There are more things to delete but the above should lead to a very small file that can be easily posted on EE.]



If not deleting one or more

Hi, thanks for this, following the 255 char finder I have three hits which I will explore. Nothing like 8192 though, just the 255 limit in the names space.

No it does not manifest on file opening, it seems to be random on file save. I tried the old file compatibility method , don't think that did anything.

Yes good idea the binary search, but the problem isn't predictable.
I am absolutely certain none of my formulas in their basic form are not 8192 chars.
Those in the name space look like this:

CashflowDates looks wrong.
However, that is very new and this problem has persisted for several weeks.
Yes using xlb because of this problem. The file crashed out very frequently, but I have assumed that may be being in the beta channel.
Here's the file:
<attachment removed>
Thanks for helping with this weirdness indeed.

edit deleted

Anthony

I found this snippet:
https://www.reddit.com/r/excel/comments/8g2872/how_to_find_which_cell_contains_the_formula/

Sub FindLongFormulas()
    Dim ws As Worksheet
    Dim c As Range
    Dim rngForms As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        Set rngForms = Nothing
        On Error Resume Next
        Set rngForms = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        If rngForms Is Nothing Then
            'Do nothing
        Else
            For Each c In rngForms.Cells
                If Len(c.Formula) >= 8000 Then
                    MsgBox ws.Name & " " & c.Address
                End If
            Next c
        End If
    Next ws
End Sub

Select allOpen in new window

Which found several hits in VAT MTD sheet, the cells that show as REFS.
These all contain Getpivotdata formulas that result in an error.
I can not see at all how these generate the 8192 error, but the above snippet reckons they do.

At the end of the snippet I have to force quit excel, so an edit of the above code would be very welcome?

Notes to self:

1. Binary uses a more compact store rather than XML encoding, and so it can handle longer formulas. The trade-off is that Binary is not as easy to repair if file gets corrupted
2. Binary uses a bit limit rather than character limit, so it's hard to compare directly.

Anthony

 and the following thread has a comment by jackosullivan in 2015 which may help someone in the future for whom none of the above works: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_2007/share-workbook-error-exceed-8192-char-in-formula/77aedd74-009f-4dad-a10b-8fbeb949c449
Buffaloed by Excel 2010's insistence that I suddenly had to save my workbook as a Binary, I tried gettng rid of my Conditional Formatting, but to no avail.
It took me overall several days to finally discover that I really did have a "formula" that exceeded 8192 characters.  It's just that my definition of formula and Microsoft's differ rather significantly -- not the first such semantic difficulty!
The villain turns out to have been a local named variable that held the entire vector of a set of cells I was operating on -- a leftover from an old Excel 4 Macro that was hanging around in my .xlsm template.
It was not easy to locate because it is not in a cell, so displaying equations and then LEN()-counting their length did not find anything.  When the worksheet concerned is not hidden, Excel's "Formulas | Name Manager", showed the "Value" as {...}, which seems to be used for some kinds of references as well as for vector-populated variables.  And the "Refers To" showed a long string of numbers ending in ... to indicate more than can be seen here.
Although Excel itself can display the "Refers to" information, it is not pastable into a worksheet for analysis.
For this, it took one of the reports from j-walk's PUP7 -- their "Utility | Name Lister" which puts all workbook names (whetherthe sheets are hidden or not) into a separate spreadsheet that you can analyse.  Lo and behold, one variable was 8240 characters long.  This particular variable retained all the contents of the last vector operated on until its macro was called again.  So, no matter what I did, I could not get back below the limit until I used the macro on some dummy numbers to flush out the variable.  That done, I could easily save my .xlsm without any complaints.
Hope this helps anyone else who might bump into the 8192 limit.

thank you Jack.

Whether I found my fix remains to be seen.

Anthony

I Posted the following before I saw your good news about a solution.  But it might be useful, so read on.;  I still think you should return to using .xlsx.

No it does not manifest on file opening, it seems to be random on file save..

I hate the error messages that pop up randomly.  For many years I have fixed corruption by using a methodical and ruthless pruning.  I sometimes call it "Divide and Conquer". This very successful approach can be nearly useless if the error message occurs randomly every few days.

======================================================================
Therefore your number 1 priority should be figuring out how to make the error repeatable. 
======================================================================

To help achieve that priority, I strongly suggest that you stop using .xlsb and  return your production copy of this workbook to  .xlsx.  Hopefully it will be easier to make the error repeatable.

I tried the old file compatibility method , don't think that did anything 

Too bad. That often fixes things.  But i was surprised that the older versions didn't complain about the Lamda feature. One of these days I am going have to upgrade my MS Office to 365 so I can start using that feature.

Nothing like 8192 though, just the 255 limit in the names space.

   Just to be clear, the find command is finding any formula that is over 254 characters long.  So all of your hits might be much larger.   But I am pretty sure you realized that already.  

i can not see at all how these generate the 8192 error, but the above snippet reckons they do. 

   The above snippet seems to have an error. It gets a hit for formulas that are LESS THAN 8000 bytes long.  

one of the reports from j-walk's PUP7 revealed a very large formula

    Unfortunately, Walkenbach's PUP7 power pak is no longer available.  The CD that came with my Power Programming with Vba 2003 has a 30 day free trial of much older version.   I bought the book from Amazon, so I could use use it if we get desperate.  If you are interested in vba, it is essential reading.  Amazon has new copies of the 2013 version which almost certainly has a PUP7 free trial.  And Amazon has a 2019 version but Walkenbach sole the series to other authors, so PUP might or might not be included.

But I think I duplicated the PUP logic as follows.  It did not reveal any formulas greater that 600 bytes.

   For Each ref In ActiveWorkbook.names
        
    Debug.Print Len(ref.RefersTo), ref.name & " " & ref.RefersTo
    If Len(ref.RefersTo) > 1000 Then MsgBox "too long"
        Next 

Select allOpen in new window

I also wonder if the new LAMBDA feature is causing the problems?   And certainly the 10 pivot tables you have might be involved.

One other thought.  You have a lot of references to external workbooks.  Let's call the 8192 problem workbook the Primary, and the external workbooks the Slaves.  This makes formulas a lot bigger.  The master/slave approach also makes renaming workbooks quite problematic. .

Perhaps you can copy (the table from the Slaves into the Primary, and eliminate the links.  It might be impractical in your production environment, but it is probably worth doing in your troubleshooting environment.

I am afraid I won't be able to troubleshoot the file as well as I would like, because of those external links C:\Users\mba\Dropbox\!!!Notes\!LedgersDEV\[!DEMO-BOOKKEEPER02DEV.xlsx]. Plus, my main computer does not have Office 365. You are using the Lambda function so I will need to use my wife's computer to troubleshoot.  

Good luck

Yes returned to xlsx instantly.
Was unaware of any external links, these are a result of the heritage of the file or aspects of it and I must delete all of them. So extra thanks for that heads up. I must cull these more frequently.

Never had issues with pivot tables before; as in this sort.
Lambdas, my current learning curve, with LETS and Dynamic Arrays and Structured Tables. Main problem is breaching the Mac 255 char name space limit, likewise with LETs, it's unpleasantly easy to reach 255 with nested formulas/ifs etc.

Yes divide and conquer, absolutely. Been doing that for years. Great minds and all that. Good idea to revert to xlsx to get the problem to reproduce the problem. Though now happily non at all after getting rid of the getpivotdatas that resulted in REFS. Typical of course, that is my first attempt ever at using those.

Thanks for dipping in; hopefully this thread is full of useful stuff for the next person with the 8192 problem.

Anthony

p.s. yes the < 8000 needs editing to >8000. I will edit it.

no your confusion is apt.
I think it was a bit late here when I made those responses.
I will update shortly
Anthony

edit: no not tired of the thread, I was just tired.

Do you still have a version of the .xlsb that was NOT fixed.

Yes will attach one.
See next post for three versions.

I would be very interested in know which cell had the very large formula, because the workbook you posted does not seem to have any such cell.

II reviewed all of the getPivotData formulas in the workbook you posted, and none of them was over 80 bytes.

Yes quite. Yet when I deleted those REF both the 8192 problem AND the constant crashes stopped. That confused me, I now think it was the <8000 I was running. The >= 8192 was yours wasn't it? That's where I remain confused.

Perhaps you posted the workbook AFTER you fixed it?  If so, please post the original .xlsb workbook because I want to know why the .find(????????255???) did not catch such a large  formula.

No I posted pre fixed.
As you say those formulae are small. And yet out of all the formulas in the file why did the snippet hit those, whose removal fixed the problems? Odd.

Another confusing thing.  You posted two versions of the FindLongFormulas snippet.  One of them erroneously found formulas that were less than 8000 bytes long.  the other one correctly found formulas >= 8000.

Yes. Trouble here is I was tired when doing this and now unclear which version I actually ran, adding to my uncertainty is that reddit thread identified the <8000 error so maybe I fixed it at runtime.

Yes the 255 test did nothing at all. Is it possible it wasn't running at all? My default setting is not to enable VBA. But again I thought it was enabled at that time. 

Again, my confusion will be resolved when you post the original/broken .xlsb.

I have several versions so might attach more than one.
Happy to say it has still not crashed at all today.
For the record I was "ok" with the presence of the REF errors, as a prompt for later attention when relevant, but it seems Excel was not. That error cannot arise in a point-and-click Pivot Table, only methinks getpivotdata.

Rather more awake at this time.

Anthony

GVXkeeping23DEVb-RANGEARRAYSFIX.xlsb
GVXkeeping25DEV.xlsb
this one looks like it was well before any fix, as one can see:
GVXkeeping18DEVDEV(AutoRecovered)(AutoRecovered).xlsb

edit, it looks to me like this is the snippet I ran:

 Dim ws As Worksheet
    Dim c As Range
    Dim rngForms As Range
    
    For Each ws In ActiveWorkbook.Worksheets
        Set rngForms = Nothing
        On Error Resume Next
        Set rngForms = ws.Cells.SpecialCells(xlCellTypeFormulas)
        
        If rngForms Is Nothing Then
            'Do nothing
        Else
            For Each c In rngForms.Cells
                If Len(c.Formula) < 8000 Then
                    MsgBox ws.Name & " " & c.Address
                End If
            Next c
        End If
    Next ws
End Sub

Select allOpen in new window

 So the <8000
I had to Force Quit Excel (Mac speak for forced closure of the application) to get out of the routine.
This was still present in my EE version.

Anthony

rats. Now I see. Fabrice gave me the >-8192 snippet. thanks Frabrice.
you gave me the 255 ???? search
jack sullivan on reddit supplied the <8000 then corrected snippet.

Fabrice's found nothing.
Yours found one or two overlength named formulas, only several chars too much not thousands, so that's a useful tool for me as I have been struggling with this restriction.
Jack's hit the getpivotdata formulae, but we do not know why, and that fixed it, it seems.

Anthony

I look at that old .xlsb and now I think I know what happened.
In all of our previous suggestions we failed to mention two important things.

    Find and fix as many error cells as your can (#ref #n/a #value #etc. )
    Find and fix similar errors in the Name Manger references.

I believe the root causes of the 8192 message were all the #errors. When you deleted enough of them the 8192 went away.

In my opinion, none of the snippets were helpful. The "correct" snippet found there were no cells over 8000 bytes.
The incorrect snippet had a bug which made it send a msgbox for EVERY formula.  It was just a coincidence that the first formula was at C4 in MTD reporter which was surrounded by many cells with GetPivotData errors.   By dumb luck it gave you the clue as to how to fix things.

Sometimes it is better to be lucky than to be a smart programmer.  You happen to be both.

So now you can continue to your normal activities.

Good luck in your future endeavors.
rberke (aka UncleBob)

thanks Uncle Bob :-)
Kind thought thinking of me as a programmer.
A bit of luck in these days is a very pleasant surprise: and lesson learned re errors. It didn't feel right leaving them there, but I was blissfully unaware of the risks you mention.

Anthony