Hi again Christoph, I mentioned using two-dimensional arrays.. well, let me retract that. I think you'd be better off using a one-dimensional array of a user defined type that holds your whole record/stack. You'd have to modify a sort routine to reference the sort-by field in your type.
Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Asa Rossoff Sent: Sunday, January 08, 2012 12:13 AM To: excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Sorted Stacks in VBA Chip Pearson has an in-place array sorting routine for download at http://www.cpearson.com/excel/SortingArrays.aspx. You might use a two-dimensional array to couple your related data, and check if his QSort routine needs any modifications to just sort by the integer value in that situation. Here's a QuickSort implementation for arrays: http://en.allexperts.com/q/Visual-Basic-1048/string-manipulation.htm Several more QuickSort implementations listed here too (including ones designed for multidimensional arrays): http://stackoverflow.com/questions/152319/vba-array-sort-function There's a comment on the above Stack Overflow page that quicksort and other recursive algorithms can overflow the VBA stack and crash Excel. Or, here's a Heap Sort implementation for sorting collections (LGPL license): http://www.source-code.biz/snippets/vbasic/6.htm. In that case, maybe you'd use two collections, or one collection and an array: the first collection with your integers, each with unique key that can be used to find it's paired string (i.e. a sequental number); the second array or collection would hold your strings, and if you used sequential numbers starting at 1 or the array base in the first collection, wouldn't require any keys (just use the array or collection index). Sort the collection of integers, and use the key to associate it with the strings. Here are Bubble Sort implementations for collections: http://www.visualbasic.happycodings.com/Applications-VBA/code27.html http://www.dailydoseofexcel.com/archives/2004/06/02/sort-a-collection/ http://stackoverflow.com/questions/3587662/vba-how-do-i-sort-a-collection Asa -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of christoph404 Sent: Friday, January 06, 2012 8:23 AM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Sorted Stacks in VBA Hi, I'm using Excel 2010 and am darn close to finishing a pretty cool spreadsheet I built with the exception of one problem I can't seem to solve. I a VBAS sub() that takes n pairs of two variables (n is usually < 50). The pairs are always made up of one integer and one string. For example, a pair could be 34, Peter. I want to push these pairs onto a "stack" and once all of them are on there, sort them by the integer number and then remove them from the "stack" in order. I can't use an indexed array (if that even exists in VBA) because the integer numbers don't have to be unique. What can I use? Collections? How would that work with the sort? Here's an example, these are elements I want to push onto th stack: 3437, Chris 3, Peter 98, Mike 82, Mary 98, Kate (note, same iteger as for Mike) Then I want to sort the items by the integer and pop them off the stack like this: 3, Peter 82, Mary 98, Mike (could be Kate, doesn't matter in which order) 98, Kate (could be Mike, doesn't matter in which order) 3437, Chris Granted, a simple solution would just be to put the values into two columns in an Excel spreadsheet and then sort by the integer. I'm trying to avoid this though and make this a bit cleaner. Any help is greatly appreciated. Thanks, Christoph -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
