XL - Custom List character limit?

[lameid]

So I am looking at fixing some old code written by an unknown author from who knows when... It applies a custom sort.

There is this suspect looking comment to me and it is the truncation causing me problems (truncated items yield duplicates)...

Any experts on here have an opinion if this is necessary or it was with version X but good starting with version Y thoughts?

Below is a snippet of what is being done...

'Excel has a custom list length limit -- roughly 1600 characters, hence the truncation
   Application.AddCustomList ListArray:=sCustomList 'sCustomList is built by looping and truncating elements in a range based on a truncated element size of 1600/n
   
   oWorksheet.Sort.SortFields.clear
   
   oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Some of my findings...

Custom lists are stored in the registry...

http://www.techrepublic.com/blog/microsoft-office/how-do-i-create-a-custom-sort-in-excel/

Size Limits of registry elements...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724872(v=vs.85).aspx

<

http://msdn.microsoft.com/en-us/library/windows/desktop/ms724872(v=vs.85).aspx>

I just have another priority come up to keep from digging so if anyone knows and can save me some hassle it would be much appreciated.

 

[SkipVought]

hi,

So do you want to create a Custom List or do you want to Sort a list on a sheet? These are two separate and distinct things that are not necessarily related.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

I have items ordered on sheet x that are not in any regular sort (I think ordered by another non-key column in that sheet; haven't dug that deep). I need them sorted by the same key column in successive sheets... which is where the original custom sort (sort using custom list) came in. Because of the perceived custom list limit of 1600 by the original author, some gyrations are done in code to enforce that limit, truncating the source item range to make a custom list that is then used for a custom sort order.

The issue of course is there are items that need sorted with long text.

 

[lameid]

CLARIFICATION: I need them ORDERED the same as the key values of a master sheet. That is the custom sort.

 

[SkipVought]

And here is what I observe from the code that you have posted:

1) There is a statement for Adding a CustomList, using an undefined variable.

2) Some undefined Worksheet Object is cleared of Sort Fields

3) An Excel 97-2003 Sort is performed on some undefined Range Object (we don't know if it is related to the Worksheet Object in 2), although it very well could be

4) the only things that could be related are 2) & 3), because the items in the Custom List are never place on a sheet in the code provided!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

ah! I didn't see the OrderCustom:=Application.CustomListCount + 1 in the Sort

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

That's where my question about the perceived list limit comes in.... Not sure if it 'still' applies... I only provided a snippet of a piece of it as the whole procedure is a bit long... not to mention the calling procedure or the procedure that called it or the under use of variables to capture objects so the code is always easily read. In this case sCustomList is built from a range on the Activesheet. The whole monstrosity executes... just the duplicates in the truncated list causing issue. Ideally the custom list has know limit in Excel 2007 and higher and I am completely good to tweak the sCustomList bit. Or as always I'm willing to go down another path but this solution seems effective... Not sure what the other solution would be... Probably try to ODBC the workbook to itself, add a key sequential numeric key to the master sheet, join and sort by that... write the result back out.... Or some sort of row swapping algorithm based on an array of values.

 

[SkipVought]

http://office.microsoft.com/en-us/e...for-sorting-and-filling-data-HA010222142.aspx

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

Not sure the purpose of the Custom list link... Nothing there about the character limit of custom lists at issue here.

 

[lameid]

There must be something to this as removing the truncation logic, I get application defined error 1004 on the AddCustomList line...

 

[lameid]

Having done some serious digging in code I have readjusted to attempt to use a range instead of a list...

This runs without error but orders by the values and not the order of elements in oRangeOrder.
oRangeSort is set using a union that yields a contiguous tablular range. oRangeKey is a cell in the column that is desired to be sorted.
oRangeOrder is a range on a hidden sheet. It and the sort column contain whole numbers now and in my test case range from 1 to 32. The key column starts out orderded ascending but I know the code is sorting values by testing sorting descending.

   oWorksheet.Sort.SortFields.clear
   oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=oRangeOrder, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Barring an actual fix, I've got a plan but it isn't pretty (involves programatically writing vlookup formulas and copy and paste special to get the sort - and of course adding the long text to my custom order sheet that sources oRangeOrder).

 

[SkipVought]

Well it might help if you posted the range values for each range object.


Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

oRangeSort - This has a header row with some blank values and the right most column is the data is what is indicated by the key range and is numbered from 1-32.


oRangeOrder:
23
18
19
2
1
20
4
21
22
9
13
5
3
7
10
29
8
28
12
14
11
16
6
31
24
27
30
25
32
15
26
17

Beyond that, I'll have to debug it and have to shutdown now to avoid getting locked in the building.... Probably another hour before I can look at it closer again.

 

[SkipVought]

What about oRangeOrder?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[lameid]

So reality happens and then it is the next day and not an hour or so...

oRange Sort is AN5:AQ37 (1 header and 32 data rows) and looks something like (taking a kind of Hybrid CSV layout... hopefully makes sense)...

Columns: AN, AO, AP, AQ
HeaderRow (Row 5): <Blank>, "Target = Widget Stay Dry Gloves", 1, <Blank>
Data Rows (Row 6): "Imagine having gloves to keep your hands dry on those not so cold days", 0.3178...,"Imagine having gloves to keep your hands dry on those not so cold days", 1
(Row 7): "Gloves wick excess moisture away but do not dry out your hands", 0.2116...,"Gloves wick excess moisture away but do not dry out your hands", 2
....
<Blank> is an empty cell.
AN and AP do contain the same long text (not sure why)
I added AQ and is the sequence that AN and AP occur naturally in the source data from which it was copied.

oRangeKey has run as both AQ6 and AQ6:AQ37; AQ6:AQ37 is numbered incrementally 1-37

oRangeOrder content is above. It is on a separate sheet on A1:A32. If you took the data in AN, had different calculated numbers associated that you ordered by but also had a column for the original sequence like in AQ, this is the resultant order for the case.

Obviously I contrived the data but it is long text, in AN and AP and likely longer than what I put there.... Maximum length in the column is 221 characters.

 

[lameid]

Disturbingly recreating that data and running the below works...

Sub Test()
   Dim oWorksheet As Worksheet
   Dim oRangeKey As Range, oRangeSort As Range, oRangeOrder As Range
   
   Set oWorksheet = Worksheets("ChartData for TARGET")
   Set oRangeSort = oWorksheet.Range("AN5:AQ37")
   Set oRangeKey = oWorksheet.Range("AN6:AQ37")
   Set oRangeOrder = Worksheets("IndexOrderSort").Range("A1:A32")
   
   oWorksheet.Sort.SortFields.Clear
   oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=oRangeOrder, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
End Sub

 

[lameid]

If I break the code on the sort line, save the work book add the above test sub and run it, it works... So maybe something with needing to save or maybe oWorksheet is not set correctly... I have to put it aside now just as I am getting close....

 

[lameid]

Had a minute...

So if I call test after the sort and watch it, it sorts appropriately, I know from past executions the ranges are as I have specfied them so moving the execution poiinter back up to sort a bunch of times I tried a bunch of things in debug window to more explicitly set variables... Every single time I got a non-custom sort. If this were Access I'd try the decompile command line switch... Don't know what to try with Excel.
After the below test the only difference I know of is that the oRangeOrder is scoped to the module and not the procedure. That can't be it can it?

oWorksheet.Parent.save
oRangeSort.Select
Set oRangeSort = Selection
oRangeKey.Select
Set oRangeKey = Selection

oRangeOrder.Parent.activate
oRangeOrder.Parent.visible = true
oRangeOrder.Select
Set oRangeOrder = Selection
oRangeSort.Parent.activate

 

[SkipVought]

Okay, I got YOUR custome sort to work.

1) your sort list in sheet IndexOrderSort, must be TEXT. [highlight]Simply changing the Number Format to TEXT DOES ABSOLUTELY NOTHING ALONE!![/highlight]. You must EDIT each cell to reenter the data as TEXT.

2) your sort table column A must ALSO be TEXT, in the same procedure.

3) ADD your sort order list

4) copy this into your code [highlight]CHANGING[/highlight] the row 5 Heading Value (column A)

   oRangeSort.Sort Key1:="[highlight]head1[/highlight]", Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

sorry I see that your first sort column is not column A but column AN

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}