[VBA] AddCustomList Length Error '1004'

[Safety]

Hey guys!

I've defined a custom sort list (AddCustomList) populated by a large array of unique elements (Example below).

Unfortunately, the number of elements being used exceeds the limit allowed for AddCustomList.

Is there anyway (entirely in VBA) to get around this limit?

Any advice would be greatly appreciated!

Thanks

/edit: An explanation of how this is being applied... The AddCustomList is used to sort a spreadsheet according to an arbitrary hierarchy defined by the sequence of array elements.

Sub Sorbet()

Dim sCustomList As Variant
Dim str As String

str = "Example 1, Example 2, Example 3, Example 4, Example 5, Example 6, Example 7, Example 8, Example 9, Example 10, Example 11, Example 12, Example 13, Example 14, Example 15, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "
str = str & "Example 16, Example 17, Example 18, Example 19, Example 20, Example 21, Example 22, Example 23, Example 24, Example 25, Example 26, Example 27, Example 28, Example 29, Example 30, "


sCustomList = Split(str, ", ")

Application.AddCustomList ListArray:=sCustomList

End Sub

 

[shg]

Put the list in a table in sort order, and add a MATCH formula in another column to look up the list position. Sort by that column.

 

[Safety]

Put the list in a table in sort order, and add a MATCH formula in another column to look up the list position. Sort by that column.

Thank you for the help shg, but would there be any way to accomplish this entirely through VBA (no in-sheet functions)? It's not that your suggestion was wrong, just that for my purposes I'm trying not to alter the spreadsheet (create columns, create extra sheets, add data) other than sorting the pre-existing data.

Thanks again!

 

[shg]

You could use a UDF that returns the sort order using the array you built.

Then a sub would put the formula in a column, sort, and then remove the formula.