vba - AutoFilter Criteria Using Array (Error) - Too Large String?

Question

Update: Through some additional testing I discovered: 1) 255 Characters does seem to be the breaking point (character limit). Setting the filter with an array with a character length of 245 worked fine -- I was able to save and reopen without any errors. I added another criteria to the array to make the length 262, saved the file, and then got the same error. 2) The sheet in the removed records message refers to the sheet index, not the sheet name, and it does indeed reference the sheet with the autofiltering. End Update

My Issue -- I've written code to set a dataset's AutoFilter based on selected items in several slicers. Sometimes when I open up the file I get the error (paraphrased): Excel found unreadable content in the workbook. Do you want to repair the file? Then a dialog pops up and says Removed Records: Sorting from /xl/worksheets/sheet2.xml part.

The code works as designed; the dataset reflects whatever is selected in the slicers (even many selections).

I set the array (a string array) as follows and then use the array to set the criteria:

If sCache.Name = "Slicer_Test" Then
    For Each sItem In ActiveWorkbook.SlicerCaches(sCache.Name).SlicerItems
        If sItem.Selected = True Then
            ReDim Preserve sArr(0 To sCount)
            sArr(sCount) = sItem.Name
            sCount = sCount + 1
        End If
    Next sItem
filterRng.AutoFilter Field:=9, Criteria1:=sArr, Operator:=xlFilterValues
ReDim sArr(0 To 0)
End If

I replicate the above code for each slicer.

Where I think the problem stems from is that the three largest slicers contain 27, 120, and 322 items, respectively. So as you can imagine, when all the items in the largest slicer are selected, the array's string length is over 5K characters long... like I mentioned above, the code works as designed. I found this thread, which mentions a character maximum?

I've tried removing the filters before saving/closing the workbook, but that doesn't always work, and this file will be used by many other people. So I'm wondering if 1) anyone has a suggestion for a way to workaround this error, or 2) if there might be a way to accomplish the filtering without using a terribly-long array...

Any thoughts on this will be much appreciated!

Answer 1

A co-worker of mine helped me resolve the issue.

Apparently when using this syntax:

Criteria1:=sArr

Excel reads the array as one long string instead of looking at it as an array that contains many string elements.

The fix is to use the Array() function like so:

Criteria1:=Array(sArr)

This seems to prevent Excel from corrupting.